Time after stats command

[hcelep]

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

How can I add this field?

Thanks

[gcusello]

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 😉

[isoutamo]

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]

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.

[sjringo]

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]

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