Sie sind hier

select hosts and their servicechecks

3 posts / 0 new
Letzter Beitrag
jankulir
Bild des Benutzers jankulir
select hosts and their servicechecks

Hi

i'm looking for select that would return current hosts and their current servicechecks from opsview database

i have tried querying the "odw" database with:

select h.name, h.alias, h.hostgroup, s.name
from hosts h, servicechecks s
where h.id = s.host
order by h.name;

but it returns lot of duplicates

the columns active_date and most_recent might be usefull somehow, but i cant figure how

I have also tried looking on "opsview" databese, but its hosts table does only seem to contain system tables of osview itself, not any user-created hosts

thanks for suggestions

 

 

awijntje
Bild des Benutzers awijntje
Re: select hosts and their servicechecks

he there,

I think you should be looking at the RUNTIME database to be honest.

The Opsview DB contains the configuration, the ODW is datawarehousing (and is only available in Pro or Enterprise) while the RUNTIME DB contains the current configuration and servicechecks (including results and such).

Just on a side note what are you trying to achieve as it sounds like you might be better of with the API for getting this kind of information??

Hope this helps,

Alan

jankulir
Bild des Benutzers jankulir
Re: select hosts and their servicechecks

thanks, i was after the configuration but was looking on wrong instance of DB belonging to some testcase,

it was the "opsview" DB in the end, I've pieced the select together from some older select one of may colleagues left and polished it

The idea was to get complete overview of Hosts and assigned checks and their details, and if anyone finds it helpful the select is this:

-----

select
    d.name as hostgroup,
    a.name as hostname,
    a.ip as host_ip,
    c.name as servicecheck,
    a.notification_interval,
    tp.name as notification_period,
    c.check_interval,
    c.check_attempts,
    c.retry_check_interval,
    ct.name as checktype,
    c.plugin,
    IF(ISNULL(she.args),'NO','YES') as args_exception,
    IFNULL(she.args,c.args) as args
from hosts a
    join hostservicechecks b on a.id = b.hostid
    join servicechecks c on b.servicecheckid = c.id
    join hostgroups d on a.hostgroup = d.id
    join checktypes ct on c.checktype = ct.id
    join timeperiods tp on a.notification_period = tp.id
    left outer join servicecheckhostexceptions she on (a.id = she.host and c.id = she.servicecheck)
where b.remove_servicecheck = 0
UNION
select
    d.name as hostgroup,
    a.name as hostname,
    a.ip as host_ip,
    c.name as servicecheck,
    a.notification_interval,
    tp.name as notification_period,
    c.check_interval,
    c.check_attempts,
    c.retry_check_interval,
    ct.name as checktype,
    c.plugin,
    IF(ISNULL(shte.args),'NO','YES') as args_exception,
    IFNULL(shte.args,c.args) as args
from hosts a
    join hosthosttemplates y on a.id = y.hostid
    join hosttemplates x on y.hosttemplateid = x.id
    join hosttemplateservicechecks z on x.id = z.hosttemplateid
    join servicechecks c on z.servicecheckid = c.id
    join hostgroups d on a.hostgroup = d.id
    join checktypes ct on c.checktype = ct.id
    join timeperiods tp on a.notification_period = tp.id
    left outer join servicecheckhosttemplateexceptions shte on (x.id = shte.hosttemplate and c.id = shte.servicecheck)
order by hostgroup, hostname, servicecheck
;