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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...