Time based counting.

[jerinvarghese]

Hi All,

need your help in getting the count correct for the below table.

Table: 

Time	sitecode	count
2020-08-21	FAW	1
2020-08-21	FAW	1
2020-08-21	FAW	1
2020-08-21	FAW	1
2020-08-21	FAW	1

Query: 

index=moogsoft_e2e 
| eval Time = _time
| fieldformat Time=strftime(Time,"%Y-%m-%d")
| sort - Time
| stats count by Time, sitecode

Expected output: 

Time	sitecode	count
2020-08-21	FAW	5

[ITWhisperer]

Hi @jerinvarghese 

Set bin to 1 day before your stats clause

index=moogsoft_e2e 
| eval Time = _time
| fieldformat Time=strftime(Time,"%Y-%m-%d")
| sort - Time
| bin Time span=1d
| stats count by Time, sitecode

[yeahnah · Accepted solution]

Hi @jerinvarghese

The issue you have is using fieldformat for Time field instead of instead of eval.  Check the Splunk docs for the difference and you should be able to work out why.

Also note, depending on how much data you are searching, it is far more efficient to do evals/formats after transforming the data set, as it reduces it size.  So something like this is better practise...

index=moogsoft_e2e 
| bin _time span=1d
| stats count by _time sitecode
| sort - _time
  `comment("# only if you want to change the column header")`
| fieldformat Time=strftime(_time,"%Y-%m-%d")

 Hope it helps.

[jerinvarghese]

Thanks for the reply, got that worked with Eval before this solution. Seems Eval is simpler than other methods. Sorry to others too. I got the answer worked with eval statement. | eval Time=strftime(_time,"%Y-%m-%d %l:%M:%S %p")

[rnowitzki]

Hi @jerinvarghese ,

You don't tell us what your issue is. The SPL looks ok.

I guess it is related to the fieldname "count" which could cause Problems with the operator "count" in SPL.

Because all the counts are "1" in your example, it does not make a difference, but try if you want to addup all the counts and you have cases where it's not "1" for all rows.:

| stats sum(count) by Time, sitecode

Hope it helps.

BR
Ralph
--
Karma and/or Solution tagging appreciated.

[jerinvarghese]

HI Ralph,

Sorry If my question didn't gave enough info.

Problem : I have same sitecode giving individual values that happened over the same day.

My aim was to get it that captured together, Like if the sitecode "FAW" created 4 events in 21st Aug, I should get 4 in the count field. But that is not happening based on time.

If I do below code. am getting correct value as FAW and 4 in table.

| stats count by sitecode

But my aim was to find out per-day basis how many events generated for each site.

[rnowitzki]

Hi @jerinvarghese ,

If I understand your requirement correct now, you should be able to get this with timechart:

| timechart span=1d count by sitecode

BR
Ralph
--
Karma and/or Solution tagging appreciated.