Splunk Search

When using stats to display values() of  fields, how can we have the values to align between the field names?

neerajs_81
Builder

Hi All,  When using stats  to display values() of  fields , how can we have the values to align between the field names ?  For example
My Data set

Severity Status Count
P1 New 1
P1 Open 2
P1 Unassigned 3
P1 Closed 5


When using | stats values(status) as status, values(Count) as Count by severity
this is what i get.  Notice the count values are not as per dataset.

Severity Status Count
P1 New
Open
Unassigned
Closed
1
5
3
2


i did like the results of Count to align as per their Status field.

Expected Result

Severity Status Count
P1 New
Open
Unassigned
Closed
1
2
3
5
Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @neerajs_81,

in values option, values are sorted in alphabetically way, so yu'll never had the correct alignment between  different fields, the only way is aggregate them before the stats command and separating them after, something like this:

<your_search>
| eval column=Status."|".Count
| stats values(column) AS column values(Status) AS Status BY Severity
| rex field=column "(?<Count>\d+)$"
| table Severity Status Count

I cannot test it but it should run!

Ciao.

Giuseppe

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @neerajs_81,

in values option, values are sorted in alphabetically way, so yu'll never had the correct alignment between  different fields, the only way is aggregate them before the stats command and separating them after, something like this:

<your_search>
| eval column=Status."|".Count
| stats values(column) AS column values(Status) AS Status BY Severity
| rex field=column "(?<Count>\d+)$"
| table Severity Status Count

I cannot test it but it should run!

Ciao.

Giuseppe

0 Karma

neerajs_81
Builder

That worked. Thank you

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @neerajs_81 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma
Get Updates on the Splunk Community!

.conf23 | Get Your Cybersecurity Defense Analyst Certification in Vegas

We’re excited to announce a new Splunk certification exam being released at .conf23! If you’re going to Las ...

Streamline Data Ingestion With Deployment Server Essentials

REGISTER NOW!Every day the list of sources Admins are responsible for gets bigger and bigger, often making the ...

Remediate Threats Faster and Simplify Investigations With Splunk Enterprise Security ...

REGISTER NOW!Join us for a Tech Talk around our latest release of Splunk Enterprise Security 7.2! We’ll walk ...