Splunk Search

Timechart - _time subsearch issue

splunk_zen
Builder

I need to feed several days most busy hour into a weighted score evolution over time,
which I'm running troubles into..
Let's say I the subsearch outputs something like the following into my main search.

(date_month = february AND date_mday = 13 AND date_hour = 20) OR (date_month = february AND date_mday = 14 AND date_hour = 20)

I need to sum some values as components to the final expression, but considering I need the score evolution over time I've added by Platform date_month date_mday date_hour so I don't lose the _time attributes.

My current expression,

(...)
| eval OptimumMem = case(
Platform=="SCPs" AND MemUsedPct <= 60, 1,
Platform=="DSCPs" AND MemUsedPct <= 75, 1,
(...)
Platform=="MGRs" AND MemUsedPct <= 60, 1
)
| eval AcceptableMem = case(
Platform=="SCPs" AND MemUsedPct > 60 AND MemUsedPct <= 75, 1,
Platform=="DSCPs" AND MemUsedPct > 75 AND MemUsedPct <= 90, 1,
(...)
Platform=="MGRs" AND MemUsedPct > 60 AND MemUsedPct <= 75, 1
)
| stats count(MemUsedPct) as TotalEvents sum(OptimumMem) as TotalOptimumMem sum(AcceptableMem) as TotalAcceptableMem by Platform date_month date_mday date_hour
    | fillnull 
| eval ScpMemApdex=if(Platform="SCPs",(TotalOptimumMem + TotalAcceptableMem/2) / TotalEvents,0)
| eval DscpMemApdex=if(Platform="DSCPs",(TotalOptimumMem + TotalAcceptableMem/2) / TotalEvents,0)
(...)
| eval MgrMemApdex=if(Platform="MGRs",(TotalOptimumMem + TotalAcceptableMem/2) / TotalEvents,0)
    | eval ApdexScore=round(100*(0.17*ScpMemApdex + 0.17*DscpMemApdex + 0.4*SdpMemApdex + 0.15*SmpMemApdex + 0.07*BitMemApdex + 0.04*MgrMemApdex), 2)

is returning,

Platform    date_month  date_mday   date_hour   TotalEvents     TotalOptimumMem     TotalAcceptableMem  ApdexScore  DscpMemApdex    MgrMemApdex     ScpMemApdex
DSCPs   february    13  20  48  48  0   **17.00**   1   0   0       
MGRs    february    13  20  36  36  0   **4.00**    0   1   0   

How should I change it so I don't get No results found when adding

| timechart sum(ApdexScore)

?

Tags (2)
1 Solution

jonuwz
Influencer

You need to reconstruct _time. If there's no _time field, timechart will not work

so do something like this before the timechart :

... | eval _time=strptime(date_hour." ".date_mday." ".date_month,"%H %d %B")

Then add :

| timechart sum(ApdexScore)

You may actually want to include the year too ...

View solution in original post

splunk_zen
Builder

Actually, no. Those ** were just to stand out.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

If you bin your original events into hours you could group by _time instead of those three, hence keeping the actual _time all the way through.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Something like bucket _time span=1h

0 Karma

splunk_zen
Builder

Martin, how does the bin expression before stats look like?

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

I was thinking of binning by hour after you determine the day's value, right before the final stats.

0 Karma

splunk_zen
Builder

Martin, I started using that approach, the issue with it is that forcing _time, would return the same value across all the main search timestamps, which would run me into trouble summing them in the timechart expression (whereas I need to compute a single daily value - representative of the busiest hour)

_time Platform TotalEvents TotalOptimumMem TotalAcceptableMem DscpMemApdex ScpMemApdex
13/02/2013 20:00:00.000 DSCPs 4 4 0 1 0
13/02/2013 20:05:00.000 DSCPs 4 4 0 1 0
13/02/2013 20:10:00.000 DSCPs 4 4 0 1 0
13/02/2013 20:15:00.000 DSCPs 4 4 0 1 0
13/02/2013 20:20:00.000 DSCPs 4 4 0 1 0

0 Karma

jonuwz
Influencer

You need to reconstruct _time. If there's no _time field, timechart will not work

so do something like this before the timechart :

... | eval _time=strptime(date_hour." ".date_mday." ".date_month,"%H %d %B")

Then add :

| timechart sum(ApdexScore)

You may actually want to include the year too ...

splunk_zen
Builder

Flawless !
Thank you jonuwz, really.
I was getting trouble wrapping my head over what was the culprit.

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

Are the ** on the ApdexScore part of the field value?

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 ...