Splunk Search

How to calculate the total count of occurances of a timestamp ?

naveenalagu
Explorer

Basically I have a set of raw data with different time stamp in CCYYMMDDHHMMSS format. I want to list out the stats which shows how many occurrences of CCYY then MM then DD . I am able to use STRFTIME to get the segregate the data into desired format as year month and day.
My expected result output is

YearYear CountMonthMonth CountDayDayCount
202210002022-112502022-11-2720
2023102022-121002022-11-125
    2022-11-2735

I used the below

|stats count as total by year, month day

But the actual output is not as expected

YearYear CountMonthMonth CountDayDayCount
2022202022-11202022-11-2720
202252022-1252022-11-125
2022352022-27352022-11-2735

 

Should be simple enough, just not for me. Please help.

Thanks!

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
<yoursearch>
| stats count as DayCount by Day
| appendcols 
    [<yoursearchagain>
| stats count as MonthCount by Month]
| appendcols
    [<yoursearchagain>
| stats count as YearCount by Year]

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

Alternatively, if eventstats gets slow because there are too many events, you can stats as the first command.

| stats count as "Day Count" by year, month day ``` reduces rows to number of days in all events ```
| eventstats sum('Day Count') as "Month Count" by year month
| eventstats sum('Month Count') as "Year Count" by year

Just to point out: the desired output your illustrated is not "occurrences of CCYY then MM then DD," but occurrences of CCYY, then CCYYMM, then CCYYMMDD.  Additionally, 2022-27 is not a very good illustration of Month.

0 Karma

naveenalagu
Explorer

Just to point out: the desired output your illustrated is not "occurrences of CCYY then MM then DD," but occurrences of CCYY, then CCYYMM, then CCYYMMDD.  


This is correct. I would have to further drill down to HH MM SS as well, which would be CCYYMMHHMMSS.  Thanks for the info.


 Additionally, 2022-27 is not a very good illustration of Month.

Apologies for this. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| stats count as DayCount by Year Month Day
| eventstats sum(DayCount) as MonthCount by Year Month
| eventstats sum(DayCount) as YearCount by Year
Tags (1)

naveenalagu
Explorer

Thanks a lot for this.
However, the Table comes out with repeated values for Year and Month.

naveenalagu_0-1675335375095.png

 DEDUP seems to remove entries of other Columns as well. Is there any way to remove those duplicates?

Thanks in advance!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
<yoursearch>
| stats count as DayCount by Day
| appendcols 
    [<yoursearchagain>
| stats count as MonthCount by Month]
| appendcols
    [<yoursearchagain>
| stats count as YearCount by Year]

naveenalagu
Explorer

Thanks! That did the trick.🙂

0 Karma
Get Updates on the Splunk Community!

Observability Newsletter Highlights | March 2023

 March 2023 | Check out the latest and greatestSplunk APM's New Tag Filter ExperienceSplunk APM has updated ...

Security Newsletter Updates | March 2023

 March 2023 | Check out the latest and greatestUnify Your Security Operations with Splunk Mission Control The ...

Platform Newsletter Highlights | March 2023

 March 2023 | Check out the latest and greatestIntroducing Splunk Edge Processor, simplified data ...