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

[noman81]

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.

[woodcock]

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

[DalJeanis]

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