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

[neerajs_81]

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

[gcusello · Accepted solution]

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

[neerajs_81]

That worked. Thank you

[gcusello]

Hi @neerajs_81 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉