Hi there,
Is there SQL report that I can pull for ips that have multiple aliases? Like it would list the ip from asset group or tag and then all the names associated with that name or as InsightVM has it: alias’s? Instead of manually going to the application and the asset page to look for numerous names under the alias category.
Hi Vanessa,
I’m sure I can help on the SQL, but I’m not sure I understand what is meant by “alias”. Maybe you can provide an example to help illustrate?
ah thank you so much, In the asset page it list an ip, under that ip is usually hostname, then it also gives alias’s which means hey i have other names associated with this asset too. Let me see if i can find example.
It pulls one name at the left hand corner then others associated by the aliases category name…basically just need an inventory report maybe site name ip address hostname1, 2 or 3 or even 4… with mac address if available
Appreciate the extra details!
Give this a try:
SELECT
da.ip_address
,da.mac_address
,dahn.asset_id
,da.host_name AS host_name
,STRING_AGG(DISTINCT aliases.host_name, ', ') AS alias_host_names
,da.sites
FROM
dim_asset_host_name dahn
JOIN
dim_asset da
ON dahn.asset_id = da.asset_id
AND dahn.host_name = da.host_name /* Used to get the "main" host_name being used AND ip_address */
LEFT JOIN
dim_asset_host_name aliases
ON da.asset_id = aliases.asset_id
AND da.host_name <> aliases.host_name /* Used to get the "Aliases" of a host */
GROUP BY
da.ip_address
,da.mac_address
,dahn.asset_id
,da.host_name
,da.sites
Hope this helps!
EDIT: Updated the script to include mac_address and sites
1 Like
@bobsledtedd This is amazing thanks so much!! This is the small solution that comes from having two private spaces with the same ips just different assets(completely different assets)
You bet! I’ve seen similar environments, but never a request for the particular data. 
Really, how do they separate the hostnames from the same ip?