Splunk Enterprise

How to see also events with no findings based on lookup

fred1455
New Member

Hello,

as a Lookup I definded a List of locations and servers

location, servername
Paris, Server1
Paris, Server2
Madrid, Server3
Madrid, Server4
Milano, Server 5

in my sourcetype=serverevents I do have events including the field servername.

How can I make a query based on inputlookup to count and see also locations with no events.

So like

Paris=4 Events
Madrid=0 Events
Milano= 3 Events

Can you helo me please?

Thanks

MIke

Tags (1)
0 Karma
1 Solution

knielsen
Contributor

Try this for example:

sourcetype=serverevents [inputlookup your_lookup | fields servername] | stats count by servername | appendcols [| inputlookup your_lookup] | fillnull count | fields location count

View solution in original post

0 Karma

knielsen
Contributor

Try this for example:

sourcetype=serverevents [inputlookup your_lookup | fields servername] | stats count by servername | appendcols [| inputlookup your_lookup] | fillnull count | fields location count
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@knielsen - appendcols won't work for that - it will add columns to the existing sums, not add events where there wasn't a match. See if you can figure out another way. (hint - append, coalesce, stats)

0 Karma

knielsen
Contributor

Well, I honestly tried that out with one of our own lookup files, where I have ~100 lines in the lookup. I got a count > 0 for like 20 of those on a limited data set without the appendcols, and adding the appendcols added the unused entries with count=0 as well.

Don't know why it doesn't work for you, Sorry. 🙂

edit: actually, I shut up on this one. I didn't check my ids reappearing, and that even makes sense. So yeah, I am wrong. 🙂

0 Karma

knielsen
Contributor

And then again, it works as expected when I do it the other way round, start with the inputlookup for the whole table, then add the count for things that are found, then fill count with 0. I got no more duplicated ids then, as the count is correct for the ids found, 0 for non found. No appendcols, just append...

 | inputlookup your_lookup | append [ search sourcetype=serverevents [| inputlookup your_lookup | fields servername ] |stats count by servername] | stats first(*) as * by servername | fillnull count

I double checked the result of that now within my data sets, this should be fine.

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