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!

Say goodbye to manually analyzing phishing and malware threats with Splunk Attack ...

In today’s evolving threat landscape, we understand you’re constantly bombarded with phishing and malware ...

AppDynamics is now part of Splunk Ideas

Hello Splunkers, We have exciting news for you! AppDynamics has been added to the Splunk Ideas Portal. Which ...

Advanced Splunk Data Management Strategies

Join us on Wednesday, May 14, 2025, at 11 AM PDT / 2 PM EDT for an exclusive Tech Talk that delves into ...