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?
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
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.
Thanks for the suggestion.
It still returns nothing. I continue to search for a solution.