topic Count days with events in Splunk Search

Count days with events

NaorPenso — Mon, 28 Sep 2020 16:46:31 GMT

Hi everyone,
I have looked all over for a solution but without luck, so i'm approaching you hoping to find a solution.
I would like to count the amount of days (not the amount of events) that has events based on a ctime value in the log.

Sample log:

1398963749 1398963749 1432 1404320549
0 0 10446976 15988 2 4 0
DD_Servers_2003-2008_1398963749_FULL.f
NULL NULL 0 1 0 2 1399050149 NULL 1 0 0 0 0 0 0 NULL 0 0 0 NULL 777220 1 0 1999619 0 0 NULL 3 1398963602 3 0 NULL NULL 0 1 0 0

The first field states the EPOCH time of the event start (i should mention right away that Splunk time is not relevant as it is the time of insertion to the index, not this field)
what I do is:

index="foo" | convert ctime(bar)

From here on im stuck as to how to count the amount of days with the event (i.e. 33 days with events)

Thanks in advance

Re: Count days with events

lguinn2 — Sun, 01 Jun 2014 16:02:39 GMT

I would do it like this

index="foo"
| bucket ctime span=1d
| stats dc(ctime) as numberofuniquedays

If you want to check that this makes sense, try

index="foo"
| bucket ctime span=1d
| stats count by ctime
| fieldformat ctime=strftime(ctime,"%x %X")

BTW, you can get Splunk to use the ctime of the event - and you probably should. It will make a lot of the reports easier and more sensible. Here is how do that:

props.conf

[sourcetypeOfData]
MAX_TIMESTAMP_LOOKAHEAD = 11
TIME_FORMAT = %s

This props.conf goes on the indexer(s), or wherever the data is being parsed.

Re: Count days with events

NaorPenso — Sun, 01 Jun 2014 16:26:33 GMT

Hi,
Thanks a lot, you steered me to the correct path.
it didn't work exactly as i wanted so what i did is:

index="foo" |
convert ctime(bar) as Time timeformat=%m/%d/%y |
bucket Time span=1d |
stats dc(Time) as numberofuniquedays

Thanks again!!