Splunk Search

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

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

 Year Year Count Month Month Count Day DayCount 2022 1000 2022-11 250 2022-11-27 20 2023 10 2022-12 100 2022-11-12 5 2022-11-27 35

I used the below

``|stats count as total by year, month day``

But the actual output is not as expected

 Year Year Count Month Month Count Day DayCount 2022 20 2022-11 20 2022-11-27 20 2022 5 2022-12 5 2022-11-12 5 2022 35 2022-27 35 2022-11-27 35

Thanks!

Labels (2)

• ### table

1 Solution
SplunkTrust
``````<yoursearch>
| stats count as DayCount by Day
| appendcols
[<yoursearchagain>
| stats count as MonthCount by Month]
| appendcols
[<yoursearchagain>
| stats count as YearCount by Year]``````
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.

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.

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)
Explorer

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

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

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

Thanks! That did the trick.🙂

Get Updates on the Splunk Community!

#### Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

#### Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

#### Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...