Dashboards & Visualizations

How to get a peak count while still including current counts?

dirtebird
Explorer

I have this query that gets current CURRENT_OUT counts by DISTRICT

index=<my index> sourcetype=oracle:query source=<source> | fields DISTRICT, OUT_CUSTS | where _time>relative_time(now(),"-5m") | stats sum(OUT_CUSTS) as CURRENT_OUT by DISTRICT | table DISTRICT, CURRENT_OUT | sort by DISTRICT

This works to get current counts because the db source is updated every 5 minutes in splunk DB connect.  I get a nice table of CURRENT_OUT by DISTRICT.

Is is possible to expand this to add a peak value for CURRENT_OUT over, say, the last 24 hours, while still including the current CURRENT_OUT value in the table as well?  I'm looking at the bin command but I can't put it together.  Once I expand my timeframe for my query, I'm bringing back way too much data an overinflating the current CURRENT_OUT  values for each DISTRICT.  Thanks

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Does something like this work for you?

index=<my index> sourcetype=oracle:query source=<source> earliest=-1d 
| bin span=5m _time
| stats sum(OUT_CUSTS) as CURRENT_OUT by DISTRICT, _time
| stats max(CURRENT_OUT) as PEAK_OUT latest(CURRENT_OUT) as CURRENT_OUT by DISTRICT
| sort by DISTRICT
| fields DISTRICT, CURRENT_OUT, PEAK_OUT

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Does something like this work for you?

index=<my index> sourcetype=oracle:query source=<source> earliest=-1d 
| bin span=5m _time
| stats sum(OUT_CUSTS) as CURRENT_OUT by DISTRICT, _time
| stats max(CURRENT_OUT) as PEAK_OUT latest(CURRENT_OUT) as CURRENT_OUT by DISTRICT
| sort by DISTRICT
| fields DISTRICT, CURRENT_OUT, PEAK_OUT

dirtebird
Explorer

Wow!  This is so close to what I needed, thank you!  The only issue is with the latest(CURRENT_OUT) 

| stats max(CURRENT_OUT) as PEAK_OUT latest(CURRENT_OUT) as CURRENT_OUT by DISTRICT

 

Many times, a district will not have any data for a given time slice.  I would only want to bring back a non-zero value if the timestamp was the "latest" time slice, here's an example where 8:00am is "latest":

_timeDISTRICTOUT_CUSTS
8:00amA15
7:55amA10
7:50amA23
7:55amB1
7:50amB3

 

Desired result:

DISTRICTCURRENT_OUTPEAK_OUT
A1523
B03

 

Actual result:

DISTRICTCURRENT_OUTPEAK_OUT
A1523
B13

 

Here B has no 8:00am data.  latest(CURRENT_OUT) returns the value 1 from the 7:55am time slice, but I want to see 0 because there is no data for district B for 8:00am.   Is there a way to accomplish this without changing the way data is being fed into splunk?

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this - note that timechart uses the earliest and latest settings to determine which time period to use.

| timechart span=5m sum(OUT_CUSTS) by DISTRICT
| fillnull value=0
| untable _time DISTRICT CURRENT_OUT
| stats max(CURRENT_OUT) as PEAK_OUT latest(CURRENT_OUT) as CURRENT_OUT by DISTRICT

dirtebird
Explorer

This works, however the other wrinkle is that I have another column i want to do a count() on called INCIDENT_ID.

| timechart span=5m count(INCIDENT_ID) as CURRENT_INCIDENTS sum(OUT_CUSTS) as CURRENT_OUT by DISTRICT 
| fillnull value=0 
| untable _time DISTRICT CURRENT_INCIDENTS_OR_OUT

Gives me results that look like 

_timeDISTRICTCURRENT_INCIDENTS_OR_OUT
10:00CURRENT_INCIDENTS: ABC5
10:00CURRENT_INCIDENTS: XYZ4
10:00CURRENT_OUT: ABC1
10:00CURRENT_OUT: XYZ1

 

So I end up with the INCIDENT count and CURRENT_OUT sum in the same column

Am I out of luck?  Either way, what you've been able to show me is nothing short of wizardry and I thank you for your help!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It is a bit more complicated, but try this

| timechart span=5m sum(OUT_CUSTS) as OUT_CUSTS count(INCIDENT_ID) as INCIDENTS by DISTRICT
| fillnull value=0
| foreach "INCIDENTS: *"
    [| eval "<<MATCHSEG1>>"='<<FIELD>>']
| foreach "OUT_CUSTS: *"
    [| eval "<<MATCHSEG1>>"='<<MATCHSEG1>>'.":".'<<FIELD>>']
| fields - INCIDENT* OUT_CUSTS*
| untable _time DISTRICT CURRENT_INCIDENT_OUT
| eval CURRENT_OUT=mvindex(split(CURRENT_INCIDENT_OUT,":"),0)
| eval INCIDENTS=mvindex(split(CURRENT_INCIDENT_OUT,":"),1)
| stats max(CURRENT_OUT) as PEAK_OUT latest(CURRENT_OUT) as CURRENT_OUT latest(INCIDENTS) as INCIDENTS by DISTRICT

dirtebird
Explorer

This worked perfectly!  Amazing to run your search step by step to see how you solved this.  You gave me an education in Splunk, thank you very much for your efforts!  I'm marking your first answer as the solution since it's simpler and solves the original question that was asked.  Thanks again for your help!

0 Karma

thesplunkmonkey
Path Finder

I generally hate recommending any solution that includes any sort of subsearch due to the performance hit, but here is one potential option for you.

index=<my index> sourcetype=oracle:query source=<source> earliest=-5m
| stats sum(OUT_CUSTS) as CURRENT_OUT by DISTRICT 
| append 
    [ index=<my index> sourcetype=oracle:query source=<source> earliest=-1d 
    | bin span=5m _time
    | stats sum(OUT_CUSTS) as CURRENT_OUT by DISTRICT, _time
    | stats max(OUT_CUSTS) as PEAK_OUT by DISTRICT] 
| stats values(PEAK_OUT) as PEAK_OUT, values(CURRENT_OUT) as CURRENT_OUT by DISTRICT
| sort by DISTRICT
| fields DISTRICT, CURRENT_OUT, PEAK_OUT

 

dirtebird
Explorer

It didn't like the index command inside the search.  I didn't spend much time troubleshooting it, since the answer above ended up working for my needs.  Thank you for taking the time to reply.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...