Splunk Search

Can I force all rows of a joined lookup table to be returned?

kejamder1
New Member

I log events from 30 devices every minute, and I'd like to be able to return a simple table of the count of events by deviceID in a given period. However, if I use something like:

index="myApplication" "myAppMessage"
| chart count(deviceEvent) by deviceID

I lose any deviceID results that have 0 events. That is, my statistics table might only show 28 rows if two devices were offline for the entire period.

I tried to add a lookup .csv with the 30 deviceIDs listed, but if I try joining the search to the .csv:

| inputlookup append=true myLookup.csv
| join type=left deviceID [search index="myApplication" "myAppMessage"
  | lookup myLookup.csv deviceID OUTPUT sortNumber]
| chart count(deviceEvent) by deviceID

I get all 30 rows, but the count of deviceEvents has been lost. Actually, it returns a 1 for devices that have events (instead of 30 or 60 depending on the time picker) and 0 for devices that have no events.

I first tried to join the .csv to the main search:

index="myApplication" "myAppMessage"
| lookup myLookup.csv deviceID OUTPUT sortNumber
| chart count(deviceEvent) by deviceID

But this doesn't retrieve the missing devices, despite them being present in the .csv. Is there a way to force all rows of the lookup table to be returned to statistics table?

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

I think you were close with your second query. Try this.

| inputlookup myLookup.csv
| append [ search index="myApplication" "myAppMessage" ]
| stats count(deviceEvent) as eventCount by deviceID
| fillnull value=0 eventCount
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I think you were close with your second query. Try this.

| inputlookup myLookup.csv
| append [ search index="myApplication" "myAppMessage" ]
| stats count(deviceEvent) as eventCount by deviceID
| fillnull value=0 eventCount
---
If this reply helps you, Karma would be appreciated.
0 Karma

kejamder1
New Member

Thanks! That helped me get past this. The 30 devices are provided by two vendors that each provide a field for health monitoring, so I actually joined two other searches once I got this part cleared up, and the lookup is what I needed to bridge the different IDs and SNs. Here's the final:

| inputlookup myLookup.csv 
| append [search index="myApplication" “myAppMessage1"]
| lookup myLookup.csv deviceId OUTPUT deviceName, sortNumber, deviceSN
| join type=left deviceName [search index="myApplication" “myAppMessage2"
| eval deviceHealth=round(deviceHealth,1) 
| lookup myLookup.csv deviceSN OUTPUT deviceName, sortNumber, deviceId]
| join type=left deviceName [search index="myApplication" “myAppMessage3"
| eval deviceHealth =round(deviceHealth,1) 
| lookup myLookup.csv deviceSN as serialnumber OUTPUT deviceName, sortNumber, deviceId]
| stats last(deviceName) as deviceName, last(deviceId) as deviceId, count(deviceEvents) as eventCount, min(deviceHealth) as deviceHealth by sortNumber
| sort + sortNumber
| fields - sortNumber
0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...