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

[SS1]

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

[ITWhisperer]

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]

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]

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                      0                 4

Can we avoid 0 moving to the bottom?