Splunk Search

How to include results where count is 0 in a an existing search using a csv file?

Rapidz
Explorer

Currently I have a search query that will show when an event happens with the device_id, count, and the device name. The search is set up to count when an event happens, but I also want to know when the event doesn't happen, so it counts devices with 0 count.

Here is my search:

sourcetype="transactions" AND (additionalMessage.requestUrl="*/cashIn/initialize" OR additionalMessage.requestUrl="*/cashIn/update" OR additionalMessage.requestUrl="*/cashIn/updateStatus" OR additionalMessage.requestUrl="*/cashIn/finalize") AND message != "Token time nonce*" message="POST - http://transactions/cashIn/finalize  - RESPONSE_SENT"

|rename additionalMessage.requestBody.deviceId as device_id
|stats count(message) by device_id |sort -count(message)
|lookup DeviceNamesAll.csv device_id OUTPUT device_name

Search will show this:

device_idcount(message)device_name
0297f12-e0ac-40d6-8ff5-2d2c2787b45Store12
37ca5c1-2c3f-41d-88d4-57f8b354c441Store54

 

I cant figure out how to also count the device_id's that have a count of 0. If anyone could help it would be greatly appreciated!

Labels (2)
Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

Try like this 

sourcetype="transactions" AND (additionalMessage.requestUrl="*/cashIn/initialize" OR additionalMessage.requestUrl="*/cashIn/update" OR additionalMessage.requestUrl="*/cashIn/updateStatus" OR additionalMessage.requestUrl="*/cashIn/finalize") AND message != "Token time nonce*" message="POST - http://transactions/cashIn/finalize  - RESPONSE_SENT"

|rename additionalMessage.requestBody.deviceId as device_id
|stats count(message) as count by device_id 
|lookup DeviceNamesAll.csv device_id OUTPUT device_name
| append [| inputlookup DeviceNamesAll.csv | table device_id device_name | eval count=0 ]
| stats max(count) as count by device_id device_name 
|sort -count | rename count as "count(message)"

 

View solution in original post

0 Karma

somesoni2
Revered Legend

Try like this 

sourcetype="transactions" AND (additionalMessage.requestUrl="*/cashIn/initialize" OR additionalMessage.requestUrl="*/cashIn/update" OR additionalMessage.requestUrl="*/cashIn/updateStatus" OR additionalMessage.requestUrl="*/cashIn/finalize") AND message != "Token time nonce*" message="POST - http://transactions/cashIn/finalize  - RESPONSE_SENT"

|rename additionalMessage.requestBody.deviceId as device_id
|stats count(message) as count by device_id 
|lookup DeviceNamesAll.csv device_id OUTPUT device_name
| append [| inputlookup DeviceNamesAll.csv | table device_id device_name | eval count=0 ]
| stats max(count) as count by device_id device_name 
|sort -count | rename count as "count(message)"

 

0 Karma
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...