3 Replies Latest reply on Feb 17, 2010 2:21 PM by AndrewW

    Creating NPM reports that query an external database

      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.

        • Re: Creating NPM reports that query an external database
          sotherls

          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!

          • Re: Creating NPM reports that query an external database
            darryld

            The good news is that this is possible but the bad news is that I can't access a sample report to show you how.

            IIRC you will need to create a linked database using sp_addlinkedserver and providing the required details.