Skip to content

Instantly share code, notes, and snippets.

@sanelson
Created April 11, 2014 21:37
Show Gist options
  • Save sanelson/10503549 to your computer and use it in GitHub Desktop.
Save sanelson/10503549 to your computer and use it in GitHub Desktop.
-- List all hosts, including custom attributes
--
-- LONG_DESC:
-- This query will return a list of FQDN names for all hosts in the database.
-- It includes the device type, IP, MAC, Subnet name, location, plus custom attributes
--
-- USAGE:
-- No options available for this query, just run it
--
-- Your SQL statement would go below this line:
select concat(dns.name,".",z.name) fqdn,
concat(m.name," ",models.name," (",roles.name,")") device_type,
INET_NTOA(i.ip_addr) ip,
i.mac_addr mac,
s.name netname,
l.reference location,
GROUP_CONCAT(CONCAT_WS('=', cat.name, ca.value)) as attributes
from interfaces i,
dns,
domains z,
subnets s,
manufacturers m,
models,
roles,
device_types,
devices
left join locations l on (devices.location_id = l.id),
hosts
left outer join custom_attributes ca on (hosts.id = ca.table_id_ref and ca.table_name_ref = 'hosts')
left join custom_attribute_types cat on (ca.custom_attribute_type_id = cat.id)
where i.host_id = hosts.id
and dns.domain_id = z.id
and hosts.primary_dns_id = dns.id
and s.id = i.subnet_id
and devices.id = hosts.device_id
and models.manufacturer_id = m.id
and device_types.model_id = models.id
and device_types.role_id = roles.id
and devices.device_type_id = device_types.id
group by fqdn, device_type, ip, mac, netname, location
order by i.ip_addr
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment