topic Re: Complex subsearch comparing time periods in Splunk Search

Complex subsearch comparing time periods

EricLloyd79 — Thu, 01 Jun 2017 15:49:12 GMT

Hello I have a rather complex search/subsearch I am trying to figure out.

I need to acquire a list of values from a search from 20 days ago to 10 days ago.
A search such as:
sourcetype=abc KPI100 earliest=05/11/2017:00:00:00 latest=now result=completed.jsp | stats values(mdn)

And I need to use those results and query them with another query that will run from 10 days ago to present and show the ones who have a count > 1 such as;
sourcetype=xyz KPI200 | table VAL | eventstats counts by VAL | search count>1 | dedup VAL

So I tried to create a subsearch as:
sourcetype=xyz KPI200 [serach sourcetype=abc KPI100 earliest=05/11/2017:00:00:00 lastest=now results=completed.jsp | stats values(mdn)] | table VAL | eventstats count by VAL | search count>1 | dedup VAL
But it didn't return any values.

Any suggestions?

Re: Complex subsearch comparing time periods

micahkemp — Thu, 01 Jun 2017 15:53:26 GMT

This may only be a partial answer, but your subsearch needs to be changed. It currently results in a field named values(mdn), which is unlikely to be in your source data. Also, multivalue fields returned from a subsearch aren't that simple, so try splitting them up via mvexpand:

[search sourcetype=abc KPI100 earliest=05/11/2017:00:00:00 lastest=now results=completed.jsp | stats values(mdn) AS mdn | mvexpand mdn]

Edit: I'm going to attempt to answer the full question since the partial answer didn't work.

I would suggest trying to do this all in one search, going back the full 20 days, and reporting based on all of that data. It might look something like:

sourcetype=abc KPI100 earliest=-20d latest=now result=completed.jsp
| addinfo 
| eval twenty_days_ago=relative_time(info_max_time, "-20d@d") 
| eval was_in_last_twenty_days=if(_time >= twenty_days_ago, 1, 0) 
| eventstats dc(was_in_last_twenty_days) AS dc_was_in_last_twenty_days, count BY mdn
| search dc_was_last_twenty_days>1

This should give you the count for mdn values that were seen in both time periods (-20d -> -10d, -10d -> now). This might not be exactly what you want, but it might be enough to get you going.

Re: Complex subsearch comparing time periods

EricLloyd79 — Thu, 01 Jun 2017 18:43:52 GMT

Thanks for the suggestion.
It still returns nothing. I continue to search for a solution.

Re: Complex subsearch comparing time periods

somesoni2 — Thu, 01 Jun 2017 20:39:38 GMT

Give this a try

sourcetype=xyz KPI200 [search sourcetype=abc KPI100 earliest=-20d@d latest=-10d@d result=completed.jsp | stats count by mdn | table mdn]
| stats count by VAL| where count>1 | table VAL