OPENNMS psql how to get host and catagories

 

#psql opennms -h localhost -d opennms

Paste sql command below

 SELECT

    n.nodesysname,

    COALESCE(s_cat.categoryname, 'no category') AS categoryname

  FROM

    node n

  LEFT JOIN

    category_node cn

  ON

    n.nodeid = cn.nodeid

  LEFT JOIN

    categories s_cat

  ON

    cn.categoryid = s_cat.categoryid;

opennms=>   SELECT

opennms->     n.nodesysname,

opennms->     COALESCE(s_cat.categoryname, 'no category') AS categoryname

opennms->   FROM

opennms->     node n

opennms->   LEFT JOIN

opennms->     category_node cn

opennms->   ON

opennms->     n.nodeid = cn.nodeid

opennms->   LEFT JOIN

opennms->     categories s_cat

opennms->   ON

opennms->     cn.categoryid = s_cat.categoryid order by nodesysname;