topic Re: Subsearches compairing datasets in Splunk Search

Subsearches compairing datasets

adamsmith47 — Fri, 10 Feb 2017 16:29:21 GMT

Hello all,

I have a search technique I've been using to compare smaller sets of data, to find the difference, however I'm running into the subsearch limit with a new set of data. I'm hoping someone has a good idea for a different way to perform the search that doesn't run into subsearch limits. Here's the situation:

Each night a system is dumping an *.csv log into a directory which Splunk is monitoring and indexing. The csv is approximately 50k lines, therefor approx 50k events indexed by Splunk. I'm being asked to report each morning on events that exist in today's dump, which didn't exist in the previous day's dump. I've gone to my typical routine below in an attempt to accomplish this, but I'm hitting that 10k subsearch limit. I'm assuming I could up the limit, but, I'd rather have a more efficient search, if possible.

| set union
[search index=<index> sourcetype=<sourcetype> earliest=@d-1d latest=@d | eval daysago=1 | stats count by <field1> <field2> <field3> daysago | fields - count]
[search index=<index> sourcetype=<sourcetype> earliest=@d latest=@d+1d | eval daysago=0 | stats count by <field1> <field2> <field3> daysago | fields - count]
| stats max(daysago) as daysago by <field1> <field2> <field3> | where daysago=0
| eval Details="Has been added in the past day."
| table Details <field1> <field2> <field3>

I know the logic is sound (I use it for other things), but here the subsearches are just too big.

Any advice is welcome! Thank you.

Re: Subsearches compairing datasets

lguinn2 — Fri, 10 Feb 2017 16:51:57 GMT

You can only up the limit to 10,499 so that isn't going to help. The following technique has no limits and will run much faster:

search index=<index> sourcetype=<sourcetype> earliest=-1d@d
| eval daysago=if(_time>reltime(now,"@d"),daysago=0,daysago=1)
| stats count by <field1> <field2> <field3> daysago 
| fields - count
| stats max(daysago) as daysago by <field1> <field2> <field3> 
| where daysago=0
| eval Details="Has been added in the past day."
| table Details <field1> <field2> <field3>

This technique searches the data set only once, then categorizes the results before comparing them.

Re: Subsearches compairing datasets

somesoni2 — Fri, 10 Feb 2017 16:59:25 GMT

I believe we can eliminate first stats altogether (| stats count...). Also, he earliest for 0 daysago i.e. @d is inclusive of events exactly at @d, the comparison operator for _time>relative_time(now(),"@d") (there is a typo in the relative_time) should be >=.

Re: Subsearches compairing datasets

DalJeanis — Fri, 10 Feb 2017 17:47:32 GMT

How about this -

 index=<index> sourcetype=<sourcetype> earliest=-1d@d
| bin _time span=1d
| stats min(_time) as mintime max(_time) as maxtime by <field1> <field2> <field3>
| eventstats min(mintime) as yesterdayepoch max(maxtime) as todayepoch
| where mintime=maxtime
| eval myflag=case(mintime==todayepoch,"Added Record",maxtime==yesterdayepoch,"Deleted Record", true(),"Nonesuch Record")  
| eval _time = mintime 
| table _time <field1> <field2> <field3> myflag

updated case tests to use == rather than =

Re: Subsearches compairing datasets

adamsmith47 — Fri, 10 Feb 2017 19:06:29 GMT

Thank you Ignuinn and somesoni, it's working well!

The form I've ultimately gone with is:

index=<index> sourcetype=<sourcetype> earliest=-1d@d
| eval daysago=if(_time>=relative_time(now(),"@d"),0,1)
| stats max(daysago) as daysago by <field1> <field2> <field3>
| where daysago=0
| eval Details="Has been added in the past day."
| table Details <field1> <field2> <field3>