- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![SplunkTrust SplunkTrust](/html/@E48BE65924041B382F8C3220FF058B38/rank_icons/splunk-trust-16.png)
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As per @ITWhisperer 's comment, yes it is case sensitive. Use eval upper or lower to convert them all to the same case
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![SplunkTrust SplunkTrust](/html/@E48BE65924041B382F8C3220FF058B38/rank_icons/splunk-trust-16.png)
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
![](/skins/images/FE4825B2128CA5F641629E007E333890/responsive_peak/images/icon_anonymous_message.png)