Splunk Search

adding field to timechart

Thomas
New Member

How could I add and additional (in my case total) field after the timechart is grouped by a field (e.g. httpcode)

| bucket _time bins=100 | eventstats count as total by _time | stats count first(total) as total by _time, httpcode | eval percent=(count/total)*100 | convert ctime(_time) as time | timechart span=1h first(percent) by httpcode | fillnull

I would like to see the following:

Datetime (span=1h) - 200 - 302 - 400 - 404 - 499 - 500 - total
8/10/11 12:00:00.000 AM - 0.857756 - 89.063617 - 0 - 0.142959 - 7.862759 - 0.929235

and so on.

Tags (2)
0 Karma

acdevlin
Communicator

I think I have the result you're looking for. It's a bit of an ugly query, so I'll walk through it here.

First off, I took the liberty of cleaning up your initial query; I eliminated a few unnecessary parts and renamed some variables which I thought were unclear (such as all the "total"s). At the end of this first step, I had this:

<your search> | bucket _time span=1h | eventstats count as rowTotal by _time | eventstats count AS "statusTotal" by _time, status | eval percent=(statusTotal/rowTotal)*100 | timechart span=1h first(percent) by status 

Now, in order to find the new "total" column you're looking for, we have to calculate the total for each row (which we have, as the field "rowTotal") and divide it by the overall number of events (which is easy to find), then multiply by 100. Unfortunately, we need to use "appendcols" and repeat our previous seach in order to actually get this percentage onto our resulting timechart, which does reduce performance. On the other hand, if we simply tried to throw a "values(totalPercent)" statement into our existing timechart, it would list the overall percentage for EACH status...which makes our results table look messy and redundant.

As such, I used appendcols since I believe proper formatting is your goal here. The section to append this overall percentage looks like this:

... | appendcols[ <your search> | eventstats count as overallTotal | bucket _time span=1h | eventstats count as rowTotal by _time | eval totalPercent=(rowTotal/overallTotal)*100 | timechart span=1h values(totalPercent)]

With all that done, our overall query with the two sections combined looks like this:

<your search>| bucket _time span=1h | eventstats count as rowTotal by _time | eventstats count AS "statusTotal" by _time, status | eval percent=(statusTotal/rowTotal)*100 | timechart span=1h first(percent) by status | appendcols[<your search> | eventstats count as overallTotal | bucket _time span=1h | eventstats count as rowTotal by _time | eval totalPercent=(rowTotal/overallTotal)*100 | timechart span=1h values(totalPercent)]

It is not pretty by any means, but it does get the results which I think you want. And sorry for the long explanation.

0 Karma

Thomas
New Member

Yes I want to get a table for each httpcode and the occurrence for each one per hour calculated as percentage value with the number of total occurrence for all httpcode in one hour to get a feeling for the traffic per hour.

0 Karma

David
Splunk Employee
Splunk Employee

I think what you're looking for is addtotals.

Here's an example: http://splunk-base.splunk.com/answers/23037/how-to-add-totals-line-for-stats

Does that get you what you're looking for?

0 Karma

acdevlin
Communicator

I'm a little confused by your overall goal for this query. Right now, it looks to me like you want:

1) The total number of events in a timespan of 1 hour, and

2) The percentage of the time each httpcode appears in the same timespan

Is this correct? Or were you looking for something else?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...