Why does Splunk values() return swapped counts?

[Zoblou]

I want to use the values() function because I want to group by fields. If I just use count by I get the correct result but it doesn't look nice. If I use the values function the counts get swapped.

this is how count by returns the results: 

Function                  |    Status  |  count
Authentication     |     Pass     |    10
Authentication     |     Fail       |      3

this is how the values() returns the results:

Function                  |    Status  |  count
Authentication     |     Pass     |    3
                                    |     Fail       |     10

Here is the count by search: 

| stats count by Function,  Status
| table Function, Status, count

Here is the values search:
| stats count by Function,  Status
| stats values(Status) as Status, values(count) as Count by Function
| table Function, Status, Count

So my question is how do I group by Function while getting the correct counts for the status.

[gcusello]

Hi @Zoblou,

the problem is that the values opetion sort data for each one so the values will not correspond,

the only way is to aggregate two fields using eval and then divide after grouping, something like this:

your_search
| stats count by Function,  Status
| eval column=Status."|".count
| stats values(column) AS column by Function
| rex field=column "^(?<Status>[^\|]+)\|(?<count>.+)"
| table Function Status count

  Ciao.

Giuseppe

[Zoblou]

Thank you so much, as you can tell I am new and still learning. But thank you for the help.

[ITWhisperer]

No problem 😀 We all started out the same - welcome to your journey with Splunk!

[ITWhisperer · Accepted solution]

Firstly, it is unlikely that values() will return the status and count values in the order you show because values() will sort them lexicographically so Fail would be before Pass and 10 would be before 3

If you want to preserve the order use list() instead of values()