How to get a list of the DA's ItemID from PC/Portal MySQL database

Products

• CA Performance Management - Usage and Administration
• DX NetOps

Issue/Introduction

Based on the KB article we need to get a list of the ItemIDs from DA, by running a REST API.

Is there a way to get it from the PC/Portal MySQL database? And export in CSV file format?

How to delete multiple devices in CA Performance Management (CAPM) Data Aggregator via REST API
https://knowledge.broadcom.com/external/article?articleId=9985

 

Environment

Release : 22.2

Resolution

If you are grabbing the devices' IDs from PC/Portal, should grab the LocalID value instead of ItemID value. The ItemID is the ID from PC and LocalID is the ID from DA.

On PC/Portal host, connect to MySQL.

# mysql -uroot -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| em                 |
| information_schema |
| mysql              |
| netqosportal       |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use netqosportal

Database changed

Or use this syntax:

# mysql -uroot -p netqosportal

mysql> show tables like 'data_sources2';
+----------------------------------------+
| Tables_in_netqosportal (data_sources2) |
+----------------------------------------+
| data_sources2                          |
+----------------------------------------+
1 row in set (0.00 sec)

1. Run the following MySQL query to find the SourceID of the DA (Data Aggregator ==> 0x40000)

mysql> select SourceID from netqosportal.data_sources2 where SourceType=0x40000;
+----------+
| SourceID |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

2. Then run the following MySQL query to list the LocalID, device name, and IP address of all devices in DA:

mysql>  select LocalID, ItemName, inet6_ntoa(Address) from netqosportal.dst_device where SourceID=X;

Based on the output of the first MySQL query output, SourceID=3:

mysql> select LocalID, ItemName, inet6_ntoa(Address) from netqosportal.dst_device where SourceID=3;

 

Or run this single MySQL query:

mysql> \T /tmp/output.txt  (enable MySQL logging to the /tmp/output.txt file)

mysql> select LocalID, ItemName, inet6_ntoa(Address) from netqosportal.dst_device where SourceID IN(select SourceID from netqosportal.data_sources2 where SourceType=0x40000);

mysql> \t  (disable logging)

Additional Information

You can dump the MySQL query output into a .CSV (comma-separated values) file format (in a bash shell):

# mysql -h localhost -u root -p -e 'select LocalID, ItemName, inet6_ntoa(Address) from netqosportal.dst_device where SourceID IN(select SourceID from netqosportal.data_sources2 where SourceType=0x40000)' | sed 's/\t/,/g' > /tmp/output.csv

Supply the MySQL root's password. The output is saved in the /tmp/output.csv file.