Splunk Search

Sort Column Value in Table from Rex Field

bruceaperez
New Member

Hi,

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

DateHostCount
Wed_Mar_03/12/2021_12:30:01_EDTmn4.cioprd.lc4295
Wed_Mar_03/12/2021_12:40:01_EDTmn3.ciodev.lc2182
Wed_Mar_03/12/2021_12:30:01_EDThive3.CIOPRD.LC1273
Wed_Mar_03/12/2021_12:30:01_EDThive2.cioprd.lc1202
Wed_Mar_03/12/2021_12:40:01_EDTmn4.ciodev.lc1118

 

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

DateHostCount
Wed_Mar_03/12/2021_12:30:01_EDTmn4.cioprd.lc4295
Wed_Mar_03/12/2021_12:30:01_EDThive2.cioprd.lc1202
Wed_Mar_03/12/2021_12:30:01_EDThive3.CIOPRD.LC1273
Wed_Mar_03/12/2021_12:40:01_EDTmn3.ciodev.lc2182
Wed_Mar_03/12/2021_12:40:01_EDTmn4.ciodev.lc1118

 

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

Labels (2)
0 Karma

scelikok
SplunkTrust
SplunkTrust

Hi @bruceaperez,

Thank you. Please try below;

| rex field=Host "\.(?<host_domain>[^\s]+)"
| sort - host_domain count
| fields - host_domain
If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

bruceaperez
New Member

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

0 Karma

scelikok
SplunkTrust
SplunkTrust

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? 

If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

bruceaperez
New Member

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.

0 Karma

scelikok
SplunkTrust
SplunkTrust

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
If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma
Get Updates on the Splunk Community!

Splunk Classroom Chronicles: Training Tales and Testimonials (Episode 3)

Welcome back to Splunk Classroom Chronicles, our ongoing blog series that pulls back the curtain on Splunk ...

Operationalizing TDIR: Building a More Resilient, Scalable SOC

Optimizing SOC workflows with a unified, risk-based approach to Threat Detection, Investigation, and Response ...

Almost Too Eventful Assurance: Part 1

Modern IT and Network teams still struggle with too many alerts and isolating issues before they are notified. ...