Splunk Search

How to search sourcetype not reporting by host?

muradgh
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:

muradgh_0-1681732875540.png

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

woodcock
Esteemed Legend

This has been solved many times.  Start here:

https://www.duanewaddle.com/proving-a-negative/

Then take a look at this app:

https://splunkbase.splunk.com/app/4621

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

https://docs.splunk.com/Documentation/Splunk/9.0.4/DMC/Configureforwardermonitoring

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

https://www.duanewaddle.com/proving-a-negative/

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

.conf25 Community Recap

Hello Splunkers, And just like that, .conf25 is in the books! What an incredible few days — full of learning, ...

Splunk App Developers | .conf25 Recap & What’s Next

If you stopped by the Builder Bar at .conf25 this year, thank you! The retro tech beer garden vibes were ...

Congratulations to the 2025-2026 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...