Using data in the netqosportal database on Performance Center, we're generating a series of reports to ensure data is consistent between Spectrum and PC, ensure users/roles are created according to our standards etc... This is working well, but one thing we'd like to do is to identify any SNMP profiles which are not in use. We already have 157 SNMP profiles and it's already at a stage where we cannot be certain if they're all used or not.
We know the profiles are stored in the t_profile table with a unique ItemID, but we cannot see a way in the database schema that this is mapped to a device in the t_device table.
Can anyone advise how we can determine the link between SNMP profiles and devices in the netqosportal database?
Unfortunately, I do not see an easy way to accomplish what you are after. As we do not support directly querying the database the route you should take will be utilizing the Data Aggregator REST Webservice.
You will want to get a list of all SNMP Profiles from:
Then compare that to the devices page below and see what is missing:
This is because the devices are not tied to the SNMP Profile but the SNMP Profile is tied to the devices so there is no way to say "Show me all devices using SNMP Profile xyz"
That approach should work for us. How do I include the SNMP Profile in the output of my query to http://DA_Hostname:8581/rest/devices/manageable? How do I obtain a list containing device name (alternatename in our case) and the corresponding SNMP profile?
In the manageable endpoint, you can find the SNMP Profile as SNMPProfileID and that corresponds to the profiles endpoint. The alternate name is also contained within the manageable endpoint. Here is a pair of examples utilizing a filter to list all devices that have the SNMP Profile ID of 4943:
curl --header "Content-Type: application/xml" --request POST --data '<FilterSelect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="filter.xsd"><Filter><ManageableDevice.SNMPProfileID type="EQUAL">4943</ManageableDevice.SNMPProfileID></Filter><Select use="exclude" isa="exclude"><Item><AlternateName use="include"/></Item></Select></FilterSelect>' --url http://$DAhostname:8581/rest/devices/manageable/filtered
Via a REST client:
<FilterSelect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="filter.xsd"> <Filter> <ManageableDevice.SNMPProfileID type="EQUAL">4943</ManageableDevice.SNMPProfileID> </Filter> <Select use="exclude" isa="exclude"> <Item>
<AlternateName use="include"/> </Item> </Select></FilterSelect>
The body of this specifies to MATCH a SNMPProfileID EQUAL to 4943 then EXCLUDE all data (will always show at least the item id) then INCLUDE AlternateName.
The result will look something like:
<ManageableDeviceList> <ManageableDevice version="1.0.0"> <ID>5858</ID> <Item version="1.0.0"> <AlternateName>MyNameIs-ALTERNATE</AlternateName> </Item>
You will need to run through as many iterations of this as you have SNMP Profile ID's. I will leave the parsing of the data up to you but in my experiences, I have utilized the CURL version above and use xml_grep to parse IDs out of a returned query to get them in a numerical list with no extra characters.