How to compare two searches and find common data between them

[bhagatdd]

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

[ITWhisperer]

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]

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. 

[ITWhisperer]

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]

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

[ITWhisperer]

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!

[bhagatdd]

@ITWhisperer 

Receiving following error:

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

[bhagatdd]

@ITWhisperer 

with fixing typo 'strftime'

its now showing query is at least  earliest of180d back. 

[ITWhisperer]

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")

[bhagatdd]

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

[ITWhisperer · Accepted solution]

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

[bhagatdd]

@ITWhisperer 

Excellent.

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

[inventsekar]

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")