topic Re: sort columns that with the highest Total to the left in Splunk Search

sort columns that with the highest Total to the left

damucka — Wed, 25 Mar 2020 15:30:26 GMT

Hello,

I have the following search:

index="_internal" sourcetype="scheduler" thread_id="AlertNotifier*" NOT (alert_actions="summary_index"OR alert_actions="" ) 
| rex field=savedsearch_id "(.+;.+;(?P<title>.+))" 
| timechart  useother=f limit=200 span=1h count by title
| addcoltotals labelfield=_time label="Total Sum"
| addtotals 
| sort _time desc
| table _time, Total, *
| where Total != 0
| rename Total AS "#Alerts/h"

it should build the table of the triggered alerts with the frequency per hour. Basically it works fine, but I would like to achieve one more thing:
- I would like the columns with the highest "Total sum" to be ordered from left to right. The goal is that I can see the alerts being executed the most straight away on the left side of the table.

How would I achieve this?

Kind Regards,
Kamil

Re: sort columns that with the highest Total to the left

to4kawa — Mon, 30 Mar 2020 20:26:42 GMT

index="_internal" sourcetype="scheduler" thread_id="AlertNotifier*" NOT (alert_actions="summary_index"OR alert_actions="" ) 
| rex field=savedsearch_id "(.+;.+;(?P<title>.+))" 
| timechart useother=f limit=200 span=1h count by title 
| addcoltotals labelfield=_time 
| table _time * 
| tail 1 
| fields - _* 
| transpose 0 
| sort - "row 1" 
| rename "row 1" as "Total Sum" 
| transpose 0 header_field=column column_name=_time 
| append 
    [ search index="_internal" sourcetype="scheduler" thread_id="AlertNotifier*" NOT (alert_actions="summary_index"OR alert_actions="" ) 
    | rex field=savedsearch_id "(.+;.+;(?P<title>.+))" 
    | timechart useother=f limit=200 span=1h count by title 
    | reverse] 
| reverse 
| addtotals 
| sort _time desc 
| table _time, Total, * 
| where Total != 0 
| rename Total AS "#Alerts/h"

First: make header with appropriate order.
Second: append original query results. At that time, reverse the time order.
Last: reverse and display them

Re: sort columns that with the highest Total to the left

woodcock — Tue, 31 Mar 2020 16:02:16 GMT

Try padding with leading whitespace like this:

index="_internal" sourcetype="scheduler" thread_id="AlertNotifier*" NOT (alert_actions="summary_index"OR alert_actions="" ) 
| rex field=savedsearch_id "(.+;.+;(?P<title>.+))" 
| timechart useother=f limit=200 span=1h count by title 

| untable _time search errors 
| eventstats sum(errors) AS TOTAL_ERRORS BY search 
| sort 0 TOTAL_ERRORS 
| streamstats dc(search) AS rank 
| eventstats first(rank) AS rank BY search 
| rename COMMENT AS "Pad with spaces to ensure that the search with the most errors show up leftmost!" 
| eval search = printf("%*s", len(search) + rank, search) 
| fields - rank TOTAL_ERRORS
| addcoltotals labelfield=_time label="Total Sum" 
| xyseries _time search errors 

| addtotals 
| sort _time desc 
| table _time, Total, * 
| where Total != 0 
| rename Total AS "#Alerts/h"

Yes, this means we are doing the totalling twice.

Re: sort columns that with the highest Total to the left

adobrzeniecki — Tue, 02 Mar 2021 16:40:04 GMT