topic How to calculate the total count of occurances of a timestamp ? in Splunk Search

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

naveenalagu — Thu, 02 Feb 2023 01:47:42 GMT

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

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

Thanks!

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

ITWhisperer — Thu, 02 Feb 2023 08:14:24 GMT

| stats count as DayCount by Year Month Day | eventstats sum(DayCount) as MonthCount by Year Month | eventstats sum(DayCount) as YearCount by Year

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

yuanliu — Thu, 02 Feb 2023 10:25:31 GMT

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.

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

naveenalagu — Thu, 02 Feb 2023 10:54:19 GMT

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.

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

naveenalagu — Thu, 02 Feb 2023 10:58:49 GMT

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?

Thanks in advance!

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

ITWhisperer — Thu, 02 Feb 2023 12:36:22 GMT

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

naveenalagu — Thu, 02 Feb 2023 15:45:35 GMT

Thanks! That did the trick.🙂