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!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...