How to do column sorting in descending order after timechart?

[venky1544]

Hello splunkers,

i have a simple timechart query for avg USED_SPACE of disks for last 4 days 

index=abc sourectype=disk_data |timechart span=24h avg(USED_SPACE) by DISK limit=10 usenull=f useother=f

below is the result 

TIME	DATApoint	MADOD	MA_ODP	MA_PD	MA_PI	MA_PSD	MA_PSI	MA_P_DBCI	MA_P_DBIN	MA_P_DCRED	MA_P_DEMS	MA_P_DFDB	MA_P_DFDMS	MA_P_DFDP
22/07/2023 01:00	476054.33	39856.33	20105.25	106848.78	23032.06	703847.03	74755.98	20326.13	28006.5	20959.31	21400.05	21921.19	76224.02	83074.48
23/07/2023 01:00	476054.33	39864.33	20104.81	106848.78	23032.06	703847.03	74755.98	20326.13	28006.5	20959.31	21400.05	21921.19	76224.02	83074.48
24/07/2023 01:00	476166.33	39872.33	20105.25	106855.78	23032.06	703847.03	74755.98	20326.13	28022.5	20975.31	21400.05	21937.19	76228.02	83074.48
25/07/2023 01:00	476238.33	39880.33	20105.19	106862.78	23032.06	703851.03	74757.05	20326.13	28038.5	20975.31	21400.05	21953.19	76232.02	83074.48

My question is i understand DATApoint Disk has highest value so its first column but why is MADOD column in 2nd place its should be on 7th or 8th  column as other disk have higher used space and if ilimit to 5  it gives me wrong info because MA_PSD should be in 1st column and its in 7th 

any suggestion how can i rearrange the columns based on used_space after timechart i'm expecting something like below 

TIME	MA_PSD	DATApoint	MA_PD	MA_P_DFDP	MA_P_DFDMS	MA_PSI	MADOD	MA_P_DBIN	MA_P_DFDB	MA_P_DEMS	MA_P_DCRED	MA_P_DBCI	MA_PI	MA_ODP
22/07/2023 01:00	703847.03	476054.3	106848.8	83074.48	76224.02	74755.98	39856.33	28006.5	21921.2	21400.05	20959.31	20326.13	23032.06	20105.25
23/07/2023 01:00	703847.03	476054.3	106848.8	83074.48	76224.02	74755.98	39864.33	28006.5	21921.2	21400.05	20959.31	20326.13	23032.06	20104.81
24/07/2023 01:00	703847.03	476166.3	106855.8	83074.48	76228.02	74755.98	39872.33	28022.5	21937.2	21400.05	20975.31	20326.13	23032.06	20105.25
25/07/2023 01:00	703851.03	476238.3	106862.8	83074.48	76232.02	74757.05	39880.33	28038.5	21953.2	21400.05	20975.31	20326.13	23032.06	20105.19

[venky1544]

Hi @yuanliu @gcusello @richgalloway 

 apologies for very late acknowledgement was down under the whether 

i kind of tweaked the data based on field values i did a total and then arranged them based on descending and then removed the total it kind of worked for me i added the below code after the timechart 

| addcoltotals labelfield=_time label="TOTAL" | transpose header_field="_time" 0

| sort - TOTAL | transpose header_field="column" 0

| rename column as _time| search _time != "TOTAL"

[yuanliu]

I had a similar situation.  SPL (and really, any language) is not so good with calculating across columns.  So, first use stats to keep values of interest in rows before attempting timechart.

Is your desire to limit chart to top five USED_SPACE, or to simply order by USED_SPACE?  Either way, let me first clarify that such comparison only makes sense with cumulative numbers, not numbers in each time period.

The following illustrates how to limit chart to top five cumulative (average) USED_SPACE.

index=abc sourectype=disk_data
```| timechart span=24h avg(USED_SPACE) by DISK limit=10 usenull=f useother=f```
| bin span=24h _time
| stats avg(USED_SPACE) as avg_USED by DISK _time ``` keep interesting data in rows ```
| eventstats sum(avg_USED) as sum_USED by DISK
| eval sum_USED = printf("%10d", sum_USED*100) ``` zero pad to 2nd decimal ```
| eventstats values(sum_USED) as top_USED
| eval top_USED = mvindex(top_USED, -5, -1) ``` get top 5 ```
| where sum_USED == top_USED
| timechart values(avg_USED) by DISK

Hope this helps.

[gcusello]

Hi @venky1544 ,

as @richgalloway said "The columns produced by timechart are in lexicographical order by field *name* rather than field value."

So it's possible to force the order only you have a fixed a limitated number of columns:

index=abc sourectype=disk_data 
| eval DISK=case(DISK="MA_PSD","01MA_PSD", DISK="DATApoint","02DATApoint", 
 DISK="MA_PD","03MA_PD", DISK="MA_P_DFDP","04MA_P_DFDP", DISK="MA_P_DFDMS","05MA_P_DFDMS", DISK="MA_PSI","06MA_PSI", DISK="MADOD","07MADOD", DISK="MA_P_DBIN","08MA_P_DBIN", DISK="MA_P_DFDB","09MA_P_DFDB", DISK="MA_P_DEMS","10MA_P_DEMS", DISK="MA_P_DCRED","11MA_P_DCRED", DISK="MA_P_DBCI","12MA_P_DBCI", DISK="MA_PI","13MA_PI",	DISK="MA_ODP","14MA_ODP")
| timechart span=24h avg(USED_SPACE) by DISK limit=10 usenull=f useother=f
| rename 
   01MA_PSD AS MA_PSD
   02DATApoint AS DATApoint 
   03MA_PD AS MA_PD
   04MA_P_DFDP AS MA_P_DFDP
   05MA_P_DFDMS AS MA_P_DFDMS
   06MA_PSI AS MA_PSI
   07MADOD AS MADOD
   08MA_P_DBIN AS MA_P_DBIN
   09MA_P_DFDB AS MA_P_DFDB
   10MA_P_DEMS AS MA_P_DEMS
   11MA_P_DCRED AS MA_P_DCRED
   12MA_P_DBCI AS MA_P_DBCI
   13MA_PI AS MA_PI
   14MA_ODP AS MA_ODP 

Ciao.

Giuseppe

[richgalloway]

The columns produced by timechart are in lexicographical order by field *name* rather than field value.  Sorting by value is not practical because columns may change order as values change over time.