topic Re: How to compare two searches and find common data between them in Splunk Search

How to compare two searches and find common data between them

bhagatdd — Sat, 26 Sep 2020 18:16:11 GMT

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

Re: How to compare two searches and find common data between them

inventsekar — Sat, 26 Sep 2020 20:09:03 GMT

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

Re: How to compare two searches and find common data between them

ITWhisperer — Sun, 27 Sep 2020 10:21:23 GMT

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

Re: How to compare two searches and find common data between them

bhagatdd — Sun, 27 Sep 2020 21:31:56 GMT

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.

Re: How to compare two searches and find common data between them

ITWhisperer — Sun, 27 Sep 2020 22:43:45 GMT

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

Re: How to compare two searches and find common data between them

bhagatdd — Mon, 28 Sep 2020 12:45:07 GMT

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

Re: How to compare two searches and find common data between them

ITWhisperer — Mon, 28 Sep 2020 12:50:15 GMT

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!

Re: How to compare two searches and find common data between them

bhagatdd — Mon, 28 Sep 2020 12:52:41 GMT

@ITWhisperer

Receiving following error:

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

Re: How to compare two searches and find common data between them

bhagatdd — Mon, 28 Sep 2020 12:58:36 GMT

@ITWhisperer

with fixing typo 'strftime'

its now showing query is at least earliest of180d back.

Re: How to compare two searches and find common data between them

ITWhisperer — Mon, 28 Sep 2020 13:30:51 GMT

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

Re: How to compare two searches and find common data between them

bhagatdd — Mon, 28 Sep 2020 13:41:16 GMT

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

Re: How to compare two searches and find common data between them

ITWhisperer — Mon, 28 Sep 2020 14:14:13 GMT

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

Re: How to compare two searches and find common data between them

bhagatdd — Mon, 28 Sep 2020 14:33:19 GMT

@ITWhisperer

Excellent.

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