topic Re: How to compare search results from 2 dates without using subsearch? in Splunk Search

How to compare search results from 2 dates without using subsearch?

wweiland — Wed, 19 Oct 2016 17:37:20 GMT

Hi,

I'm trying to compare stats from 2 different dates (sometimes not back to back) and I'm running into a wall because of subsearch limitations. I want to look at total count and average reqtime for grouped URLs for 2 different days and then find the difference between then. Because of the number of logs, I would like to limit the search to just those days if possible.

index=f5 instance=test earliest=-4d@d latest=-3d@d|eval urlall=split(uri,"?")|eval url=mvindex(urlall,0)+"*"|stats count avg(reqtime) as avgtimeold by url|sort - avgtimeold|where count>100|head 30|rename count as countold| join url [search index=f5 instance=test earliest=-2d@d latest=-1d@d|eval urlall=split(uri,"?")|eval url=mvindex(urlall,0)+"*"|stats count avg(reqtime) as avgtimenew by url|sort - avgtimenew|where count>100|head 100|rename count as countnew]|eval avgtimediff=avgtimenew-avgtimeold|eval avgtimediffpercent=tostring(floor(avgtimediff*100/avgtimeold))+"%"|eval countdiff=countnew-countold|eval countdiffpercent=tostring(floor(countdiff*100/countold))+"%"|table url,countold,countnew,countdiff,countdiffpercent,avgtimeold,avgtimenew,avgtimediff,avgtimediffpercent

The subsearch doesn't work because it takes longer than 60 seconds to return the results. I thought of using a lookup table, but the plan is to put this into a dashboard and I'm not sure how I could populate the lookup tables from there. If anyone has any suggestions on which way I could go, it would be greatly appreciated.

TIA!

Re: How to compare search results from 2 dates without using subsearch?

cmerriman — Wed, 19 Oct 2016 17:49:18 GMT

are you on 6.5?

Re: How to compare search results from 2 dates without using subsearch?

sundareshr — Wed, 19 Oct 2016 17:53:22 GMT

Try this

index=f5 instance=test (earliest=-4d@d latest=-3d@d) OR (earliest=-2d@d latest=-1d@d) 
| eval when=if(_time<=relative_time(now(), "-3d"), "Old", "New")
| eval urlall=split(uri,"?") 
| eval url=mvindex(urlall,0)+"*" 
| chart count avg(reqtime) as avgtime over url by when
| sort - avgtimeold 
| where count>100 
| head 30 
| rename count as countold 
| eval avgtimediff=new-old 
| eval avgtimediffpercent=tostring(floor(avgtimediff*100/avgtimeold))+"%" 
| eval countdiff=countnew-countold 
| eval countdiffpercent=tostring(floor(countdiff*100/countold))+"%" 
| table url,countold,countnew,countdiff,countdiffpercent,avgtimeold,avgtimenew,avgtimediff,avgtimediffpercent

Re: How to compare search results from 2 dates without using subsearch?

wweiland — Wed, 19 Oct 2016 18:05:18 GMT

I am on 6.4.2. Is there something in 6.5 that will make this super easy?

Re: How to compare search results from 2 dates without using subsearch?

wweiland — Wed, 19 Oct 2016 18:06:05 GMT

Looks sweet. I will give it a try and will get back to you. Thank you again for working out the query.

Re: How to compare search results from 2 dates without using subsearch?

cmerriman — Wed, 19 Oct 2016 18:09:33 GMT

Something along these lines. I took out your sort and head commands, as I wasn't sure how you'd want to cut off the values. :

index=f5 instance=test earliest=-4d@d latest=-1d@d 
| eval urlall=split(uri,"?") 
| eval url=mvindex(urlall,0)+"*" 
| eval timeFrame=if(relative_time(now(),"-2d@d")>=_time,"old","new")
| chart count avg(reqtime) as avgtime by url timeFrame
| rename "count: old" as countold "count: new" as countnew "avgtime: old" as avgtimeold "avgtime: new" as avgtimenew
| where count>100 
| eval avgtimediff=avgtimenew-avgtimeold 
| eval avgtimediffpercent=tostring(floor(avgtimediff*100/avgtimeold))+"%" 
| eval countdiff=countnew-countold 
| eval countdiffpercent=tostring(floor(countdiff*100/countold))+"%" 
| table url,countold,countnew,countdiff,countdiffpercent,avgtimeold,avgtimenew,avgtimediff,avgtimediffpercent

Re: How to compare search results from 2 dates without using subsearch?

wweiland — Wed, 19 Oct 2016 20:27:09 GMT

index=f5 instance=test (earliest=-4d@d latest=-3d@d) OR (earliest=-2d@d latest=-1d@d) 
| eval when=if(_time<=relative_time(now(), "-3d"), "Old", "New") 
| eval urlall=split(uri,"?") 
| eval url=mvindex(urlall,0)+"*" 
| chart count avg(reqtime) as avgtime over url by when
| sort - avgtimeOld
| where countOld > 100

This query doesn't produce results. When I take out the where it does. Am I screwing up the field names? They show in splunk as "count: Old"

Re: How to compare search results from 2 dates without using subsearch?

wweiland — Wed, 19 Oct 2016 20:33:11 GMT

Nevermind, I used the rename from cmerriman's response and it seems to work. Thank you both for the great help. This query is nice!

Re: How to compare search results from 2 dates without using subsearch?

wweiland — Fri, 21 Oct 2016 16:12:17 GMT

One last question on this topic.

Works

eval when=if(_time<=if(isnum(1476860400),1476860400,relative_time(now(),"1476860400")), "old", "new")
eval when=if(_time<=if(isnum("-3d@d"),"-3d@d",relative_time(now(),"-3d@d")), "old", "new")

Doesn't Work

when=if(_time<=if(isnum("1476860400"),"1476860400",relative_time(now(),"1476860400")), "old", "new")
eval when=if(_time<=if(isnum(-3d@d),-3d@d,relative_time(now(),"-3d@d")), "old", "new")

My problem is, I can't figure out how to do the query where it can take calendar and relative times from the time picker. Quotes are needed for the relative and not for the calendar (epoch).

index=f5 instance=test (earliest=1476774000 latest=1476860400) OR (earliest=1476946800 latest=1477033200) | eval when=if(_time<=if(isnum(1476860400),1476860400,relative_time(now(),"1476860400")), "old", "new") | eval urlall=split(uri,"?") | eval url=mvindex(urlall,0)+"*" | chart count avg(reqtime) as avgtime over url by when | rename "count: old" as countold "count: new" as countnew "avgtime: old" as avgtimeold "avgtime: new" as avgtimenew | sort - avgtimeold | where countold > 100 | head 30 | eval avgtimediff=avgtimenew - avgtimeold | eval avgtimediffpercent=tostring(floor(avgtimediff*100/avgtimeold))+"%" | eval countdiff=countnew-countold | eval countdiffpercent=tostring(floor(countdiff*100/countold))+"%" | table url,countold,countnew,countdiff,countdiffpercent,avgtimeold,avgtimenew,avgtimediff,avgtimediffpercent

Re: How to compare search results from 2 dates without using subsearch?

wweiland — Fri, 21 Oct 2016 17:26:41 GMT

heh, think i found a solution.

| eval timea = if(len("$timeRangeOld.latest$") < 10,relative_time(now(),"$timeRangeOld.latest$","$timeRangeOld.latest$") | eval when=if(_time<=timea, "old", "new")