Can anyone tell me why Account ID and User Agent aren't displaying in my table?

[jhilton90]

index=akamai "httpMessage.host"="*" "httpMessage.path"="/auth/realms/user/login-actions/authenticate" "*User-Agent:*"
| spath "attackData.clientIP"
| rename "attackData.clientIP" as ipAddress, httpMessage.host as Host, httpMessage.path as Path, User-Agent as "User Agent"
| where [search index=keycloak type=LOGIN* [ inputlookup fraud_accounts.csv | rename "Account Login" as customerReferenceAccountId, "Input IP" as ipAddress | return 1000 customerReferenceAccountId ] | return 10000 ipAddress ]
| table ipAddress, Host, Path, _time, "Account ID", "User Agent"

[yuanliu]

Two pointers.

1. Does index=akamai contain a field named "User-Agent"?  I notice that you have a search term "*User-Agent:*".  The wildcards, especially the leading wildcard, suggest that the field name is "something.something.User-Agent".
2. Field of name "Account ID" is nowhere defined in your search.  Unless somewhere in index=akamai there is such a field, it will not be populated.  The closest field name that appears in the code is customerReferenceAccountId; the fact that the code renames "Account Login" from lookup fraud_accounts.csv to this name suggests that that index=keycloak contains such a field.  Even then, the cascaded subsearch does not return this field, anyway.

But a bigger problem is the use of fraud_accounts.csv.  Using inputlookup in a subsearch is nearly always a waste of memory, compute, and time.  SPL command lookup is very efficient; if you have a lookup table, that's the first command you should consider.

To get better help, you should have first explained your data, and explain what is it you are trying to achieve.  Based on reverse engineering of your sample code, I can make some educated guesses.  But such are often incorrect.

To me, it reads like you want to match customerReferenceAccountId in index=keycloak with "User Login" in the table fraud_account.csv in order to find "Input IP" that matches attackData.clientIP in index=akamai.  But in addition to matching IP, you also want to output matching customerReferenceAccountId.

If the speculation is correct, you could use something like

index=akamai "httpMessage.host"="*" "httpMessage.path"="/auth/realms/user/login-actions/authenticate" "*User-Agent:*"
| spath "attackData.clientIP"
| rename "attackData.clientIP" as ipAddress, something.something.User-Agent as "User Agent" ``` you must identify what that something.something is ```
| join ipAddress
    [search index=keycloak type=LOGIN*
    | stats values(customerReferenceAccountId) as "Account ID"
    | lookup fraud_accounts.csv "Account Login" as "Account ID" OUTPUT "Input IP" as ipAddress]
| table ipAddress, Host, Path, _time, "Account ID", "User Agent"

I still don't understand what is the point of spath attackData.clientIP because the field name does not suggest JSON content at all.  I'm just keeping it as is.

[jhilton90]

1. It does contain "User-Agent" but its not its own field. It's "httpMessage.requestHeaders" and the User-Agent is within that

2. I've corrected that mistake, the Account ID has been renamed to customerReferenceAccountId

Let me explain it a bit clearer: So I have a lookup table called fraud_accounts.csv which contains account ids and ipAddresses. We have two indexes, keycloak and akamai.

Keycloak logs feature account ids (customerReferenceAccountId), as well as ipAddresses etc. The akamai logs feature browser information like user-agents and headers, as well as ipAddresses (attackData.clientIP) etc.

So what I am trying to achieve is to look through the fraud_accounts.csv to cross-reference the account ids, ipAddresses in the keycloak logs and see what their user agent is.

Hopefully that is a lot clearer

[yuanliu]

So, that "something.something" in my wild speculation is httpMessage.requestHeaders; in other words, User-Agent should be httpMessage.requestHeaders.User-Agent.  Next, keycloak contains both account ID and IP address.  Your original code did not suggest any field name for IP address in index=keycloak, so I will assume it is also called "ipAddress"; you also did not specify what kind of match is needed from that lookup, so I assume that you want exact row match of both "Account Login" and "Input IP".

index=akamai "httpMessage.host"="*" "httpMessage.path"="/auth/realms/user/login-actions/authenticate"
"httpMessage.requestHeaders.User-Agent"=*
| rename "attackData.clientIP" as ipAddress, "httpMessage.requestHeaders.User-Agent" as "User Agent"
| join ipAddress
    [search index=keycloak type=LOGIN*
    | dedup customerReferenceAccountId ipAddress ``` join is expensive ```
    | rename customerReferenceAccountId as "Account ID"
    | lookup fraud_accounts.csv "Account Login" as "Account ID" "Input IP" as ipAddress OUTPUT "Account Login" "Input IP"
    | rename "Account Login" as "Account ID", "Input IP" as ipAddress]
| table ipAddress, Host, Path, _time, "Account ID", "User Agent"

Hope this helps

[jhilton90]

Tried that updated SPL but it didn't return any results.

I should say that i think ive messed up here when talking about the user-agent field, let me give you an example of what it looks like:

httpMessage.requestHeaders: Content-Type: text/html; charset=utf-8
X-Frame-Options: 
Referrer-Policy:
X-Content-Type-Option:
Content-Security-Policy:
User-Agent:
etc
etc