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
Example : 2
Index ="Index02"
Field
REQ_STATUS
Value
Alerted
Passed
Counts
50
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.
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
(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.