Sort Column Value in Table from Rex Field

[bruceaperez]

Hi,

I'm trying to sort a value on a table from a rex field in Splunk Search.  For instance, I have below value:

Date	Host	Count
Wed_Mar_03/12/2021_12:30:01_EDT	mn4.cioprd.lc	4295
Wed_Mar_03/12/2021_12:40:01_EDT	mn3.ciodev.lc	2182
Wed_Mar_03/12/2021_12:30:01_EDT	hive3.CIOPRD.LC	1273
Wed_Mar_03/12/2021_12:30:01_EDT	hive2.cioprd.lc	1202
Wed_Mar_03/12/2021_12:40:01_EDT	mn4.ciodev.lc	1118

I would like to sort this by Host starting with ".cioprd.local".  The table should look like this.

Date	Host	Count
Wed_Mar_03/12/2021_12:30:01_EDT	mn4.cioprd.lc	4295
Wed_Mar_03/12/2021_12:30:01_EDT	hive2.cioprd.lc	1202
Wed_Mar_03/12/2021_12:30:01_EDT	hive3.CIOPRD.LC	1273
Wed_Mar_03/12/2021_12:40:01_EDT	mn3.ciodev.lc	2182
Wed_Mar_03/12/2021_12:40:01_EDT	mn4.ciodev.lc	1118

I tried the using the eval from this doc, but no luck.  Can you please help me on this?  Thanks.

[scelikok]

Hi @bruceaperez,

Thank you. Please try below;

| rex field=Host "\.(?<host_domain>[^\s]+)"
| sort - host_domain count
| fields - host_domain

[bruceaperez]

Hi @scelikok  - it seems it didn't work.  Here's my search command.

.... | rex field=_raw "(?<Date>[^\|]+)\|(?<Host>[^\|]+)\| (?i)Count=(?<count>[^\|]+)"
| fields + Date, Host, count
| search "mn4.ciodev.lc" OR "mn3.ciodev.lc" OR "hive3.CIOPRD.LC" OR "hive2.cioprd.lc" OR "mn4.cioprd.lc"
| eval Date = strptime(Date,"%a_%b_%m/%d/%Y_%H:%M:%S_%Z") | sort 0 - Date | eval Date = strftime(Date,"%a_%b_%m/%d/%Y_%H:%M:%S_%Z")
| table Date, Host, count
| dedup Host

[scelikok]

Hi @bruceaperez,

Your sort by criteria is not certain and I couldn't guess it by looking at your output sample. Do you want to extract one part of host? If yes which part? 

[bruceaperez]

Hi @scelikok - i want to sort it by host and I want to arrange it by environment. Basically, I want to arrange it first with strings containing "cioprd.lc" then "ciodev.lc".  After I have arranged the host on this manner, then I will have to sort the Count in descending order.

[scelikok]

Hi @bruceaperez,

I think the problem was uppercase environment. Below changing environment to lowercase should work for you.

| rex field=Host "\.(?<host_domain>[^\s]+)" 
| eval host_domain=lower(host_domain) 
| sort - host_domain count
| fields - host_domain