Timechart - _time subsearch issue

[splunk_zen]

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)

?

[splunk_zen]

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

[martin_mueller]

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.

[martin_mueller]

Something like bucket _time span=1h

[splunk_zen]

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

[martin_mueller]

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

[splunk_zen]

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

[jonuwz · Accepted solution]

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]

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

[alacercogitatus]

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