Splunk Search

Split Timechart into stats by field

mfudali
Explorer

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:

Actormaxavgp50p99
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

 

Labels (4)
0 Karma
1 Solution

PickleRick
Ultra Champion

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.

View solution in original post

somesoni2
Revered Legend

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

PickleRick
Ultra Champion

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?

0 Karma

mfudali
Explorer

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:

mfudali_0-1632843075261.png

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:

mfudali_1-1632843223311.png

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.

 

 

0 Karma

PickleRick
Ultra Champion

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?

0 Karma

mfudali
Explorer

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

mfudali_0-1632844349306.png

 

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

 

0 Karma

PickleRick
Ultra Champion

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.

mfudali
Explorer

Perfect! Thank you so much

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>