Database Host Visible Table name

[vlam]

Hi

Can anyone help me with what the table name is for Host Visible Name:

For a report that we need to do my query looks as follow:

select
g.name as 'Client',
SUBSTRING_INDEX(h.host,'.domain.com',1) as 'Server name'
from
hosts h,
hosts_groups hg,
groups g
where
g.groupid=hg.groupid
and
h.status = 0
and
hg.hostid=h.hostid
order by g.name

My problem is my ESX LLD places UUID in hostname and for the report for that I then need to use the Visible name.

Any help would be greatly appreciated

Thanks

[sca_ebe]

Hello,

When you type "discribe hosts;" in mysql, output is like :

+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| hostid             | bigint(20) unsigned | NO   | PRI | NULL    |       |
| proxy_hostid       | bigint(20) unsigned | YES  | MUL | NULL    |       |
| host               | varchar(128)        | NO   | MUL |         |       |
| status             | int(11)             | NO   | MUL | 0       |       |
...
| name               | varchar(128)        | NO   | MUL |         |       |
...

So when I do : select host, name from hosts;

+---------------------------------+---------------------------------+
| host                            | name                            |
+---------------------------------+---------------------------------+
| ansi06p                         | srv_cli1                        |
| ansi04p                         | srv_cli2                        |
| ansi03p                         | srv_cli3                        |
...

So i think you should use the field "name" to get the visible name.

Hope this help

[vlam]

Thanks for the reply

Just for my sake, I am not good with db queries

The script file should look like

select
g.name as 'Client',
SUBSTRING_INDEX(h.host,'.domain.com',1) as 'Server name'
from
hosts h,
hosts_groups hg,
groups g
where
g.groupid=hg.groupid
and
h.status = 0
and
hg.hostid=h.hostid
and
hg.hostname=h.hostname
order by g.name



Thanks

[sca_ebe]

Hello,

Try something like this :

select
g.name as 'Client',
h.name as 'Server name'
from
hosts h,
hosts_groups hg,
groups g
where
g.groupid=hg.groupid
and
h.status = 0
and
hg.hostid=h.hostid
and
hg.hostname=h.hostname
order by g.name

[vlam]

Thanks for the help

did not need the following

and
hg.hostname=h.hostname

and changed h.hosts to h.name

All perfect now

[vlam]

If I need to add Server IP also to this query

Would that be done like:

select
g.name as 'Client',
h.name as 'Server name',
h.ip as 'Server IP'
from
hosts h,
hosts_groups hg,
groups g
where
g.groupid=hg.groupid
and
h.status = 0
and
hg.hostid=h.hostid
and
hg.hostid=h.hostip
order by g.name

[sca_ebe]

Nop, this won't work because ip are stored in another table (interface)

This work :

select g.name as 'Client', h.name as 'Server name', i.ip as 'IP Address'
from hosts h, hosts_groups hg, interface i, groups g
where g.groupid=hg.groupid and h.status = 0 and hg.hostid=h.hostid and h.hostid=i.hostid order by g.name;