topic Re: summarize stats by month in Splunk Search

summarize stats by month

mvasquez21 — Mon, 03 Mar 2025 15:17:57 GMT

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!

Re: summarize stats by month

kiran_panchavat — Mon, 03 Mar 2025 15:26:03 GMT

@mvasquez21

Re: summarize stats by month

mvasquez21 — Mon, 03 Mar 2025 15:31:17 GMT

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

Re: summarize stats by month

kiran_panchavat — Mon, 03 Mar 2025 15:34:47 GMT

@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

Re: summarize stats by month

kiran_panchavat — Mon, 03 Mar 2025 15:44:49 GMT

@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

Re: summarize stats by month

mvasquez21 — Mon, 03 Mar 2025 15:41:33 GMT

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:

Re: summarize stats by month

kiran_panchavat — Mon, 03 Mar 2025 15:42:43 GMT

@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.

Re: summarize stats by month

kiran_panchavat — Mon, 03 Mar 2025 15:44:24 GMT

@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

Re: summarize stats by month

mvasquez21 — Mon, 03 Mar 2025 15:44:40 GMT

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.

Re: summarize stats by month

kiran_panchavat — Mon, 03 Mar 2025 15:45:43 GMT

@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

Re: summarize stats by month

mvasquez21 — Mon, 03 Mar 2025 15:47:14 GMT

perfect! you are a geniius

Re: summarize stats by month

mvasquez21 — Mon, 03 Mar 2025 15:56:14 GMT

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

Re: summarize stats by month

kiran_panchavat — Mon, 03 Mar 2025 16:09:13 GMT

@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

Re: summarize stats by month

kiran_panchavat — Mon, 03 Mar 2025 16:10:19 GMT

@mvasquez21

Refer my output:-

Re: summarize stats by month

mvasquez21 — Mon, 03 Mar 2025 16:28:49 GMT

that last one seems to undo the month summarizing