Splunk Enterprise

Can I list the index with 0 count of the field value in a table or chart ?

noman81
Engager

Hi,

We have some fields in the index and within the field there are some values with different occurrence counts. for example:

The index is let suppose "index01". The field is "REQ_STATUS" and the values of field are "Blocked", "Alerted" and "Passed". All values have different counts. For example :

Example : 1

Index = "Index01"

Field

REQ_STATUS

Value

Blocked

Alerted
Passed

Counts

30
50
100

  • Blocked has 30 occurrences, Alerted has 50 and Passed has 100.

Example : 2

Index ="Index02"

Field

REQ_STATUS

Value

Alerted
Passed

Counts

50
100

  • Blocked has no occurrences while Alerted has 50 and Passed has 100.

My question is if there are multiple indexes with different field value counts, can I list only those specific indexes in the table or chart who have no occurrences/counts of any of the Fied 'Values' like the Index02 with no 'Blocked' value as shown in Example:2.
Please advise.

Tags (1)
0 Karma

woodcock
Esteemed Legend

Like this:

Your Base Search Here With No Pipes
| append 
    [| makeresults count=3 
    | eval index="DUMMY" 
    | streamstats count AS _serial 
    | eval REQ_STATUS=case((_serial==1), "Alerted", (_serial==2), "Blocked", true(), "Passed") ] 
| chart count OVER index BY REQ_STATUS
| search index!="DUMMY"
| untable index REQ_STATUS count
| search count=0
0 Karma

DalJeanis
SplunkTrust
SplunkTrust
(your base search here)
| table index REQ_STATUS Count 
| appendpipe [ | table index | dedup index | eval Count = 0 | eval REQ_STATUS =mvappend("Blocked","Alerted","Passed") | mvexpand REQ_STATUS]
| stats Sum(Count) as Count by index REQ_STATUS
| search Count=0

What the above code does -

First, get rid of everything but the three fields you need. You have one record for each existing combination of index and REQ_STATUS. Presumably every index has at least ONE record of one REQ_STATUS.

Next, appendpipe processes all those records, dedups them and produces a cross-join set of every index against every REQ_STATUS, with a zero Count.

Finally, we add all those zero records to the existing records with the stats command. Any combination that still has zero after that, is a combination that doesn't exist on your summaries.


*Splunk efficiency experts and internal SMEs- *

Assuming a very large set of input-- unlikely to happen in this exact case, but go with me here -- in which order should the "| table index" and "| dedup index" within the appendpipe go? I'm assuming that you would table them then sort/dedup, but I'm open to knowing that splunk is smart enough to have some tricks that makes that unnecessary or less efficient than dedup then table.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...