Splunk Search

Compare data from Yesterday and today without Weekend

manapuna
New Member

host=somehost sourcetype=somesource earliest=@d+9h latest=now| timechart span=15m dc(UserId)
| appendcols [search host=somehost sourcetype=somesource earliest=-d@d+9h latest=now-d@d| timechart span=15m dc(UserId)]

I know I got the syntax wrong here => latest=now-d@d

My report is running every 15min Mon-Fri. */15 * * * 1-5

I want to display today's values vs Yesterdays value. If Today is Monday, then Yesterday should be Friday for any other days Today should be comparing with previous days. Since Today is Monday, yesterday is weekend. I only want weekdays. I seen similar example but not what I am looking for.

Since I am using now as latest - some time I am getting my 15min reports in two line. Is there anyway we can make sure we only one one line of answer.

Thanks in advance.

Tags (1)
0 Karma
1 Solution

maciep
Champion

i feel like always end up doing this stuff the hard way, but maybe something like this:

index=_internal earliest=@d+9h 
| timechart span=15m count
| eval interval = trim(strftime(_time,"%I:%M%P"),"0")
| rename count as today 
| fields - _time
|  appendcols [ 
    search index=_internal [
        |  stats count 
        |  eval now = now(), today=strftime(now,"%w")
        |  eval earliest = strftime(if(today=1,relative_time(now,"-3d@d+9h"),relative_time(now,"-1d@d+9h")),"%m/%d/%Y:%H:%M:%S")
        |  eval latest = strftime(if(today=1,relative_time(now,"-3d"),relative_time(now,"-1d")),"%m/%d/%Y:%H:%M:%S")
        | fields earliest, latest
    ] 
    |  timechart span=15m count
    | rename count as previous 
    | fields + previous
 ]
| table interval, today, previous

View solution in original post

0 Karma

maciep
Champion

i feel like always end up doing this stuff the hard way, but maybe something like this:

index=_internal earliest=@d+9h 
| timechart span=15m count
| eval interval = trim(strftime(_time,"%I:%M%P"),"0")
| rename count as today 
| fields - _time
|  appendcols [ 
    search index=_internal [
        |  stats count 
        |  eval now = now(), today=strftime(now,"%w")
        |  eval earliest = strftime(if(today=1,relative_time(now,"-3d@d+9h"),relative_time(now,"-1d@d+9h")),"%m/%d/%Y:%H:%M:%S")
        |  eval latest = strftime(if(today=1,relative_time(now,"-3d"),relative_time(now,"-1d")),"%m/%d/%Y:%H:%M:%S")
        | fields earliest, latest
    ] 
    |  timechart span=15m count
    | rename count as previous 
    | fields + previous
 ]
| table interval, today, previous
0 Karma

maciep
Champion

not ideal or maybe not possible for your use case, but have you considered running this report Tue-Fri and a different one on Mondays?

0 Karma

manapuna
New Member

I thought about doing two reports. Thanks,

0 Karma

maciep
Champion

ok, well let me know if my answer helps you do it in one report.

0 Karma

manapuna
New Member

Thank you. Yours is good.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

You could use relative_time to look back yesterday then add an additional condition which will exclude any low volume counts (Assuming the weekends are low counts compared to the week)

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...