Splunk Search

How to search sourcetype not reporting by host?

Path Finder

Hi Splunkers,

I need your assistance to create a search that provides the following:
SPL query I will use it to look for sourcetypes that are not reporting, my focus here is on the nix sourcetypes.

I have a CSV lookup file called "os_sourcetypes.csv" that contains a list of 27 different sourcetypes.
I have another CSV lookup file called "onboarded_hosts.csv" that contains a list of onboarded hosts on Splunk with their IP addresses and a Bunit.

What I want is to have something like this:


My search is below:


| tstats max(_time) as lastTime where index=os by host sourcetype 
| join host 
    [| inputlookup onboarded_hosts.csv 
    | eval host=lower(host) 
    | search Bunit=production] 
| join sourcetype 
    [| inputlookup os_sourcetypes.csv ] 
| eval current_time=now() 
| eval timediff=round((current_time-lastTime)/60,2) 
| sort -timediff 
| convert ctime(current_time) , ctime(lastTime)
| table host IP sourcetype timediff lastTime


In the above search, I'm searching only for the "production" Bunit (which has 45 hosts)
What I'm expecting is: (27 sourcetypes * 45 hosts = 1215 statistics) 
But I'm having only 637!

What I have tested is trying to search for only one host from the "production" Bunit (host1 for example) just for testing purposes, and found that this host returns only 23 sourcetypes out of 27!
What I need is that all the hosts to show all of the 27 sourcetypes, even if one of the sourcetypes for one host is never seen before to show NULL.

One more thing: if I select "All time" from the time range picker, the search will take a VERY LONG time to finish, I would like the search to be efficient also.

Can someone please guide me in the right direction?

Thank you all. 

Labels (4)
Tags (2)
0 Karma

Esteemed Legend

This has been solved many times.  Start here:


Then take a look at this app:


Also take a look at Splunk's built-in feature for this:


It is actually a considerably deeper and more nuanced problem than it appears and will need considerable tuning (probably ongoing).

0 Karma


Finding something that is not there is not Splunk's strong suit.  See this blog entry for a good write-up on it.


If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

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