Splunk Search

stats results using lookup and index fields, event count =0 or more, so I can report on something that does not exist

tlmayes
Contributor

I've seen many fine examples on how to present stats results even if a zero output, but for some reason I cannot get it to work in my environment. Not sure if there is a loop causing false output or not.

One of my users created a lookup table that has only two columns: Location, ApplianceName. He created an "Automatic Lookup" using both field names, so both show up when executing a query against the index=proxies. All entries in the lookup table field ApplianceName have a match in the index. Only some of the entries in the lookup table field Location have a match.

What they want, is an event count from the index, by Location, and want zero to display where there are no events. Cannot seem to wrap my head around how to report on something that does not exist (i.e. the Location will not appear in the index).

  'index=proxies Location=* | stats distinct_count(Location) = 16 Locations'
   '| inputlookup proxy_appliances | stats count BY Location = 23 Locations'

How do I generate a list of events, per location, listing all 23 locations?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try like this

index=proxies Location=* | stats count by Location
| append [| inputlookup proxy_appliances | eval count=0]
| stats max(count) as count by Location

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try like this

index=proxies Location=* | stats count by Location
| append [| inputlookup proxy_appliances | eval count=0]
| stats max(count) as count by Location

tlmayes
Contributor

Had seen similar queries in "answers" but was obviously getting something wrong. Your feedback worked exactly as expected. Thanks

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...