Useful queries for HP Network Node Manager - part 1

As I use HP Network Node Manager every day in my professional life, I would like to share with you sting DB queries which have been of great help.

The queries are for the embedded PostgreSQL database and are tested on version 10.00 of HP Network Node Manager.

Query all the incidents by profile descripion or notes field:

select i.time_first, n.name, p.descr, i.formatted_message, j.notes
from nms_incidents i, nms_incident_journal j, nms_node n, nms_device_profile p
where 1=1 
and j.id = i.id
and n.device_profile=p.id
and j.notes like '%Ticket%'
and j.notes not like '%IR%'
and n.uuid=i.node_uuid
and p.descr like 'F5%'
--and j.notes = ' 123456789 '

Find strings in filter expressions

select *
from nms_payload_filter_expr
where expression like '%STRING%'

select *
from nms_iface_filter_expression
where expression like '%STRING%'

select *
from nms_node_filter_expression ex, nms_node_groups gr
where ex.expression like '%STRING%'
and ex.node_group = gr.id

Find Communication region, node name and SNMPv3 settings name

select nod.name as node_name, co.name as comm_region_name, us.name as snmpv3_settings_name  from
        nms_usm_settings us, nms_region_usm re, nms_snmp_v3_settings se, nms_communication_region co, nms_snmp_agent ag, nms_node nod, nms_snmp_agent_settings sett
        where true
        and us.id = se.usm_settings
        and re.usm_settings = us.id
        and co.id = re.region
        and nod.snmp_agent = ag.id
        and sett.id = ag.id
        and se.agent_uuid = ag.uuid
        and us.name = 'xxxxxx'

Get nodes by node group

select * from nms_node nod, nms_nodegrp_assn ass, nms_node_groups gro
where gro.name in ('group1', 'group2')
and gro.id = ass.grp
and nod.id = ass.node