How to get a list of all devices in Management Lost status or NOT Up status

Products

• Network Observability
• CA Performance Management

Issue/Introduction

Could you provide a query to get a list of all devices in "Management Lost" status? Or NOT Up status?

Environment

Performance Management: Any version

Resolution

The following MySQL query (run on the Portal's MySQL database) will retrieve all devices with contact status NOT Up:

mysql -h localhost -u root -p -e 'select LocalID,ItemName,ItemSubTypeName,ContactStatus from netqosportal.dst_device where SourceID IN(select SourceID from netqosportal.data_sources2 where SourceType=0x40000) and dst_device.ContactStatus != "UP"' | sed 's/\r$//' > /tmp/output.csv

 

The following MySQL query (run on the Portal's MySQL database) will retrieve all devices with Management Lost status:

mysql -h localhost -u root -p -e 'select LocalID,ItemName,ItemSubTypeName,ContactStatus from netqosportal.dst_device where SourceID IN(select SourceID from netqosportal.data_sources2 where SourceType=0x40000) and dst_device.ContactStatus = "MANAGEMENT_LOST"' | sed 's/\r$//' > /tmp/output.csv

 

The following MySQL query (run on the Portal's MySQL database) will retrieve all devices with DC Connection Lost status:

mysql -h localhost -u root -p -e 'select LocalID,ItemName,ItemSubTypeName,ContactStatus from netqosportal.dst_device where SourceID IN(select SourceID from netqosportal.data_sources2 where SourceType=0x40000) and dst_device.ContactStatus = "DC_CONNECTION_LOST"' | sed 's/\r$//' > /tmp/output.csv

 

The following MySQL query (run on the Portal's MySQL database) will retrieve all devices with DC Connection Lost and Management Lost status:

mysql -h localhost -u root -p -e 'select LocalID,ItemName,ItemSubTypeName,ContactStatus from netqosportal.dst_device where SourceID IN(select SourceID from netqosportal.data_sources2 where SourceType=0x40000) and dst_device.ContactStatus = "DC_CONNECTION_LOST|MANAGEMENT_LOST"' | sed 's/\r$//' > /tmp/output.csv