Splunk Search

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

wweiland
Contributor

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!

0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

cmerriman
Super Champion

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
Legend

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
Contributor

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
0 Karma

wweiland
Contributor

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

wweiland
Contributor
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"

0 Karma

wweiland
Contributor

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!

0 Karma

wweiland
Contributor

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

0 Karma

cmerriman
Super Champion

are you on 6.5?

0 Karma

wweiland
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...