Splunk Search

map not working with zero value data?

bfaber
Communicator

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

Tags (2)
0 Karma
1 Solution

gkanapathy
Splunk Employee
Splunk Employee

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)

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee

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.

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

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
Communicator

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

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

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.

0 Karma

bfaber
Communicator

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

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