topic Re: How do I use a look up to check to see if I'm getting logs from hosts that are in a CSV? in Splunk Search

How do I use a look up to check to see if I'm getting logs from hosts that are in a CSV?

wvalente — Thu, 13 Sep 2018 12:36:56 GMT

Dears,

I'm trying to use a lookup for Splunk to read a file and tell me if I'm collecting the logs to the host of that file.

What I need: Check if I'm getting logs from hosts that are in a CSV.

I am using the following query:

index = main OR index = client * | stats count by host | lookup client_sys hostname AS host

I also tried using the inputlookup command, but it did not work:

index = main OR index = client * NOT [| inputlookup client_sys.csv | fields host]

Is there any other way to do this?

Thanks a lot.

Re: How do I use a look up to check to see if I'm getting logs from hosts that are in a CSV?

mayurr98 — Thu, 13 Sep 2018 12:59:49 GMT

suppose hostname is column name in client_sys.csv.

can you try this:

| inputlookup client_sys.csv | rename hostname as host | table host |join type=outer host [|  tstats count where index=* by host ] | fillnull count value="NA" | search count=NA

This query should give you list of hosts which do not have any data

Re: How do I use a look up to check to see if I'm getting logs from hosts that are in a CSV?

landen99 — Thu, 13 Sep 2018 13:06:43 GMT

[edit] You need to use append inputlookup to see hosts not reporting in. Also I recommend tstats since you only use host:

| tstats count AS tstats_cnt where index = main OR index = client* by host | append [| inputlookup client_sys | rename hostname AS host ] | stats first(tstats_cnt) AS tstats_cnt by host | search NOT tstats_cnt=* | stats count AS host_dc

Other notes: client* had a space between which didn't make sense so I combined them. client_sys should be a lookup definition properly defined with a connection to an uploaded lookup file ending in .csv Double check the field name hostname in the lookup file.

Re: How do I use a look up to check to see if I'm getting logs from hosts that are in a CSV?

landen99 — Thu, 13 Sep 2018 13:08:05 GMT

better to avoid subsearches and replace inputlookup with lookup.

Re: How do I use a look up to check to see if I'm getting logs from hosts that are in a CSV?

wvalente — Thu, 13 Sep 2018 14:56:22 GMT

@landen99 I tried first the search |tstats count where index = main OR index = client* by host and return me 286 results.

When I put the whole command return the same results.

Do I have to write something after OUTPUT?

Thanks for your help.

Re: How do I use a look up to check to see if I'm getting logs from hosts that are in a CSV?

landen99 — Thu, 13 Sep 2018 15:25:17 GMT

286 results have the lookup columns added. Do you want to see the count of hosts in the lookup not reporting in from your search time window? If so, instead of lookup, use | append [| inputlookup client_sys] and then uses a stats to bring the two together to show which are not shown, a filter on those not present in the data, and then a stats to count the ones not filtered.

 | tstats count AS tstats_cnt where index = main OR index = client* by host | append [| inputlookup client_sys | rename hostname AS host ] | stats first(tstats_cnt) AS tstats_cnt by host | search NOT tstats_cnt=* | stats count AS host_dc