Splunk Search

Group table results by lookup table value

Communicator

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?

0 Karma

SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Communicator

@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.

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Communicator

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

SplunkTrust
SplunkTrust

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 ?

0 Karma

Communicator

@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

0 Karma

SplunkTrust
SplunkTrust

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
0 Karma