Sourcetype Missing for Tag Member

[CyberSamurai]

Hello Splunk Community. I'd like to use a query to find a host which is a member of a tag group and has 0 events for a specific sourcetype. Here's the search that gets me most of the way there:

index=sw tag=MemberServers sourcetype="windows PFirewall Log" | stats count by sourcetype,host

But I'd like to return only hosts which have 0 events (aka. are missing firewall data). How can I do this?

[gcusello · Accepted solution]

Hi @CyberSamurai ,

you have two solutions:

you can create a lookup (called e.g. perimeter.csv and containing two columns: host and sourcetype) listing all the sourcetypes and hosts to monitor (beware: in the lookup you have to list all the copuple of sourcetype and host to monitor), and the run a search like this:

index=sw tag=MemberServers sourcetype="windows PFirewall Log" 
| stats count BY sourcetype host
| append [ | inputlookup perimeter.csv | eval count=0 | fields sourcetype host count]
| stats sum(count) AS total BY sourcetype host
| where total=0

otherwise, if you don't want to manage a lookup, you could check the couples of sourcetype and host that were present e.g. in the last 30 days and aren't present in tha last hour, running a search like this:

index=sw tag=MemberServers sourcetype="windows PFirewall Log" 
| stats latest(_time) AS _time count BY sourcetype host
| where _time<now()-3600

obviously to customize on your situation.

Ciao.

Giuseppe

[CyberSamurai]

It looks like I closed this thread early because I've hit an issue. I decided to go with the lookup table afterall. The query I marked as correct, does work, but it creates duplicate hosts which throws off the results. At first I thought it was just because the host name in Splunk had different casing than the MemberServers.csv file. I tweaked the query to lower the host names (all the names in MemberServers.csv are lower case) and removed the "| where total=0" line. That showed me there are 2 hosts for every host returned by the Splunk query. There is one from the original query and the one appended by the .csv file. For some reason the stats sum(count) command doesn't see them as identical hosts but two different ones even though their names are exactly the same (including case). This is the query which tells me there are now duplicates, one with count 0 (presumably added by the append command) and one a count greater than 0 (presumably added by the Splunk query).

index=sw tag=MemberServers sourcetype="windows PFirewall Log"
| eval host=lower(host)
| stats count BY sourcetype host
| append [ | inputlookup MemberServers.csv | eval count=0 | fields sourcetype host count]
| stats sum(count) AS total BY sourcetype host

I tried replacing append with a join command but I ran into problems with that too. Any help would be appreciated.

[gcusello]

Hi @CyberSamurai ,

try to avoid to use join: Splunk isn't a relational database and join is a very slow command!

my solution is more efficient.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[CyberSamurai]

@gcusello 

It turns out there was different casing between the sourctype name in the lookup file and in Splunk. That was causing the duplicates in the search you gave me. The final version of the query that worked exactly as desired was this (once I made the sourctype match exactly in the lookup file and in Splunk).

index=sw tag=MemberServers sourcetype="windows PFirewall Log"
| stats count BY sourcetype host | eval host=lower(host)
| append [ | inputlookup MemberServers.csv | eval count=0 | fields sourcetype host count]
| stats sum(count) AS total BY sourcetype host
| where total=0

Thank you for your help!

[gcusello]

Hi @CyberSamurai ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[CyberSamurai]

Each of these answers are excellent. Thank you all for your input. For now, I'm going to go with the "was working within the last 30 days" search as that's what I need.

Thanks!

[PrewinThomas]

@CyberSamurai 

Try with lookup

Eg:

| inputlookup memberservers.csv | rename host as lookup_host | join type=left lookup_host [ | tstats count as totalEvents count(eval(sourcetype=="windows PFirewall Log")) as fwCount WHERE index=sw tag=MemberServers BY host | rename host as lookup_host ] | fillnull value=0 totalEvents fwCount | where fwCount=0 | table lookup_host totalEvents fwCount | rename lookup_host as host

Also try with tstats
| tstats count as totalEvents count(eval(sourcetype=="windows PFirewall Log")) as fwCount
WHERE index=sw tag=MemberServers
BY host
| where fwCount=0
| table host,totalEvents,fwCount


Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[yuanliu]

Suppose you have a lookup called myhosts.csv; it has a field called host.  You use this as primary input, then find which host has zero count compared with index search.

| inputlookup myhosts.csv
| append
    [search index=sw tag=MemberServers sourcetype="windows PFirewall Log" | stats count by sourcetype,host]
| stats values(sourcetype) as not_missing by host
| where isnull(not_missing)

[PickleRick]

It's better to do it the other way around without using append and having a subsearch which has its limitations.

index=sw tag=MemberServers sourcetype="windows PFirewall Log"
| stats count by sourcetype,host
| inputlookup append=t myhosts.csv
| stats values(sourcetype) as not_missing by host
| where isnull(not_missing)

[yuanliu]

---

---

| inputlookup append=t myhosts.csv

---

Good suggestion.  I always forget the append option🙂

[CyberSamurai]

Thank you for the reply. I've used lookup tables a little before and can probably figure out that piece of it. Once I have that comparison list working, how would I say where events for that sourcetype is zero? I've tried something like this without success:

... | stats count by sourcetype,host | where sourcetype="windows PFirewall Log" | where "count">="1"

[ITWhisperer]

Splunk is not good at reporting on things that don't exist. To get around this, you need to provide a list (of the hosts you are interested in) and compare that to the number of event you have for each host, and then just keep those where the number of events is less than 1. This is often done using a lookup file, for example (if the hosts are "new"), or some historic data (if the hosts are "old").