topic Re: Split Timechart into stats by field in Splunk Search

Split Timechart into stats by field

mfudali — Sat, 25 Sep 2021 00:24:51 GMT

Hello,

I have the query :

hostalias=$hostname$ AND actor AND total | timechart span=1s count by actor | stats

This returns the stats for all the actors into a row, but I wanted to have a table where each row indicates a specific actor and the resulting max/avg/p50/p99 statistics for that actor.

Something like below:

Actor	max	avg	p50	p99
actorName1
actorName2
actorName3

I tried the following query, but nothing returned:

hostalias=$hostname$ AND actor AND total | timechart span=1s count as TPS | stats

max(TPS) as maxTPS avg(TPS) as avgTPS p50(TPS) as p50TPS p99(TPS) as p99TPS by actor

I had something similar working before, but there was no timechart involved. Is this possible to do with timechart?

Thanks for any insights

Re: Split Timechart into stats by field

PickleRick — Sat, 25 Sep 2021 05:43:43 GMT

Do you want to have those stats separately per each second? That's easy - just add other stats functions after "count" in your timechart

Do you want the stats for the whole period? Do a separate search.

You're trying to transform the original data (do a timechart) but then reach to the original events again. It doesn't work that way. In general, after each pipe character you "lose" information of what happened before that pipe.

So if you do an aggregation by using stats or timechart, you can no longer perform aggregations on raw data. In some cases (like counting events) you can simply add up stats values and it gives you sane results but if you aggregated events and returned just number of events you cannot count percentiles on the original data.

So the question is - what exactly do you want to calculate?

Re: Split Timechart into stats by field

mfudali — Tue, 28 Sep 2021 15:37:28 GMT

Hi,

Thanks for replying. So I wanted to query a box for logs that contain the 'actor' and 'total' fields, then I wanted to count how many of those logs are happening each second per 'actor' and I wanted to get the stats per 'actor' as well.

When I queried,

hostalias=$hostname$ AND actor AND total | timechart span=1s count by actor | stats

This was the result:

Which is great but now when I want to extract the relevant information from stats (such as max, avg, p50, p99), I can no longer split the data up 'by actor'.

I would like the result to look more like this:

Which was create by this query: hostalias=$hostname$ AND actor | stats
max(total) as maxT avg(total) as avgT p50(total) as p50T p99(total) as p99T by actor

I think I need to store the actor field because I lose that information after piping the results of the timechart to the stats function, but I'm not sure how to do that either. Thanks again.

Re: Split Timechart into stats by field

PickleRick — Tue, 28 Sep 2021 15:49:43 GMT

Well, firstly, doing a timechart and then _counting_ the resulting values seems a bit pointless, don't you think? You're not counting your data. You're counting the time...

Can't you drop the final stats from the first search and just do your stats by actor?

Re: Split Timechart into stats by field

mfudali — Tue, 28 Sep 2021 15:53:10 GMT

Essentially this is counting the transactions per second, here's an output of the timechart:

You can see that there are multiple transactions for an actor in a given second.

Re: Split Timechart into stats by field

somesoni2 — Tue, 28 Sep 2021 15:57:06 GMT

Try something like this:

hostalias=$hostname$ AND actor AND total | bucket span=1s _time | stats count as TPS by _time actor | stats max(TPS) as maxTPS avg(TPS) as avgTPS p50(TPS) as p50TPS p99(TPS) as p99TPS by actor

Re: Split Timechart into stats by field

PickleRick — Tue, 28 Sep 2021 16:45:50 GMT

OK, sorry, I "mislooked" at my data. You're right, if you don't specify any fields, the stats do a set of predefined stats functions on any column.

And I understand that you want to generate a set of stats over every actor separetely?

How about

hostalias=$hostname$ AND actor AND total
| timechart span=1s count by actor
| untable _time actor value
| stats by actor

Or whatever stats aggregations you want to use.

Re: Split Timechart into stats by field

mfudali — Tue, 28 Sep 2021 17:25:29 GMT

Perfect! Thank you so much