Need help on the Lookup Table

[renjujacob88]

Hi Splunkers,

I have a lookup which contains Suspicious UA String/Keyword and type. Please find below screenshot

Also I do have a query to capture any event matching the suspicious keyword.

sourcetype=iis OR sourcetype=pan:threat OR (index=citrix sourcetype=citrix:netscaler:syslog) | eval cs_User_Agent=coalesce(cs_User_Agent,user_agent,citrix_useragent) | stats count by _time sourcetype cs_User_Agent | search [| inputlookup Suspicious_Useragent_List.csv | eval search=""+cs_User_Agent+"" | rename search as cs_User_Agent |fields cs_User_Agent] | convert ctime(_time) | stats values(_time) as UA_Timestamp first(_time) as Earliest_UA_Timestamp last(_time) as Lastest_UA_Timestamp values(sourcetype) as UA_Sourcetype count by cs_User_Agent |rename cs_User_Agent as Non_Standard_User_Agent count as UA_Count |

After running the query the output results will be like below screenshot:

What i require is to display the matching word and type along with the results!!!
Any help will be appreciated

[somesoni2]

Since your lookup table values doesn't match exactly with your raw data values, you can setup a wildcard match lookup (requires updating the lookup table file, so assuming you got access/mechanics to have the lookup table updated.

Step1: Update the lookup table file to include the wildcard/asterisk included in the value.
Lookup table name: Suspicious_Useragent_List.csv

Type,cs_User_Agent,sl
Suspicious,*Crazy*,1
Scan,*ZmEu*,2
......

Step 2: Create a lookup tranform for your lookup table (from file system, from deployer/deployment server whatever you use to deploy configs, or create directly on SH and restart it). You would need to create a transforms.conf entry like this

[Suspicious_Useragent_List]
 filename = Suspicious_Useragent_List.csv
 match_type = WILDCARD(cs_User_Agent)

Step3 : Update your query like this

sourcetype=iis OR sourcetype=pan:threat OR (index=citrix sourcetype=citrix:netscaler:syslog) 
| eval cs_User_Agent=coalesce(cs_User_Agent,user_agent,citrix_useragent) 
| search [| inputlookup Suspicious_Useragent_List  |fields cs_User_Agent]
| stats count by _time sourcetype cs_User_Agent | convert ctime(_time) 
| stats values(_time) as UA_Timestamp first(_time) as Earliest_UA_Timestamp last(_time) as Lastest_UA_Timestamp values(sourcetype) as UA_Sourcetype count by cs_User_Agent 
| lookup Suspicious_Useragent_List cs_User_Agent OUTPUT Type
|rename cs_User_Agent as Non_Standard_User_Agent count as UA_Count 

[mayurr98]

Try this:

sourcetype=iis OR sourcetype=pan:threat OR (index=citrix sourcetype=citrix:netscaler:syslog) | eval cs_User_Agent=coalesce(cs_User_Agent,user_agent,citrix_useragent)  | stats values(_time) as UA_Timestamp first(_time) as Earliest_UA_Timestamp last(_time) as Lastest_UA_Timestamp values(sourcetype) as UA_Sourcetype count  by cs_User_Agent | lookup Suspicious_Useragent_List.csv cs_User_Agent output Type | rename cs_User_Agent as Non_Standard_User_Agent count as UA_Count 

This query will work provided cs_User_Agent of lookup and data is exactly matching.
let me know if this helps!

[renjujacob88]

@ mayurr98 Thanks for pitching in. Actually my request is to display the user agent details that matches the suspicious keyword from the lookup and coming to your query, it will be displaying all the user agent details within the time range, not exactly what i need .

[mayurr98]

if you want only Type=Suspicious then try

sourcetype=iis OR sourcetype=pan:threat OR (index=citrix sourcetype=citrix:netscaler:syslog) | eval cs_User_Agent=coalesce(cs_User_Agent,user_agent,citrix_useragent)  | stats values(_time) as UA_Timestamp first(_time) as Earliest_UA_Timestamp last(_time) as Lastest_UA_Timestamp values(sourcetype) as UA_Sourcetype count  by cs_User_Agent | lookup Suspicious_Useragent_List.csv cs_User_Agent output Type | search Type="Suspicious" | rename cs_User_Agent as Non_Standard_User_Agent count as UA_Count 

[renjujacob88]

@mayuur98 : Not the one i'm looking for . Need to get the matching string and type against the useragent details.