Splunk Search

Need help on the Lookup Table

renjujacob88
Path Finder

Hi Splunkers,

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

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:
alt text
What i require is to display the matching word and type along with the results!!!
Any help will be appreciated

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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 
0 Karma

mayurr98
Super Champion

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!

0 Karma

renjujacob88
Path Finder

@ 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 .

0 Karma

mayurr98
Super Champion

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 
0 Karma

renjujacob88
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...