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.
... View more
