Splunk Search

How to compare two searches and find common data between them

bhagatdd
Explorer

Hi,

I have following search where I'm searching for the common Plugin_ID between searches. However with the 'stats count by', i'm loosing other fields (Name, _time) that are important part of over all goal. End goal is to find common Plugin_ID between two searches, when it (Plugin_ID) was first detected (date) and number of days from when it was first detected. 

index=main sourcetype="csv_nessus" Risk=High earliest=-180d@d latest=-35d@d AND [search index=main sourcetype="csv_nessus" Risk=High earliest=-35d@d latest=now | stats count by Plugin_ID | table Plugin_ID Name _time ] |chart count by Plugin_ID | table Plugin_ID, Name,  _time

Please help me. Thanks,

Bhagatdd

Labels (1)
Tags (1)
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

OK let's use different names to avoid clashes with reserve words

index=main sourcetype="csv_nessus" Risk=High earliest=-180d@d latest=now
| stats earliest(_time) as firstseen latest(_time) as lastseen values(Name) as Name by Plugin_ID
| where firstseen < relative_time(now(),"-35d@d")
| fieldformat firstseen=strftime(firstseen, "%Y-%m-%d %H:%M:%S")
| fieldformat lasstseen=strftime(lastseen, "%Y-%m-%d %H:%M:%S")

 If that gives you the events first seen between 180 and 35 days ago, then the full query would be

index=main sourcetype="csv_nessus" Risk=High earliest=-180d@d latest=now
| stats earliest(_time) as firstseen latest(_time) as lastseen values(Name) as Name by Plugin_ID 
| where lastseen > relative_time(now(),"-35d@d") AND firstseen < relative_time(now(),"-35d@d")
| eval difference=((relative_time(lastseen,"@d") - relative_time(firstseen,"@d")) / (60*60*24)
| eval _time=latest
| table Plugin_ID, Name,  _time, difference

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Just to clarify, do you mean "End goal is to find common Plugin_ID between two searches, when it (Plugin_ID) was first detected (date) between 180 and 35 days ago and number of days from when it was first detected to when it was last detected in the last 35 days"?

index=main sourcetype="csv_nessus" Risk=High earliest=-180d@d latest=now
| stats earliest(_time) as first latest(_time) as last values(Name) as Name by Plugin_ID 
| where last > relative_time(now(),"-35d@d") AND first < relative_time(now(),"-35d@d")
| eval difference=((relative_time(last,"@d") - relative_time(first,"@d")) / (60*60*24)
| eval _time=latest
| table Plugin_ID, Name,  _time, difference

 

bhagatdd
Explorer

Yes. 

When Plugin_ID was first detected and also find out if its still present in last 35 days. So, 'OR' is not going to work. I have also tried your last suggestion with AND and its not finding any common data. Thanks. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Does this at least give you a row for each Plugin_Id with the first and last as you expect?

index=main sourcetype="csv_nessus" Risk=High earliest=-180d@d latest=now
| stats earliest(_time) as first latest(_time) as last values(Name) as Name by Plugin_ID

Make sure the timepicker for the query is at least  earliest of180d back

bhagatdd
Explorer

@ITWhisperer 

Its difficult to say as format of _time in a readable format. Also, I have noticed duplicate Plugin_ID's. 

Plugin_ID first last Name
101371 1590721200 1598498520 Security Update for Microsoft Office Products (July 2017)
103137 1590721200 1590721200 Security and Quality Rollup for .NET Framework (Sep 2017)
103138 1590721200 1598498520 Security Update for Microsoft Office Excel Products (September 2017)
104556 1590721200 1598498520 Security Updates for Microsoft Excel Products (November 2017)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
index=main sourcetype="csv_nessus" Risk=High earliest=-180d@d latest=now
| stats earliest(_time) as first latest(_time) as last values(Name) as Name by Plugin_ID
| fieldformat first=stftime(first, "%Y-%m-%d %H:%M:%S")
| fieldformat lasst=stftime(last, "%Y-%m-%d %H:%M:%S")

Make it easier to read!

0 Karma

bhagatdd
Explorer

@ITWhisperer 

Receiving following error:

Error in 'fieldformat' command: The 'stftime' function is unsupported or undefined.

0 Karma

bhagatdd
Explorer

@ITWhisperer 

with fixing typo 'strftime'

its now showing query is at least  earliest of180d back. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Sorry about the typo

Let's see what is being removed by the where firstly for Plugin_IDs that first appeared over 35 days ago

index=main sourcetype="csv_nessus" Risk=High earliest=-180d@d latest=now
| stats earliest(_time) as first latest(_time) as last values(Name) as Name by Plugin_ID
| where first < relative_time(now(),"-35d@d")
| fieldformat first=strftime(first, "%Y-%m-%d %H:%M:%S")
| fieldformat lasst=strftime(last, "%Y-%m-%d %H:%M:%S")

  And then for Plugin_IDs that last appeared less than 35 days ago

index=main sourcetype="csv_nessus" Risk=High earliest=-180d@d latest=now
| stats earliest(_time) as first latest(_time) as last values(Name) as Name by Plugin_ID
| where last > relative_time(now(),"-35d@d")
| fieldformat first=strftime(first, "%Y-%m-%d %H:%M:%S")
| fieldformat lasst=strftime(last, "%Y-%m-%d %H:%M:%S")
0 Karma

bhagatdd
Explorer

@ITWhisperer 

Last query (Plugin_IDs that last appeared less than 35 days ago) showing correct data. Only missing field is # of days from it was first detected. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

OK let's use different names to avoid clashes with reserve words

index=main sourcetype="csv_nessus" Risk=High earliest=-180d@d latest=now
| stats earliest(_time) as firstseen latest(_time) as lastseen values(Name) as Name by Plugin_ID
| where firstseen < relative_time(now(),"-35d@d")
| fieldformat firstseen=strftime(firstseen, "%Y-%m-%d %H:%M:%S")
| fieldformat lasstseen=strftime(lastseen, "%Y-%m-%d %H:%M:%S")

 If that gives you the events first seen between 180 and 35 days ago, then the full query would be

index=main sourcetype="csv_nessus" Risk=High earliest=-180d@d latest=now
| stats earliest(_time) as firstseen latest(_time) as lastseen values(Name) as Name by Plugin_ID 
| where lastseen > relative_time(now(),"-35d@d") AND firstseen < relative_time(now(),"-35d@d")
| eval difference=((relative_time(lastseen,"@d") - relative_time(firstseen,"@d")) / (60*60*24)
| eval _time=latest
| table Plugin_ID, Name,  _time, difference
0 Karma

bhagatdd
Explorer

@ITWhisperer 

Excellent.

This query showing expected results. Thank you for your help. 

0 Karma

inventsekar
SplunkTrust
SplunkTrust

Hi @bhagatdd ..
one important thing to remember,...
earliest and latest only work when you use them in the base search(1st search), not inside the Subsearch(2nd search).

and also, for your case, there is no need of subsearch altogether., the earliest and latest also supports an OR condition. 

index=main sourcetype="csv_nessus" Risk=High (earliest=-7d@d latest=-1d@d OR earliest=-35d@d latest=now)
| stats count by Plugin_ID | table Plugin_ID Name _time ] |chart count by Plugin_ID | table Plugin_ID, Name,  _time

 

 

 

documentation for ref:

index=myindex ((earliest=-24h latest<@d) OR (earliest>=@d+1h))

https://docs.splunk.com/Documentation/Splunk/latest/Search/Specifytimemodifiersinyoursearch

 

(PS - i have given around 350+ karma points so far, received badge for that, if an answer helped you, a karma point would be nice!. we all should start "Learn, Give Back, Have Fun")

thanks and best regards,
Sekar

PS - If this or any post helped you in any way, pls consider upvoting, thanks for reading !
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...