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

[wweiland]

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!

[cmerriman]

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

[sundareshr · Accepted solution]

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

[wweiland]

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

[wweiland]

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

[wweiland]

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"

[wweiland]

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!

[wweiland]

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

[cmerriman]

are you on 6.5?

[wweiland]

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