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

[kejamder1]

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?

[richgalloway · Accepted solution]

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

[kejamder1]

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