Splunk Search

Why does Splunk values() return swapped counts?

Zoblou
Engager

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.


 

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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()

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

Zoblou
Engager

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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()

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...