summarize stats by month

[mvasquez21]

I have this search to see logins to our splunk environment:

  index = _audit user="*" action="login attempt" info=succeeded | stats count by user

mgmt is asking to see the same data but instead of a "count" column, they want a column for each month. I assume it will be a table of some sort but can't figure out the date summarizing.

Here is an example of the individual entry:

Audit:[timestamp=03-03-2025 09:10:52.577, user=xxxxxx, action=login attempt, info=succeeded reason=user-initiated useragent="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Safari/537.36" clientip=xxx.xxx.xxx.x" method=LDAP" session=17a169464fada764a1bac7310cac4c47]

columns should be:  user   monthA    monthB   monthc

with the counts under each month

Thanks!

[mvasquez21]

that last one seems to undo the month summarizing

[mvasquez21]

one last thing. this is listing the months alphabetically. any way to do it chronologically?

[kiran_panchavat]

@mvasquez21 

Yes, you can definitely display the months chronologically instead of alphabetically. To achieve this, you need to convert the month representation (e.g., "Jan 2024") into a sortable format, like a timestamp or a year-month string (e.g., "2024-01").

index = _audit user="*" action="login attempt" info=succeeded
| eval _time=relative_time(now(), "-".(random()%180)."d") 
| eval month=strftime(_time, "%Y-%m-%d"), sort_month=strftime(_time, "%Y-%m-%d") 
| chart count over user by month 
| sort + sort_month

[kiran_panchavat]

@mvasquez21 

Refer my output:-

[mvasquez21]

when i try to append my search with it i get this error: Error in 'makeresults' command: This command must be the first command of a search.

index = _audit user="*" action="login attempt" info=succeeded | makeresults count=20
| eval _time=relative_time(now(), "-".(random()%180)."d")
| eval user="user".tostring(1+random()%5)
| eval action="login attempt", info="succeeded"
| eval month=strftime(_time, "%b %Y")
| chart count over user by month

[kiran_panchavat · Accepted solution]

@mvasquez21 

Don't append makeresults in your query:-

Use this 

index = _audit user="*" action="login attempt" info=succeeded
| eval _time=relative_time(now(), "-".(random()%180)."d") 
| eval month=strftime(_time, "%b %Y") 
| chart count over user by month

[mvasquez21]

perfect! you are a geniius

[mvasquez21]

when using this one:

| makeresults count=20 
| eval _time=relative_time(now(), "-".(random()%180)."d") 
| eval user="user".tostring(1+random()%5) 
| eval action="login attempt", info="succeeded" 
| eval month=strftime(_time, "%b %Y") 
| chart count over user by month

my results don't show the username:

[kiran_panchavat]

@mvasquez21 

makeresults is a command in Splunk that generates synthetic (fake) data for testing, debugging, and query development without using an actual index. You have to pass your original query. 

 

[kiran_panchavat]

@mvasquez21 

You have to use this query:

index = _audit user="*" action="login attempt" info=succeeded
| eval _time=relative_time(now(), "-".(random()%180)."d") 
| eval month=strftime(_time, "%b %Y") 
| chart count over user by month

[kiran_panchavat]

@mvasquez21 

Try this 

index = _audit user="*" action="login attempt" info=succeeded
| eval _time=relative_time(now(), "-".(random()%180)."d") 
| eval month=strftime(_time, "%b %Y") 
| chart count over user by month

[mvasquez21]

could i ask of you to paste that so my bad typing doesn't mess it up? Thanks so much!

[kiran_panchavat]

@mvasquez21 

| makeresults count=20 
| eval _time=relative_time(now(), "-".(random()%180)."d") 
| eval user="user".tostring(1+random()%5) 
| eval action="login attempt", info="succeeded" 
| eval month=strftime(_time, "%b %Y") 
| chart count over user by month

[kiran_panchavat]

@mvasquez21