Splunk Search

How to timechart the count of a field by day?

jbleich
Path Finder

hello all, relative newbie here, so bare with me.

I have a table output with 3 columns Failover Time, Source, Destination (This data is being sent over via syslog from a sonicwall)

Anyways, I would like to do a count by events by day. Below is the first 19 entries from the Failover Time column. If I do a [stats count by "Failover Time"] i just get each of the entries and a count of 1. I dont kow how to count by the DAY within the "Failover Time" field....looking at the below example you see there was 1 on 4-1, 2 on 4-2, 3 on 4-3, that's what i want in a chart.....

2015-04-01 02:13:48 UTC

2015-04-02 09:56:50 UTC

2015-04-02 13:18:25 UTC

2015-04-04 02:27:02 UTC

2015-04-04 02:27:57 UTC

2015-04-04 02:53:27 UTC

2015-04-07 11:42:01 UTC

2015-04-08 05:03:57 UTC

2015-04-09 18:13:54 UTC

2015-04-13 15:04:14 UTC

2015-04-13 18:29:59 UTC

2015-04-13 23:09:14 UTC

2015-04-16 05:00:42 UTC

2015-04-16 05:25:42 UTC

2015-04-16 05:30:33 UTC

2015-04-16 05:30:52 UTC

2015-04-16 05:47:48 UTC

2015-04-16 05:55:38 UTC

2015-04-16 05:55:57 UTC

What I would like to see is
Failover Time........................................Count
2015-04-01............................................1
2015-04-02............................................2
2015-04-04............................................3
2015-04-07............................................1
etc....

Tags (2)
1 Solution

stephanefotso
Motivator

I know you can do it several ways, but you can use this query:

   your base search |rename "Failover Time" as failovertime|rex field=failovertime "(?<day>\d+\-\d+\-\d+)"|chart count by day|rename day as "Failover Time"
SGF

View solution in original post

0 Karma

chimell
Motivator

Hi jbleich
Try this search code

 ......| timechart span=1d count by "Failover Time"  limit=0  where count >1

fdi01
Motivator

try like this:

...| bucket span=24h _time |stats count by _time|...
or
...| bucket span=24h "Failover Time" |stats count by "Failover Time"|...
or
...| bucket span=1d _time |stats count by _time|...
0 Karma

Flynt
Splunk Employee
Splunk Employee

Why not just substring it for the day? Following up on Runals comment. The timechart answers given are fine for _time. It looks as if "Failover Time" is a field other than _time and you end up with a column for _time and columns for each of your "Failover Time" values. No matter how you slice it, |timechart count span=1d by "Failover Time" is going to give you the same wrong output you have seen because timechart is going to use _time and not any other timefield in your data and split this out in columns over your field "Failover Time".

So you have two easy ways to do this.

With a substring -

 your base search |eval "Failover Time"=substr('Failover Time',0,10)|stats count by "Failover Time"

or if you really want to timechart the counts explicitly make _time the value of the day of "Failover Time" so that Splunk will timechart the "Failover Time" value and not just what _time is originally in the events.

your base search |eval date2=substr('Failover Time',0,10)|eval _time=strptime(date2,"%Y-%m-%d")|timechart span=1d count |eval "Failover Time"= strftime(_time,"%Y-%m-%d")|table "Failover Time" count
0 Karma

stephanefotso
Motivator

I know you can do it several ways, but you can use this query:

   your base search |rename "Failover Time" as failovertime|rex field=failovertime "(?<day>\d+\-\d+\-\d+)"|chart count by day|rename day as "Failover Time"
SGF
0 Karma

jbleich
Path Finder

Thank you everybody for your help, i marked the first answer that worked 🙂 Looks nice!

0 Karma

Runals
Motivator

I would do it similarly to this though what you will likely want to do is make an additional field where you change the day field into epoch time, sort based on that field, and then get rid of the epoch time field. I'd probably use stats vs chart but I don't think that would result in different formatting of the result really.

0 Karma

stephane_cyrill
Builder

Hi, try this

•••••|stats count by date_mday "Failover time" source |where count > 1 |addcoltotals count

0 Karma

treinke
Builder

Try the following:

 | timechart span=1d count by "Failover Time"
There are no answer without questions
0 Karma

jbleich
Path Finder

Tony has got me going in the right direction with timechart, but it stinks the way it displays, it has the days going down the side and the events as a column with an "other" as the last column, what i want is the events where count>1 to list the date going down the side and then a total count column....

Here is what i have right now with the | timechart span=1d count by time
_time................04-01-2015........04-02-2015..........04-02-2015.........04-04-015..............OTHER
2015-01-17.............0...........................0...........................0..........................0...........................0....
2015-01-18.............0...........................0...........................0..........................0...........................0....
2015-01-19.............0...........................0...........................0..........................0...........................0....
2015-01-20.............0...........................0...........................0..........................0...........................0....
and so on.......

0 Karma
Get Updates on the Splunk Community!

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

Announcing the public preview of Splunk Data Management Ingest Processor

We are pleased to announce the public preview of Splunk’s latest data management capability - Ingest Processor ...

Splunk Lantern is up for three awards! Can we get your vote?

It’s been a big news month for Splunk Lantern. Along with celebrating the publication of our 1000th article, ...