topic Re: Search query to combine data from 2 different index not working in Splunk Search

Search query to combine data from 2 different index not working

neerajs_81 — Mon, 12 Aug 2024 05:51:10 GMT

Hi All,
i need to consolidate / correlate data from 2 different indexes as explained below. I have gone thru multiple posts on this forum from experts relevant to this but somehow for my use case, the same query ain't working. I have below situation:
In Index=windows , the field "host" contains all the different hosts sending logs to Splunk. For example: Host01, Host02 etc.
In another index=cmdb, the field "dv_name" contain the same hostnames sending logs. Also, there are other fields like dv_status and dv_os in this index which i need to be part of final output

So as explained above, the common link is the host field, its name is different across the 2 index, but the values are same.

When i run the following 2 queries to get my expected output, it only pulls data from windows index. It completely avoids the other cmdb index, irrespective of the fact the cmdb index has data / events from same hosts in the time range whatever i select.

(index=windows) OR (index=cmdb sourcetype="snow:cmdb_ci_server" dv_name=*) | eval asset_name=coalesce(dv_name, host) | stats dc(index) as idx_count, values(index) values(dv_os), values(dv_install_status) by asset_name

Output it it showing:

asset_name	idx_count	index	dv_os	dv_status
Host01	1	windows
Host02	1	windows

Expected output

asset_name	idx_count	index	dv_os	dv_install_status
Host01	2	windows, cmdb	Windows Server	Production
Host02	2	windows, cmdb	Windows Server	Test

Re: Search query to combine data from 2 different index not working

KendallW — Mon, 12 Aug 2024 05:51:48 GMT

Hi @neerajs_81 try just renaming the dv_name field instead of creating a new field with coalesce, e.g.:

(index=cmdb sourcetye=server) OR (index=windows) | rename dv_name as host | stats dc(index) as idx_count, values(index) values(dv_os), values(dv_install_status) by host

Re: Search query to combine data from 2 different index not working

neerajs_81 — Mon, 12 Aug 2024 05:58:50 GMT

That didn't work. Query does not show any results if we rename the dv_name to host. That is because host is a default field and for index=cmdb, the host field originally contains the name of the Log source (ServiceNow) sending over the asset information to splunk. Renaming it overwrites the default field.
thanks for replying though.

Re: Search query to combine data from 2 different index not working

KendallW — Mon, 12 Aug 2024 06:15:10 GMT

It shouldn't matter what is contained in the host field in the 'cmdb' index as we are overwriting it. There is no problem with overwriting default fields in a search.
Regardless, I still can't see why your original query didn't work. - There may be some whitespace or other strange characters in some of the field values from one of the indexes causing them to not match with the other index. Are you able to check this?

Re: Search query to combine data from 2 different index not working

neerajs_81 — Mon, 12 Aug 2024 07:14:41 GMT

Hi @KendallW Does the coalesce or rename command treat the hostnames differently if they are different in cases? One is lower case in one index and other index has the same hostname in Upper case. Is the merge case sensitive ? For example, HOST01 which is one of the values in host field of index=windows, is actually host01 in index=cmdb ( under the dv_name) field.

That explains why the consolidation via coalesce or rename ain't working.

Re: Search query to combine data from 2 different index not working

ITWhisperer — Mon, 12 Aug 2024 07:12:49 GMT

Case does matter - as far as Splunk is concerned they are two different hosts - you could try converting to lower case

(index=windows) OR (index=cmdb sourcetype="snow:cmdb_ci_server" dv_name=*) | eval asset_name=lower(coalesce(dv_name, host)) | stats dc(index) as idx_count, values(index) values(dv_os), values(dv_install_status) by asset_name

Re: Search query to combine data from 2 different index not working

KendallW — Tue, 13 Aug 2024 04:21:18 GMT

As per @ITWhisperer 's comment, yes it is case sensitive. Use eval upper or lower to convert them all to the same case