Need UnDP Report showing labels and data on the same row for multiple devices

hebron1212

I have 2 UnDP's running on multiple nodes. One collects the data and the other collects the labels as follows:

Data

$ snmpwalk ... 1.3.6.1.4.1.9.9.543.1.2.3.1.14.1000.1
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.14.1000.1.1 = INTEGER: 1
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.14.1000.1.2 = INTEGER: 1
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.14.1000.1.3 = INTEGER: 1
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.14.1000.1.4 = INTEGER: 1
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.14.1000.1.5 = INTEGER: 3
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.14.1000.1.6 = INTEGER: 1
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.14.1000.1.7 = INTEGER: 1
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.14.1000.1.8 = INTEGER: 1

Label

$ snmpwalk ...1.3.6.1.4.1.9.9.543.1.2.3.1.3.1000.1
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.3.1000.1.1 = STRING: "Ipservices"
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.3.1000.1.2 = STRING: "Ipservices eval"
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.3.1000.1.3 = STRING: "Ipbase"
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.3.1000.1.4 = STRING: "Ipbase eval"
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.3.1000.1.5 = STRING: "Lanbase"
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.3.1000.1.6 = STRING: "Apcount eval"
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.3.1000.1.7 = STRING: "Base apcount"
SNMPv2-SMI::enterprises.9.9.543.1.2.3.1.3.1000.1.8 = STRING: "Adder apcount"

The database holds data and labels in separate rows rather than columns. How can I create a report showing the label and data on the same row?

I can get close with a single device, but once I add devices, the data gets more and more disorganized.

CustomPollerAssignmentID	Status	RowID	AssignmentName
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	1	CiscoLicenseEnabled on switchA
7656a43f-ba1f-487b-b060-2e8d0e850e38	Ipservices	1	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	2	CiscoLicenseEnabled on switchA
7656a43f-ba1f-487b-b060-2e8d0e850e38	Ipservices eval	2	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	3	CiscoLicenseEnabled on switchA
7656a43f-ba1f-487b-b060-2e8d0e850e38	Ipbase	3	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	4	CiscoLicenseEnabled on switchA
7656a43f-ba1f-487b-b060-2e8d0e850e38	Ipbase eval	4	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	3	5	CiscoLicenseEnabled on switchA
7656a43f-ba1f-487b-b060-2e8d0e850e38	Lanbase	5	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	6	CiscoLicenseEnabled on switchA
7656a43f-ba1f-487b-b060-2e8d0e850e38	Apcount eval	6	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	7	CiscoLicenseEnabled on switchA
7656a43f-ba1f-487b-b060-2e8d0e850e38	Base apcount	7	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	8	CiscoLicenseEnabled on switchA
7656a43f-ba1f-487b-b060-2e8d0e850e38	Adder apcount	8	CiscoLicenseLabel on switchA

CustomPollerAssignmentID	Status	RowID	AssignmentName
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	1	CiscoLicenseEnabled on switchA
1a201245-538a-4046-b55b-1c42403a409f	1	1	CiscoLicenseEnabled on switchB
5f2165a8-4024-4e3b-adb9-a6a00868c356	Ipservices	1	CiscoLicenseLabel on switchB
7656a43f-ba1f-487b-b060-2e8d0e850e38	Ipservices	1	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	2	CiscoLicenseEnabled on switchA
1a201245-538a-4046-b55b-1c42403a409f	1	2	CiscoLicenseEnabled on switchB
5f2165a8-4024-4e3b-adb9-a6a00868c356	Ipservices eval	2	CiscoLicenseLabel on switchB
7656a43f-ba1f-487b-b060-2e8d0e850e38	Ipservices eval	2	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	3	CiscoLicenseEnabled on switchA
1a201245-538a-4046-b55b-1c42403a409f	1	3	CiscoLicenseEnabled on switchB
5f2165a8-4024-4e3b-adb9-a6a00868c356	Ipbase	3	CiscoLicenseLabel on switchB
7656a43f-ba1f-487b-b060-2e8d0e850e38	Ipbase	3	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	4	CiscoLicenseEnabled on switchA
1a201245-538a-4046-b55b-1c42403a409f	1	4	CiscoLicenseEnabled on switchB
5f2165a8-4024-4e3b-adb9-a6a00868c356	Ipbase eval	4	CiscoLicenseLabel on switchB
7656a43f-ba1f-487b-b060-2e8d0e850e38	Ipbase eval	4	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	3	5	CiscoLicenseEnabled on switchA
1a201245-538a-4046-b55b-1c42403a409f	3	5	CiscoLicenseEnabled on switchB
5f2165a8-4024-4e3b-adb9-a6a00868c356	Lanbase	5	CiscoLicenseLabel on switchB
7656a43f-ba1f-487b-b060-2e8d0e850e38	Lanbase	5	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	6	CiscoLicenseEnabled on switchA
1a201245-538a-4046-b55b-1c42403a409f	1	6	CiscoLicenseEnabled on switchB
5f2165a8-4024-4e3b-adb9-a6a00868c356	Apcount eval	6	CiscoLicenseLabel on switchB
7656a43f-ba1f-487b-b060-2e8d0e850e38	Apcount eval	6	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	7	CiscoLicenseEnabled on switchA
1a201245-538a-4046-b55b-1c42403a409f	1	7	CiscoLicenseEnabled on switchB
5f2165a8-4024-4e3b-adb9-a6a00868c356	Base apcount	7	CiscoLicenseLabel on switchB
7656a43f-ba1f-487b-b060-2e8d0e850e38	Base apcount	7	CiscoLicenseLabel on switchA
fb1a1e57-5946-43a1-89e1-17b131c9b0c9	1	8	CiscoLicenseEnabled on switchA
1a201245-538a-4046-b55b-1c42403a409f	1	8	CiscoLicenseEnabled on switchB
5f2165a8-4024-4e3b-adb9-a6a00868c356	Adder apcount	8	CiscoLicenseLabel on switchB
7656a43f-ba1f-487b-b060-2e8d0e850e38	Adder apcount	8	CiscoLicenseLabel on switchA

Here's the query I'm using:

SELECT TOP 1000 cps.CustomPollerAssignmentID, cps.Status, RowID, cpa.AssignmentName
FROM Orion.NPM.CustomPollerStatus cps
inner join Orion.NPM.CustomPollerAssignment as [cpa] on cpa.custompollerassignmentID = cps.CustomPollerAssignmentID
where cpa.AssignmentName like 'CiscoLicense%'
order by RowID, Status

jajao555

You would join the table back on itself to move rows to a different column if that makes sense.

Here's an example of something similar we have but is the same idea.

Also look in Orion.NPM.CustomPollerAssignmentOnNode there's alot of pre joined tables you could use from there.

SELECT
N.NodeID,
N.IPAddress,
CPON.CurrentValue AS Battery_Type,
CPON1.CurrentValue AS Battery_Replace_Date,
CPON2.CurrentValue AS Chassis_Model,
CPON3.CurrentValue AS Chassis_Serial,
CPON4.CurrentValue AS Ident_Software,
SUBSTRING(N.NodeDescription,CharIndex('MN:',N.NodeDescription),9) AS Mgmt_Card, 
SUBSTRING(N.NodeDescription,CharIndex('SN:',N.NodeDescription),16) AS Mgmt_Card_Serial,
N.IOSVersion AS Mgmt_Card_Firmware,
N.Caption,
N.Vendor,
N.MachineType AS Model,
N.CustomProperties.Address,
N.CustomProperties.City,
N.CustomProperties.LATA
FROM Orion.Nodes N

LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode CPON
    ON N.NodeID = CPON.NodeID AND CPON.CustomPollerName = 'upsAdvBatteryInternalSKU'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode CPON1
    ON N.NodeID = CPON1.NodeID AND CPON1.CustomPollerName = 'upsAdvBatteryRecommendedReplaceDate'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode CPON2
    ON N.NodeID = CPON2.NodeID AND CPON2.CustomPollerName = 'upsAdvIdentSkuNumber'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode CPON3
    ON N.NodeID = CPON3.NodeID AND CPON3.CustomPollerName = 'upsSerialNumber'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode CPON4
    ON N.NodeID = CPON4.NodeID AND CPON4.CustomPollerName = 'upsIdentUPSSoftwareVersion'

WHERE N.Vendor = 'American Power Conversion Corp.'