How to get an inventory list from vertica

Products

• CA Infrastructure Management
• CA Infrastructure Management
• CA Performance Management - Usage and Administration
• CA Performance Management - Data Polling

Issue/Introduction

How can I get a device inventory out of the vertica database?

Environment

CAPM 3.x

Resolution

To get a device inventory out of the vertica database do the following

1. Log on to the DR
2. su - dradmin
3. cd /opt/vertica/bin
4. Run the following query to get ALL devices

./vsql -U dradmin -w dbpass -o /tmp/AllDevices.txt -c "select d.item_id, v6_ntoa(d.primary_ip_address), d.hostname, i.item_name, p.is_pingable, m.is_manageable from dauser.device d join dauser.item i on d.item_id=i.item_id left join (select item_id, true as is_pingable from dauser.v_item_facet where facet_qname='{http://im.ca.com/inventory}AccessibleDevice') p on p.item_id=d.item_id left join(select item_id, true as is_manageable from dauser.v_item_facet where facet_qname='{http://im.ca.com/inventory}ManageableDevice') m on m.item_id=d.item_id order by item_id;"
 

Check the tmp directory for AllDevices.txt.  This will show an inventory of your devices, including wether a device is manageable or pingable