topic Re: Looking to add a field from another searched index to a finished table in Splunk Search

Looking to add a field from another searched index to a finished table

psomeshwar — Tue, 19 Mar 2024 19:07:27 GMT

Currently, I have two tables

Table1

hostnames vendors products versions
host1 vendor1 product1 version1
host2 vendor2 product2 version2
host3 vendor3 product3 version3
host4 vendor4 product4 version4

Table2

device.hostname device.username
HOST1 user1
HOST2 user2
HOST3 user3
HOST4 user4

The table that I want to generate from these two is the following:

Table3

hosts username vendors products versions
host1 user1   vendor1 product1 version1
host2 user2   vendor2 product3 version4
host3 user3   vendor3 product3 version3
host4 user4   vendor4 product4 version4

The search I tried was the following:

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) | rename device.hostname as hostname | rename device.username as username | eval hosts = coalesce(hostnames, hostname) | table hosts, username, vendors, products, versions

The result was the following:

hosts username vendors products versions
host1 vendor1 product1 version1
host2 vendor2 product3 version4
host3 vendor3 product3 version3
host4 vendor4 product4 version4
HOST1 user1
HOST2 user2
HOST3 user3
HOST4 user4

host1 and HOST1 both reference the same hostname, just one index had the letters capitalized and the other did not.

Does anyone have any ideas?

Re: Looking to add a field from another searched index to a finished table

marnall — Tue, 19 Mar 2024 19:20:06 GMT

Try this:

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) | rename device.hostname as hostname | rename device.username as username | eval hosts = lower(hosts) | stats values(*) as * by hosts | table hosts, username, vendors, products, versions

Re: Looking to add a field from another searched index to a finished table

psomeshwar — Tue, 19 Mar 2024 19:27:17 GMT

So, I tried your solution and the result was:

hosts username vendors products versions
host1 user1
host2 user2
host3 user3
host4 user4

Also, I'm assuming you meant for the search to look like this:

Otherwise, the search wouldn't yield any results

Re: Looking to add a field from another searched index to a finished table

marnall — Tue, 19 Mar 2024 19:56:08 GMT

Ack, seems I forgot to rename the hostname field to hosts, thus ruining the stats.

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) | rename device.hostname as hosts | rename device.username as username | eval hosts = lower(hosts) | stats values(*) as * by hosts | table hosts, username, vendors, products, versions

The trick is to get the hosts values (e.g. HOST1 and host1) to be in the same case (hence the lower()), then if you do "stats values(*) as * by host" , then it will put together all the values for the other columns on one row for each unique value of hosts. One for host1, one for host2, and so on.

Re: Looking to add a field from another searched index to a finished table

psomeshwar — Tue, 19 Mar 2024 20:02:05 GMT

When trying this, the result was the same as the previous attempt, only the hosts and username fields populating

Re: Looking to add a field from another searched index to a finished table

marnall — Tue, 19 Mar 2024 20:10:43 GMT

This could be caused because the host values are not becoming equal. Could you try your initial search but with the "| eval hosts = lower(hosts)" command at the end?