Splunk Search

Timechart into stats?

dennywebb
Path Finder

So... I have a weird one I can't seem to find much info on.

I want to use the per_second() command, but i want a sum of it's results. The real scenario is complicated but for simplification lets say we have csv data that returns:

_time,feet,name
###,100,steve
###,70,bob
###,200,steve
###,120,bob

and i want to know the feet per second, by name.

timechart per_second(feet) as Fps by name

no problem. makes a nice chart of feet per second, by name, for each time tick. but what i want is a table, that shows a sum of:

Name    Fps
------------
Bob    23
Steve  42

(numbers in that example made up)

when i try to do:

timechart per_second(feet) as Fps by name | stats sum(FPS) by Name

i get zilch.

0 Karma

sideview
SplunkTrust
SplunkTrust

Well stats sum(FPS) by Name just takes incoming rows, looks at all the "Name" fields and "FPS" fields, and does what it does. On the other hand the output rows of timechart per_second(feet) as Fps by name do not have any 'Name' fields, thus no values for "Name". Those output rows have only '_time', 'Fps', and then a number of fields that are each values of the 'name' field.

As is often the case, the answer is to leave the data in "stats world" a while longer. Think of stats as being the real mover and shaker, and timechart as being this flashy trick you sometimes do at the end to make a pretty chart. In complex scenarios you might use several different stats commands in a single search, and then only at the end pipe it to timechart or chart.

So the stats style version of timechart is just to use bin and stats manually, and in some cases eval to match whatever foo() functions you were doing. In your case it would look like this: NOTE: I don't know what the granularity of your timechart is, so I picked 1 minute:

| bin _time span="1min" | stats sum(feet) as feet count by name _time | eval fps=feet/60 | table name fps

From here, although it's an odd thing to do statistically, you can sum up the raw fps numbers by name, by tacking on another stats. I'll assume naively that you're not interested in accounting for the 'count' field as a weighting - that you literally want to sum up the feet-per-second numbers...

| bin _time span="1min" | stats sum(feet) as feet count by name _time | eval fps=feet/60 | table name fps | stats sum(fps) by name

sideview
SplunkTrust
SplunkTrust

per_second basically does a count, and then divides by the number of seconds in the timebucket, no matter what size timechart has chosen for those timebucket. It still seems quite strange to take per-second numbers and then just add them into eachother. For one thing you're then at the mercy of the granularity choice that timechart uses - For instance run the same search over a larger timerange, timechart will choose different bucketing, now your summed FPS numbers are totally different... For this reason I figured you were already specifying an explicit span in timechart.

0 Karma

dennywebb
Path Finder

well the real goal, perhaps more relevant to this than i had suspected, is that i've got a packet monitor running over the system. it reports source and packet sized as they go from machine to machine. i want to know the Mbps used by each IP within my report range.

meaning i don't know what the granularity of my entries would be either. perhaps i'm misunderstanding what per_second() does? or misunderstanding what you're saying.

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...