map not working with zero value data?

[bfaber]

There is probably a better way to do this, but I am trying to catalog what rules are (and are not) used using the firewall log and a list of rules. I came up with this search, but it does not show me rules that are matched zero times (even though the left hand side is the rule list):

sourcetype=RULELIST | chart list(rule) | map search=search sourcetype=FW-LOG | stats count(rule) by rule 

FW-LOG looks like this

   date=11111 rule=2   
   date=11112 rule=3
   date=11113 rule=3  
   date=11114 rule=4

RULELIST looks like this

rule=1,ruledesc=rule1
rule=2,ruledesc=this is rule2
rule=3,ruledesc=some other rule
rule=4,ruledesc=blah

What I expect to see is this:

rule          count
1               0
2               1
3               2
4               1

But instead, I see this:

 rule          count
 2               1
 3               2
 4               1

And I really want to know what rules are NOT getting used. Maybe there is an option to map? Any thoughts on this? TIA

[gkanapathy]

I'm not sure you're using map correctly up there, but it sort of doesn't matter, because I'd suggest a different way below, and the "right" way (actually the way you're trying to make it work) would give you the same results as above anyway. It's just the same as sourcetype=FW-LOG | stats count by rule. For the record, you'd need something like: sourcetype=RULELIST | map search="search sourcetype=FW-LOG rule=$rule$" | stats count by rule or sourcetype=RULELIST | map search="search sourcetype=FW-LOG rule=$rule$ | stats count by rule". But for your data, it probably won't be different.

[gkanapathy · Accepted solution]

I would not write the search this way, and I would avoid map.

sourcetype=RULELIST OR sourcetype=FW-LOG 
| stats 
    count(eval(sourcetype!="RULELIST")) as count 
    first(ruledesc) as rulename 
  by rule

will run much faster and scale up to much large data set sizes. This is a little tricky. Using join is not as efficient as the above, but maybe more transparent:

sourcetype=RULELIST 
| join type=left max=0 rule 
    [ search sourcetype=FW-LOG 
      | stats count by rule ] 
| eval count=coalesce(count,0)

And of course you can sort by count or add | where count=0 to either, or change the final eval clause of the latter to | where isnull(count)

[bfaber]

This works great -- I had a field naming issue that I didn't notice. Thanks!

[gkanapathy]

I just tried loading the exact data you had above. The first one above and it works fine for me, returning a 0 for rule 1. The second one works if you just add | table rule,count,ruledesc to render it as a table with the selected columns. Not sure why the first one, in particular, wouldn't work for you.

[bfaber]

I appreciate the more efficient search, but it returns EXACTLY what my original does -- ie no zero values. The second search you provide does not seem to work at all, but I'm still looking at it.

Again, the end result needs to show EVERY left hand side value - even if the right hand size value is zero...