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!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...