topic Re: column sorting in descending order after timechart in Splunk Search

How to do column sorting in descending order after timechart?

venky1544 — Thu, 10 Aug 2023 21:20:21 GMT

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

Re: column sorting in descending order after timechart

richgalloway — Wed, 26 Jul 2023 17:28:04 GMT

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.

Re: column sorting in descending order after timechart

gcusello — Wed, 26 Jul 2023 18:19:37 GMT

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

Re: column sorting in descending order after timechart

yuanliu — Fri, 28 Jul 2023 09:07:28 GMT

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.

Re: column sorting in descending order after timechart

venky1544 — Thu, 10 Aug 2023 11:45:50 GMT

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"