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

[mchoudhary]

Hi Everyone!

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

| 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 Month=strftime(_time, "%b")
| stats sum(Blocked) as Blocked by Source Month
| eventstats sum(Blocked) as Total by Source
| appendpipe [ stats values(Total) as Blocked by Source | eval Month="Total" ]
| xyseries Source Month Blocked
| fillnull value=0

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-

| 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

Could someone please help here!

Thanks In advance 🙂

[livehybrid]

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?

| table Source [| makeresults count=12
  | 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
]

|tstats count where index=main by _time span=1d
| eval MonthNum=strftime(_time, "%Y-%m"), MonthName=strftime(_time, "%b")
| eval Source="Email"
| eval Blocked=count
| 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
]

🌟 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

[mchoudhary]

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

[livehybrid · Accepted solution]

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

[mchoudhary]

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

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

[livehybrid]

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

[PrewinThomas]

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

[yuanliu]

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

| 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 span=1month@month
| eval Month=strftime(_time, "%b")
| transpose header_field=month column_name=Source
| eval Source = "Email"
| fillnull value=0
| addtotals

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.

[livehybrid]

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

[mchoudhary]

@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