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

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

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

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

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

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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...