Splunk Search

How to count mixed NULL/NOT NULL result sets

Sebastian2
Path Finder

Let's say I got a table as search result like this:

Object Name                               | Field_A                                 | Field_B
_____________________________________________________________________________________________________________
object_a                                  | value_AA                                | value_AA
object_b                                  |                                         | value_BB
object_c                                  | value_A                                 |
object_d                                  | value_DD                                | value_XX
object_e                                  |                                         |
object_f                                  | value_FF                                | value_FF
object_g                                  | value_GG                                |
object_h                                  | value_AA                                | value_AA

So I got different objects, with maybe different maybe not different values. The information I want:
1. How many objects do have a Value in Field A AND Field B while Field A AND Field B do have the same value
2. How many objects do have a Value in Field A AND Field B while Field A AND Field B do NOT have the same value
3. How many objects do have a value in Field_A but not in Field_B
4. How many objects do have a value in Field_B but not in Field_A
5. How many objects have both, Field_A and Field_B empty

So my desired result would look something like this:

Both and equal (1)    | Both but Different (2) | Only F_A (3) | Only F_B(4) | Both empty (5)
_____________________________________________________________________________________________________________
3                     | 1                      | 2            | 1           | 1
0 Karma
1 Solution

somesoni2
Revered Legend

Try something like this

your base search | eval Type=case(isnotnull(Field_A) AND isnotnull(Field_B) AND Field_A=Field_B,"Both and equal", isnotnull(Field_A) AND isnotnull(Field_B) AND Field_A!=Field_B,"Both but different",isnotnull(Field_A) AND isnull(Field_B)," Only F_A", isnull(Field_A) AND isnotnull(Field_B),"Only F_B",1=1,"Both empty") | eval temp=1 | chart count over temp by Type | fields - temp

Just added field temp for chart command to produce columns for each value of field Type.

View solution in original post

somesoni2
Revered Legend

Try something like this

your base search | eval Type=case(isnotnull(Field_A) AND isnotnull(Field_B) AND Field_A=Field_B,"Both and equal", isnotnull(Field_A) AND isnotnull(Field_B) AND Field_A!=Field_B,"Both but different",isnotnull(Field_A) AND isnull(Field_B)," Only F_A", isnull(Field_A) AND isnotnull(Field_B),"Only F_B",1=1,"Both empty") | eval temp=1 | chart count over temp by Type | fields - temp

Just added field temp for chart command to produce columns for each value of field Type.

Sebastian2
Path Finder

sorry 4 my late answer - that worked out of the box. Thanks!

0 Karma
Get Updates on the Splunk Community!

New Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...