SQL for report DNS names

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:


	,da.host_name AS host_name
	,STRING_AGG(DISTINCT aliases.host_name, ', ') AS alias_host_names

	dim_asset_host_name dahn
	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 */
	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 */

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. :slight_smile:

Really, how do they separate the hostnames from the same ip?