topic Re: unable to sort the month fields chronological instead of alphabetically in output in Splunk Search

unable to sort the month fields chronological instead of alphabetically in output

mchoudhary — Tue, 27 May 2025 10:13:35 GMT

Hi Everyone!

I wrote a search query to get the blocked count of emails for last 6months and below is my query-

and its output looks something like this -

The only issue is in the output the month field is not chronologically sorted instead it is alphabetical. I intend to sort it chronologically. I tried with the below query as well to achieve the desired output but no go-

Could someone please help here!

Thanks In advance 🙂

Re: unable to sort the month fields chronological instead of alphabetically in output

livehybrid — Tue, 27 May 2025 10:16:11 GMT

Hi @mchoudhary

The easiest way might be to add a table on the end, something like this:

| table Source Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec *

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

Re: unable to sort the month fields chronological instead of alphabetically in output

mchoudhary — Tue, 27 May 2025 10:23:40 GMT

@livehybrid The issue is in my query I am fetching data for last 6 months. so If someone run the query till date it will give results from December till now and also there is 0 count for some months, so it will look blank. something like this if I hardcode the months

Re: unable to sort the month fields chronological instead of alphabetically in output

yuanliu — Wed, 28 May 2025 07:18:33 GMT

Instead of computing month before xyseries, it's better to carry _time into xyseries and use transpose to get your final layout. Unlike xyseries, transpose preserves row order into column order.

But then, given that you only have one prescribed "source", I wonder if xyseries and streamstats are a waste. How about

Here, I removed the first stats sum because by using span=1mon@mon in tstats, that calculation is already done. I also removed eventstats and streamstats because total on row is more easily performed with addtotals.

Re: unable to sort the month fields chronological instead of alphabetically in output

PrewinThomas — Wed, 28 May 2025 07:45:21 GMT

@mchoudhary
You can try below,

| tstats summariesonly=false dc(Message_Log.msg.header.message-id) as Blocked from datamodel=pps_ondemand
where (Message_Log.filter.routeDirection="inbound")
AND (Message_Log.filter.disposition="discard" OR Message_Log.filter.disposition="reject" OR Message_Log.filter.quarantine.folder="Spam*")
earliest=-6mon@mon latest=now by _time
| eval Source="Email"
| eval MonthNum=strftime(_time, "%Y-%m"), MonthName=strftime(_time, "%b")
| stats sum(Blocked) as Blocked by Source MonthNum MonthName
| eventstats sum(Blocked) as Total by Source
| appendpipe [ stats values(Total) as Blocked by Source | eval MonthNum="9999-99", MonthName="Total" ]
| sort MonthNum
| eval Month=MonthName
| table Source Month Blocked

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a kudos. Thanks!

Re: unable to sort the month fields chronological instead of alphabetically in output

livehybrid — Wed, 28 May 2025 10:31:03 GMT

Okay @mchoudhary - this might look a little bizarre but stay with me....you could use the following table output, this uses a search to determine the months returned based on the earliest/latest set by the time picker and lists them out as per the screenshot below. Would this work for you?

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

Re: unable to sort the month fields chronological instead of alphabetically in output

mchoudhary — Wed, 28 May 2025 18:48:38 GMT

@livehybrid ,

I tried the same query as you suggested, not sure why it is giving me data only for May month 😞

| tstats summariesonly=false dc(Message_Log.msg.header.message-id) as Blocked from datamodel=pps_ondemand where (Message_Log.filter.routeDirection="inbound") AND (Message_Log.filter.disposition="discard" OR Message_Log.filter.disposition="reject" OR Message_Log.filter.quarantine.folder="Spam*") earliest=-6mon@mon latest=now by _time | eval Source="Email" | eval MonthNum=strftime(_time, "%Y-%m"), MonthName=strftime(_time, "%b") | stats sum(Blocked) as Blocked by Source MonthNum MonthName | xyseries Source MonthName Blocked | addinfo | table Source [| makeresults count=60 | streamstats count as month_offset | addinfo | eval start_epoch=info_min_time, end_epoch=info_max_time | eval start_month=strftime(start_epoch, "%Y-%m-01") | eval month_epoch = relative_time(strptime(start_month, "%Y-%m-%d"), "+" . (month_offset-1) . "mon") | where month_epoch <= end_epoch | eval month=strftime(month_epoch, "%b") | stats list(month) as search ]

Re: unable to sort the month fields chronological instead of alphabetically in output

livehybrid — Wed, 28 May 2025 20:31:40 GMT

Ah! Okay, this is because you're using earliest/latest not the time-picker, we can fix that - try the below table section instead:

| table Source [| makeresults count=7 | streamstats count as month_offset | eval start_epoch=relative_time(now(),"-6mon@mon"), end_epoch=now() | eval start_month=strftime(start_epoch, "%Y-%m-01") | eval month_epoch = relative_time(strptime(start_month, "%Y-%m-%d"), "+" . (month_offset-1) . "mon") | where month_epoch <= end_epoch | eval month=strftime(month_epoch, "%b") | stats list(month) as search ]

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

Re: unable to sort the month fields chronological instead of alphabetically in output

mchoudhary — Thu, 29 May 2025 12:00:41 GMT

@livehybrid It worked like magic! 🙂 Thank you so much.

Also, if you could explain the logic behind it, I would be grateful 🙂

Re: unable to sort the month fields chronological instead of alphabetically in output

livehybrid — Thu, 29 May 2025 12:46:33 GMT

Absolutely @mchoudhary

So, what we are doing here is using a subsearch within the "table" command to generate the list of months you are interested in. Not many people realise but you can use a subsearch in a lot more places than as part of an original search, e.g. to derive a variable for timechart span, or in our case to list some fields for your table command.

Regarding the subsearch, this is what it is doing:

1. | makeresults count=7

Generates 7 dummy events (rows) to work with in the pipeline. (6 months ago + current month)

2. | streamstats count as month_offset

For each of the 7 rows, assigns a sequential number in month_offset (from 1 to 7).
This will be used to generate one value per month, going backwards in time.

3. | eval start_epoch=relative_time(now(),"-6mon@mon"), end_epoch=now()

start_epoch calculates the epoch time at the start of the month, six months ago.
- -6mon@mon: Go back 6 months, then snap to the beginning of the month.
end_epoch is the current epoch time.
This sets the time range: from the start of 6 months ago until now.

4. | eval start_month=strftime(start_epoch, "%Y-%m-01")

Formats start_epoch into a string representing the first day of the starting month (e.g., "2024-11-01").

5. | eval month_epoch = relative_time(strptime(start_month, "%Y-%m-%d"), "+" . (month_offset-1) . "mon")

For each row, this creates a timestamp for a month in the range.
Increments from start_month by (month_offset - 1) months.
- month_offset runs 1 to 7.
- So, months generated will be: start_month + 0, +1, +2, ..., +6 months.
This way, you get the start-of-month epoch for each month in the range.

6. | where month_epoch <= end_epoch

Filters out any months whose starting epoch is greater than now (in case the 7 generated months go slightly into the future).

7. | eval month=strftime(month_epoch, "%b")

Converts month_epoch into a "short month name" format (e.g., "Jan", "Feb", etc).

8. | stats list(month) as search

Aggregates the results into a single row, with the months as a list, titled "search".

This is then returned from the subsearch as a list which is consumed by the table command.

If you ran the search by itself you would get:

Please let me know if you have any further questions on this! I'm really pleased to have got to the bottom of it!

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing