Splunk Search

When using appendcols, one column doesn't have events?

SS1
Path Finder

My Query: 

index=test sourcetype=true AND private AND beta |rex field=_raw "\[private]\s(?<category>\S+\s+\S+\s+\S+)" |dedup category, source|eval category=upper(category)| stats count by category |rename count as count1| appendcols [search index=test sourcetype=true AND private AND alpha |rex field=_raw "\[private]\s(?<category>\S+\s+\S+\s+\S+)" |dedup category, source|eval category=upper(category)| stats count by category |rename count as count2]| eval Total=(count1-count2)

So when the 2nd query doesn't have any events i am not getting the Total column

Current output if the 2nd search doesn't have any events:

category      count1      

xxxx                  5

 

Desired output:

category      count1         count2     Total

xxxx                  5                    0                  5

Labels (6)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
index=test sourcetype=true AND private AND beta OR alpha
|rex field=_raw "\[private]\s(?<category>\S+\s+\S+\s+\S+)" 
|dedup category, source
|eval category=upper(category)
| eval ab=if(searchmatch("beta"),"beta","alpha")
| chart count by category ab 
| eval Total=(beta-alpha)

gcusello
SplunkTrust
SplunkTrust

Hi @SS1,

if you search in Community, there are many questions about how to add a zero value when there isn't any result, so, please, try something like this:

index=test sourcetype=true AND private AND beta 
| rex field=_raw "\[private]\s(?<category>\S+\s+\S+\s+\S+)" 
| dedup category, source|eval category=upper(category)
| stats count by category 
| rename count as count1
| appendcols [ search 
   index=test sourcetype=true AND private AND alpha 
   | rex field=_raw "\[private]\s(?<category>\S+\s+\S+\s+\S+)" 
   | dedup category, source 
   | eval category=upper(category)
   | stats count by category 
   | append [ | makeresults | eval count=0 | fields count ]
   | rename count as count2 ]
| eval Total=(count1-count2)

There's only one thing tha I don't understand: in the subsearch there's a stats by category, so you haven't one value but one for each category, but appendcols doesn't associate the value to the same category!

To do this, you need the join command (to avoid because it's a very slow command) or a different approach using stats: you can find many examples of this approach in other answers.

Ciao.

Giuseppe

SS1
Path Finder

Hi @gcusello , 

 

Thanks for the response.

Tried your suggestion but facing an issue,

Lets say below is the correct output,

category      count1         count2     Total

xxxx                  5                    2                  3

yyyy                    3                   0                  3

zzzz                 4                      2                  2

 

But in splunk 0 is moving to the bottom, this is how it is showing in splunk

 

category      count1         count2     Total

xxxx                  5                    2                  3

yyyy                    3                   2                 1

zzzz                 4                                     4

 

Can we avoid 0 moving to the bottom?

0 Karma
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 ...