Splunk Search

Time after stats command

hcelep
Engager

Hey,

 

I want to add _time column after stats command but I couldn't select the best command. Forexample;

 

index=*
| eval event_time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| stats count by user, ip, action
| iplocation ip
| sort -count



hcelep_0-1736343851401.png

 

How can I add this field?

 

Thanks

 

 

Labels (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @hcelep ,

let us know if we can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

0 Karma

isoutamo
SplunkTrust
SplunkTrust

Hi

another guys already show to you how you can technically add _time or event_time into your stats command. But I think that much more important thing is to understand and decide what _time you are needing with stats? Usually stats is used to make some statistics aggregations for some values or another option is join some data together. In both of these case you must understand your data and what you really want to show with your _time field value. I suppose that in many cases it's much harder to make decisions what _time you need to use. Use _time from one event, make some span e.g. 1min, 1hour etc. of calculate average or median time of events or something else?

r. Ismo

richgalloway
SplunkTrust
SplunkTrust

The stats command discards all fields not mentioned in the command so, in this case, only the count, user, ip, and action fields are available.  Fields cannot be re-added after they've been discarded by such a command.  

The solution is to include the desired field(s) in the stats command.

| stats count by event_time, user, ip, action

This may or may not make sense depending on your data and the desired output.

---
If this reply helps you, Karma would be appreciated.

sjringo
Contributor

Are you trying to perform the stats by _time also ?

Just add your event_time into the stats command.  Change the event_time format to only Hour and Minute or just by hour ?

index=*
| eval event_time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| stats count by event_time, user, ip, action
| iplocation ip
| sort -count

gcusello
SplunkTrust
SplunkTrust

Hi @hcelep ,

after the stats command, you have only the fileds in the command, in your case: count, user, ip and action.

If you want alto the _time, you have to add it to the stats command.

You have two methods to do this:

  • add it to the BY clause, 
  • choose the first or the last value for the groups.

in the first case, remember to group the timestamps using the bin command:

index=*
| eval event_time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| bin span=1h _time
| stats count by _time user ip action
| iplocation ip
| sort -count

in the second case, taking e.g. the first occurrence:

index=*
| eval event_time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| stats count earliest(_time) AS _time BY user ip action
| iplocation ip
| sort -count

Ciao.

Giuseppe

Get Updates on the Splunk Community!

How to send events & findings from AWS to Splunk using Amazon EventBridge

Amazon EventBridge is a serverless service that uses events to connect application components together, making ...

Exciting News: The AppDynamics Community Joins Splunk!

Hello Splunkers,   I’d like to introduce myself—I’m Ryan, the former AppDynamics Community Manager, and I’m ...

The All New Performance Insights for Splunk

Splunk gives you amazing tools to analyze system data and make business-critical decisions, react to issues, ...