Search query to combine data from 2 different index not working

[neerajs_81]

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

[KendallW]

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

[neerajs_81]

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.

[KendallW]

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?

[neerajs_81]

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.

[KendallW]

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

[ITWhisperer · Accepted solution]

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