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

[psomeshwar]

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?

[marnall]

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

[psomeshwar]

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:

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

Otherwise, the search wouldn't yield any results

[marnall]

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.

[psomeshwar]

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

[marnall]

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?