topic Timechart - _time subsearch issue in Splunk Search

Timechart - _time subsearch issue

splunk_zen — Mon, 28 Sep 2020 13:31:09 GMT

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)

Re: Timechart - _time subsearch issue

alacercogitatus — Thu, 14 Mar 2013 19:44:34 GMT

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

Re: Timechart - _time subsearch issue

jonuwz — Thu, 14 Mar 2013 20:51:03 GMT

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

Re: Timechart - _time subsearch issue

martin_mueller — Thu, 14 Mar 2013 21:17:09 GMT

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.

Re: Timechart - _time subsearch issue

splunk_zen — Fri, 15 Mar 2013 08:59:21 GMT

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

Re: Timechart - _time subsearch issue

splunk_zen — Fri, 15 Mar 2013 09:19:14 GMT

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

Re: Timechart - _time subsearch issue

splunk_zen — Fri, 15 Mar 2013 11:49:01 GMT

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

Re: Timechart - _time subsearch issue

martin_mueller — Fri, 15 Mar 2013 12:57:38 GMT

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

Re: Timechart - _time subsearch issue

splunk_zen — Mon, 18 Mar 2013 11:45:21 GMT

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

Re: Timechart - _time subsearch issue

martin_mueller — Mon, 18 Mar 2013 11:48:03 GMT

Something like bucket _time span=1h