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.
