combine two search results tables by matching fields value

[kannu]

Hello Guys ,

I am having results from two different query

1> index=_internal ("version" AND source="/opt/splunk/var/log/splunk/metrics.log") | table hostname , version

output
hostname version
abc.com 6.6.2

2> index=* source=computer |stats count by host,Manufacturer

host Manufacturer
abc.com HP

Is there any way in which i can combine the results of two into one by correlating hostname and host field .

expected output

host version Manufacturer
abc.com 6.6.2 HP

I dont want to use join command to get the expected output

please help

Thanks
Manish

[knielsen]

Try

(index=_internal "version" source="/opt/splunk/var/log/splunk/metrics.log") OR (index=* source=computer) | eval host=coalesce(host, hostname) | stats values(version) as version values(Manufacturer) as Manufacturer by host

Regards,
-Kai.

[kannu]

@knielsen . Your query is not returning the result in manner which i want .

SsdfWsdfC4 VMware, Inc.
SWsdfBeF5 VMware, Inc.
ansdfging5 5.0.3

asd1dfsing6 5.0.3

ansdfsdfg2

6.2.1

[vik_splunk]

Considering you do not want to use join, see if the following approach works for you.

|multisearch [search index=_internal ("version" AND source="/opt/splunk/var/log/splunk/metrics.log")][search index=* source=computer]|stats latest(version) AS Version latest(Manufacturer) AS Manufacturer by host

Is there a reason you do not want to use join?

[kannu]

@vik_splunk

Actually join command is very heavy , in production i have more than 4000 hosts so comparing each host value with hostname entry was taking almost 10 minutes for completing the result

[vik_splunk]

@kannu - For the multisearch query I see what the issue is , try the below.

|multisearch [search index=_internal ("version" AND source="/opt/splunk/var/log/splunk/metrics.log")][search index=* source=computer]|rename hostname as host|stats latest(version) AS Version latest(Manufacturer) AS Manufacturer by host

An alternate option is to create a lookup that's updated once a day for manufacturer and then use it to populate your other search.

You can use index=* source=computer |stats count by host,Manufacturer to populate your lookup and then use the same as a lookup for the internal data search

[kannu]

@vik_splunk

Its still not working Manufacturer column is not getting populated

host Version Manufacturer
AN2SQLTEST01 6.6.2

ANMAADMINTS01 6.4.3

MINESM003 6.6.2

anddevcaspec02 6.6.2

anddevcaspec03 6.6.2

anddevcaspec05 6.6.2

anddevcaspec07 6.6.2

andndevsplunk-mn-01 6.6.7

andqacaspec02 6.6.2

anma1camcore001 7.2.0

[vik_splunk]

@kannu. Can you please provide limited sample data(masked) for both your internal and your custom sourcetype to proceed?

Make sure when you mask the data to match the hostname field in _internal to the host for which you are trying to get the manufacturer.

[kannu]

@vik_splunk

That is my question actually as you are saying that match the hostname with host for which i am looking manufacturer .

I said that matching can be done i am getting the result using join command only , is there any way except join in which i can map the hostname from _internal to host in .

[vik_splunk]

@kannu - I believe it is possible without having to use join. Just need some sample data so I can build and test the query.

[kannu]

Hello @vik_splunk

I tried running your search but output is not coming as expected

host Version Manufacturer
ANMqweMIewwSe01 VMware, Inc.
MINEqweq HP
andndasdasnk-idx-01 6.6.7

andndasdaunk-idx-02 6.6.7

andnddsadunk-idx-03 6.6.2

anasddlunk-mn-01 6.6.2