topic Re: compair list of hosts in Splunk Search

How to compare list of hosts?

vlape_SCWX — Tue, 25 May 2021 16:05:28 GMT

I have a large amount of hostnames and IP's (approx. 1850) I need to validate are sending logs to Splunk. I do not believe I have access to create a list within Splunk (basic user access). Besides querying one by one, is there a way to craft a query to check for hosts reporting and create a table of items not found? Much appreciated!

Re: compair list of hosts

gcusello — Thu, 16 Jan 2020 13:02:06 GMT

HI @vlape_SCWX,
I think that all these servers has an Universal Forwarder and send logs to Splunk.
So you should create a lookup containing all the hosts to monitor (called e.g. perimeter.csv) containing at least a column (called e.g. hostname) and then run a search like this:

| metasearch index=_internal
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup perimeter.csv | eval host=lower(hostname), count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total=0

that you can schedule (e.g. every five minutes) in an alert.
In this way you have all the hosts that didn't send logs in tha last period.

If you like, you can also display this list in a dashboard, without the last row and adding

| metasearch index=_internal
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup perimeter.csv | eval host=lower(hostname), count=0 | fields host count ]
| stats sum(count) AS total BY host
| eval Status=if(total=0,"Missing","OK")
| table host Status

In this way: hosts with total=0 are missing and hosts with total>0 are ok; this dashboard is also displayable in graphic format (with a red or green circle).

Ciao.
Giuseppe

Re: compair list of hosts

acfecondo75 — Thu, 16 Jan 2020 13:54:39 GMT

hi @vlape_SCWX,

even with basic user access, you do have permission to create a list (lookup in splunk terms). This comes with the input_file capability that is given to every out-of-the-box role in Splunk. The easiest way to create the lookup would be via the lookup file editor app (https://splunkbase.splunk.com/app/1724/), if it's installed. If it is not, I'd recommend requesting that it be installed!

If the lookup editor app is not an option, you can upload the file (in csv format) via Settings->Lookups->Add New (Next to lookup files). This doc outlines the process more thoroughly: https://docs.splunk.com/Documentation/Splunk/8.0.1/Knowledge/Usefieldlookupstoaddinformationtoyourevents

Once you have the lookup, you output the hosts from the lookup using the inputlookup command. Then perform a left join which will run a subsearch and add the fields from the subsearch to your results by matching the field specified (in this case, host). Then look for instances where the count field is null:

    | inputlookup hostlookup.csv
    | eval host=lower(host)
    | join type=left host
    | [tstats count by host | eval host=lower(host)]
    | where isnull(count)

*the field that you join on IS case sensitive so it's a good idea to use evals to force the field values to be in the same format.

Re: compair list of hosts

mydog8it — Thu, 16 Jan 2020 15:24:39 GMT

If you still find that you are struggling with this objective, you can run a search, download the reulting "spreadsheet" and perform your comparison in excel or whatever similar tool you have available. Here is a sample search that should get you the hosts sending data to splunk:

|tstats count where index=* by host

Re: compair list of hosts

vlape_SCWX — Tue, 21 Jan 2020 20:54:45 GMT

@acfecondo75
I created the .csv file and uploaded. when running the search you provided I get an error "Error in 'from' command: invalid dataset specifier 'host', expected dataset-type:dataset-name

any ideas?

Re: compair list of hosts

acfecondo75 — Thu, 23 Jan 2020 19:24:07 GMT

I've never seen that error so I don't know for sure. I would need to see the contents of the lookup and the exact search you ran to figure out what caused it.

Re: compair list of hosts

vlape_SCWX — Fri, 24 Jan 2020 19:35:29 GMT

@acfecondo75
The lookup is a csv file named C_Hosts.csv. the forst 4 rows look like:

host
server1
server2
server3
The search is:

| inputlookup C_Hosts.csv
| eval host=lower(host)
| join type=left host
| [tstats count by host | eval host=lower(host)]
| where isnull(count)
Does that help?