topic Re: Group table results by lookup table value in Splunk Search

Group table results by lookup table value

aohls — Fri, 31 May 2019 16:34:24 GMT

We have a few servers clustered together and have created a lookup table that combines them.
What I would like to do is use the lookup table in my search results to group the results by the combined name.

CombinedName    Host1 Host2

In the above example, I want the results in my search from Host1 and Host2 to get combined and show up as CombinedName. I was attempting the following:

| lookup client-info.csv hostname, combinedName OUTPUT hostname,combinedName
|fields + combinedName

I am not getting results back but I should be. Is there something I missed or a better way to do this?

Re: Group table results by lookup table value

DavidHourani — Fri, 31 May 2019 16:43:36 GMT

hi @aohls, could you please share the entire search you are trying to run ? Also could you please specify what the combinedName field should contain ? Is it the list of hosts ?

Re: Group table results by lookup table value

aohls — Fri, 31 May 2019 16:53:24 GMT

@DavidHourani here is my search. combinedName is just a name we use to represent a cluster of hosts. It is defined in the client-name.csv lookup.

sourcetype="userlogins" | lookup client-name.csv hostname, combinedName OUTPUT hostname,combinedName | fields + combinedName | stats avg(responsetime) as averageResponse, count(_raw) AS eventCount by combinedName , operation

Re: Group table results by lookup table value

DavidHourani — Fri, 31 May 2019 16:59:02 GMT

so in client-name.csv you have both hostname and combinedName and in your data you only have hostname, right ? If that's the case do the following :

sourcetype="userlogins" | lookup client-name.csv hostname OUTPUT combinedName | stats avg(responsetime) as averageResponse, count AS eventCount by combinedName , operation

Re: Group table results by lookup table value

richgalloway — Fri, 31 May 2019 17:05:04 GMT

Do you get results from sourcetype="userlogins" | lookup client-name.csv hostname, combinedName OUTPUT hostname,combinedName | table hostname combinedName responseTime operation? If not, the problem may be with your lookup file. Verify all four fields have values.

Re: Group table results by lookup table value

aohls — Fri, 31 May 2019 17:08:55 GMT

@DavidHourani It seems like your solution will work. @richgalloway identified the other issue; the lookup table is lower case but we have the hosts all capitalized. I believe this is causing a mismatch and not getting results back.

Re: Group table results by lookup table value

DavidHourani — Fri, 31 May 2019 17:32:08 GMT

Cool! let me know so I convert it to answer 🙂

Re: Group table results by lookup table value

aohls — Thu, 13 Jun 2019 12:28:01 GMT

@DavidHourani This worked great once I resolved the case match issue. You can convert it to answered.

Re: Group table results by lookup table value

DavidHourani — Thu, 13 Jun 2019 12:51:23 GMT

@aohls, glad I could help! it's converted to an answer, you can upvote and accept 🙂