topic Re: How to find count of events by host from inputlookup? in Splunk Search

How to find count of events by host from inputlookup?

whar_garbl — Mon, 18 Jul 2022 20:23:28 GMT

I have a lookup table with only one field, named host. The table contains a list of hostnames.

I'm trying to find a way to get a count of events by host using this lookup table as the input (i.e. the hosts I want a count for).

I've tried a variety of approaches. For example:

|inputlookup file.csv | stats count by host

Every host returns a count of 1.

|inputlookup file.csv | join type=left host [|tstats count by host]

About a dozen hosts return counts; the rest return null values.

Complicating this problem seems to be case. If I crunch all the hosts to upper or lowercase, I get different results, but neither returns a complete result set. That seems super odd given that field values aren't case sensitive. I've tried crunching case with eval as well as in the lookup table itself, to no avail.

We're stumped. What is the best approach to use a lookup table of hostnames to get an event count by host?

Re: How to find count of events by host from inputlookup?

richgalloway — Mon, 18 Jul 2022 21:09:23 GMT

The first query

|inputlookup file.csv | stats count by host

is counting how many times each host name appears in the lookup file. That's why the results are only '1'.

The second query look for all hosts in the default indexes and joins those results with the lookup file. Hosts not in an index will have a null count, but that can be fixed with the fillnull command. To help ensure more hosts are found, specify index=* in the tstats command.

|inputlookup file.csv | join type=left host [|tstats count where index=* by host] | fillnull value=0 host

I've seen host names fail to match if the lookup contains a host name, but the events contain a FQDN or vice versa.

Re: How to find count of events by host from inputlookup?

whar_garbl — Mon, 18 Jul 2022 21:56:27 GMT

Thanks. I did start out using index=* as well, but I forgot about it. I still get null values for way too many hosts, but I can search for them directly using the same syntax (i.e. index=* and short hostname as found in the inputlookup) and get many thousands of events. It's making me nuts because it seems like it should be super straightforward!

Re: How to find count of events by host from inputlookup?

bowesmana — Mon, 18 Jul 2022 23:27:59 GMT

You could do this, just using the inputlookup as a subsearch

| tstats count where [ | inputlookup file.csv ] by host

Re: How to find count of events by host from inputlookup?

whar_garbl — Tue, 19 Jul 2022 19:30:27 GMT

This was close to what I want to do:

| tstats count where [ | inputlookup file.csv ],index=* by host

But it doesn't include hosts from the lookup that have no events, which I want to include. Adding a fillnull afterward doesn't do it.

Re: How to find count of events by host from inputlookup?

bowesmana — Tue, 19 Jul 2022 22:02:06 GMT

In order to show the hosts where there is no data, your search should look like

so, you are first finding the data for the hosts that exist (tstats) and then adding all the hosts with count=0 to the end of the list, then finally aggregating the max count for all hosts, so that those hosts who have no data will then have 0 as their count.

Re: How to find count of events by host from inputlookup?

whar_garbl — Tue, 19 Jul 2022 22:33:28 GMT

Thanks. I've tried a very similar permutation today, but this brings back the problem I had at another point: I get about a third more results than I should, and they're duplicates (one uppercase, one lowercase).

If I cast the host field to upper or lowercase before the stats max command, I get the correct number of hosts but around 10% then erroneously return 0 events. E.g.

Expected result:

host count HOST1 12345 HOST2 67890 HOST3 24680

Actual result:

host count HOST1 0 HOST2 67890 HOST3 24680

But if I search for HOST1 or host1 manually, there are many thousands of events in the same time period. Since field values are supposed to be case-insensitive (IIRC), I'm stumped as to why case is relevant here. It's trivial to do the case-casting in the actual lookup file if appropriate, but I think there's one more piece missing somewhere.

Re: How to find count of events by host from inputlookup?

bowesmana — Tue, 19 Jul 2022 22:43:38 GMT

You should not do dedup - that's wrong. The whole point of stats is to do the aggregation of hosts, so you NEED both hosts (data value and 0 value from lookup) to exist, so the max(count) can work.

Re: How to find count of events by host from inputlookup?

whar_garbl — Tue, 19 Jul 2022 23:01:18 GMT

Ah, I see what you mean. Righto. So, then I end up getting the same results as explained, with ~10% of hosts showing up with different-case duplicates - one with appropriate event counts, and one with zero. Any thoughts on what's going on there?

Re: How to find count of events by host from inputlookup?

bowesmana — Wed, 20 Jul 2022 05:22:13 GMT

No idea without seeing the data and not sure what you mean by different case duplicates - narrow it down to a single host and show what the data is and what should be expected.

Re: How to find count of events by host from inputlookup?

whar_garbl — Wed, 20 Jul 2022 15:18:29 GMT

Expected:

host count HOST1 11111 HOST2 22222 HOST3 33333

Actual:

host count HOST1 11111 HOST2 22222 HOST3 0 host1 0 host2 0 host3 33333

Does that make sense? I get HOSTx and hostx (the lookups are all uppercased), and on one or the other there are zero events counted - but they're the same host.

Re: How to find count of events by host from inputlookup?

bowesmana — Wed, 20 Jul 2022 23:45:39 GMT

That's odd if you are doing

| eval host=upper(host)

before the stats command as there cannot be any lower case hosts in there any more

Re: How to find count of events by host from inputlookup?

whar_garbl — Thu, 21 Jul 2022 14:45:01 GMT

Huh. It must be an issue of WHEN I crunch the case, because this does the trick and gets the results I expect. The only zero hosts now are true zeros. Success!

You're the best! Thanks so much for sticking with this and holding my hand through it. I'm going to accept the post with the main query as the answer for the next person. Cheers!