Creating NPM reports that query an external database

AndrewW

I know this question must have been asked before, but I am unable to find the right keywords to return an answer.

We'd like to incorporate data from systems other than NPM in our NPM reports via SQL Query. Is this possible? I seem to remember a way to populate custom properties with external data (other than .CSV import) but must have imagined it because I can't find it now.

FormerMember

Andrew, In a nutshell - yes it can be done.

I have written a few advanced SQL reports which pulls in data from our Ciscoworks server so its data will display on an Orion report.

This is a sample report query which pulls data from both Orion and Ciscoworks:

select *
from
(
select  Description, PortName, ConfiguredDuplex,ConfiguredSpeed, NegotiatedDuplex, NegotiatedSpeed, slotnumber, deviceportid,lastmodifieddate
from
openrowset('sqloledb','1xx.1xx.2xx.5x\CISCOWORKS';'MyUserName';'MyPassword',
'select * from CWNCM.dbo.RN_DEVICE_PORT')
where deviceid in (271) and portstatus like '%up%' and Description <> ''
)a
where exists
(select 1 from
(
select  Description, PortName, ConfiguredDuplex,ConfiguredSpeed, NegotiatedDuplex, NegotiatedSpeed, slotnumber, deviceportid,lastmodifieddate
from
openrowset('sqloledb','1xx.1xx.2xx.5x\CISCOWORKS';'MyUserName';'MyPassword',
'select * from CWNCM.dbo.RN_DEVICE_PORT')
where deviceid in (271) and portstatus like '%up%' and Description <> ''
)b where a.Portname = b.Portname having max(b.lastmodifieddate)=a.lastmodifieddate)
order by Portname

Good luck!