topic Re: Sort Column Value in Table from Rex Field in Splunk Search

Sort Column Value in Table from Rex Field

bruceaperez — Wed, 24 Mar 2021 17:05:55 GMT

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.

Re: Sort Column Value in Table from Rex Field

scelikok — Wed, 24 Mar 2021 17:48:05 GMT

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?

Re: Sort Column Value in Table from Rex Field

bruceaperez — Wed, 24 Mar 2021 18:04:02 GMT

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.

Re: Sort Column Value in Table from Rex Field

scelikok — Wed, 24 Mar 2021 19:01:28 GMT

Hi @bruceaperez,

Thank you. Please try below;

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

Re: Sort Column Value in Table from Rex Field

bruceaperez — Wed, 24 Mar 2021 22:41:55 GMT

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

Re: Sort Column Value in Table from Rex Field

scelikok — Thu, 25 Mar 2021 04:28:36 GMT

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