topic Re: combine two search results tables by matching fields value in Splunk Search

combine two search results tables by matching fields value

kannu — Fri, 22 Mar 2019 13:12:48 GMT

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

Re: combine two search results tables by matching fields value

vik_splunk — Fri, 22 Mar 2019 13:47:51 GMT

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?

Re: combine two search results tables by matching fields value

kannu — Fri, 22 Mar 2019 14:11:53 GMT

@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

Re: combine two search results tables by matching fields value

kannu — Fri, 22 Mar 2019 14:14:43 GMT

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

Re: combine two search results tables by matching fields value

vik_splunk — Fri, 22 Mar 2019 14:37:25 GMT

@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

Re: combine two search results tables by matching fields value

kannu — Fri, 22 Mar 2019 15:41:08 GMT

@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

Re: combine two search results tables by matching fields value

vik_splunk — Tue, 26 Mar 2019 08:07:39 GMT

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

Re: combine two search results tables by matching fields value

kannu — Wed, 27 Mar 2019 10:37:33 GMT

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

Re: combine two search results tables by matching fields value

vik_splunk — Thu, 28 Mar 2019 09:42:57 GMT

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

Re: combine two search results tables by matching fields value

knielsen — Thu, 28 Mar 2019 12:15:45 GMT

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.

Re: combine two search results tables by matching fields value

kannu — Mon, 15 Apr 2019 09:43:16 GMT

@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