Splunk Search

How to get time to propogate through stats command?

brajaram
Communicator

Events in my sourcetype contain a build time, and an ID field. A given ID can have multiple events, and each event contains a build time. I'm trying to get the sum of build times across IDs.

So far, my query looks like:
index=... | stats sum(buildTime) AS sum by id | stats mean(sum)

This provides the mean of the total build time per ID. But what I want is to build a timechart of this. How would I propagate the time field across to do that?

If I do:
| timechart limit=0 sum(buildTime) as Sum by id| timechart mean(Sum)
It doesn't build the table properly, because id becomes the column names and time becomes the row labels. How do I set the initial query up so that I can build a chart to see net buildTime over time?

Tags (3)
0 Karma
1 Solution

brajaram
Communicator

The solution is | eventstats sum(buildTime) as SumID by id| table SumID _time id| dedup id| stats mean(SumID)

Eventstats attaches to each event the sum of build times for a given id. Then I table it with time to propogate time forward in the query, dedup ID to remove multiple instances of id, to get a 1:1 matching of id:SumID, then timechart off of that.

View solution in original post

0 Karma

brajaram
Communicator

The solution is | eventstats sum(buildTime) as SumID by id| table SumID _time id| dedup id| stats mean(SumID)

Eventstats attaches to each event the sum of build times for a given id. Then I table it with time to propogate time forward in the query, dedup ID to remove multiple instances of id, to get a 1:1 matching of id:SumID, then timechart off of that.

0 Karma

adonio
Ultra Champion

maybe something like this:
index=... | stats sum(buildTime) AS sum by id _time | timechart mean(sum)
stats will strip the fields that arent specified, you wish to keep the _time field so you can timechart down the road.

hope it helps

brajaram
Communicator

Nope, that didn't work - it produced values, but it was producing the wrong values. However, I may have gotten it to work with

| eventstats sum(buildTime) as SumID by id| timechart mean(SumID)

What I don't understand is why that worked, but it seems to provide values that are correct.

0 Karma

adonio
Ultra Champion

eventstats does not drop the fields, it adds summary statistics, see here:
http://docs.splunk.com/Documentation/Splunk/7.0.3/SearchReference/Eventstats
glad you figure it out.
if its set, please mark the question as answered or place your resolution in another answer and mark as answered

0 Karma

brajaram
Communicator

It looks like I was wrong about the accuracy. If I do mean(SumID) it takes each event's sumID and aggregates that.

However, if 1 ID has 2 events, and another ID has 4 events, then it will take the second ID's SumID and use that 4 times, and the first ones 2 times, to calculate the mean. This means its incorrectly weighs toward ID's that have more events, and doesn't provide the true value of buildTimes.

What eventstats does do is correctly calculate for each event, the sum of the buildTime for each ID and attach it to every event for the specific ID of the event. I can then use |table SumID _time id | dedup id to get a table with _time and a 1:1 matching of SumID and id.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...