Splunk Search

Time based counting.

jerinvarghese
Communicator

Hi All,

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

Table: 

Timesitecodecount
2020-08-21FAW1
2020-08-21FAW1
2020-08-21FAW1
2020-08-21FAW1
2020-08-21FAW1

 

Query: 

 

 

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

 

 

 

Expected output: 

Timesitecodecount
2020-08-21FAW5
Labels (4)
0 Karma
1 Solution

yeahnah
Motivator

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.

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

yeahnah
Motivator

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.

0 Karma

jerinvarghese
Communicator
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")
0 Karma

rnowitzki
Builder

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.

--
Karma and/or Solution tagging appreciated.
0 Karma

jerinvarghese
Communicator

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.

 

0 Karma

rnowitzki
Builder

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.
 

--
Karma and/or Solution tagging appreciated.
0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...