19 Replies Latest reply on May 20, 2008 6:15 PM by daveh

    Custom Poller Inventory Report

    daveh

      Hi folks,


      I have many Cisco routers and switches with varying IOS and CatOS version. I have loads of custom mibs, i.e. temp, flash size, bin file etc. some of these custom mibs don't work on certain devices, and rather than view the node details of every switch and router, can I create a report which displays every cisco device and give the status of the custom poller?


      for example it would look like this...


      Node Name_________temp________flash size________bin file


      switch 1____________46C_________45MB___________xyz.bin


      switch 1____________no data_________45MB___________xyz.bin


      switch 1____________46C_________no data___________xyz.bin


      router 1____________46C_________45MB___________no data


       


      thanks


      Dave

        • Re: Custom Poller Inventory Report
          daveh

           Having this display on a single web page such as a summary would also be great.

            • Re: Custom Poller Inventory Report
              daveh

              Anyone? there must be someone who has also requested this??? Another custom poller that would be great to have in the inventory report would be the device serial number which I also have custom pollers for.


               


              Dave

                • Re: Custom Poller Inventory Report

                  You are not on your own, I would also like this feature. The only way I can see this being acheived is by using an SQL based report but I don't think it would be straight forward  

                    • Re: Custom Poller Inventory Report
                      SELECT DISTINCT CustomPollerAssignment_1.NodeID,Nodes.Plant,Nodes.Subnet,Nodes.Caption,
                                               RIGHT( (SELECT     CustomPollerStatus.Status
                                                  FROM          CustomPollerStatus INNER JOIN
                                                                         CustomPollerAssignment ON
                                                                         CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
                                                  WHERE      (CustomPollerAssignment_1.NodeID = CustomPollerAssignment.NodeID) AND
                                                                         (CustomPollerAssignment.PollerID = '099ef9d0-5059-4aae-a29a-57389a1381bb'))+'.????' ,14)  AS 'STP MacRoot',
                                                 (SELECT     CustomPollerStatus_2.RawStatus
                                                  FROM          CustomPollerStatus AS CustomPollerStatus_2 INNER JOIN
                                                                         CustomPollerAssignment AS CustomPollerAssignment_2 ON
                                                                         CustomPollerAssignment_2.CustomPollerAssignmentID = CustomPollerStatus_2.CustomPollerAssignmentID
                                                  WHERE      (CustomPollerAssignment_1.NodeID = CustomPollerAssignment_2.NodeID) AND
                                                                         (CustomPollerAssignment_2.PollerID = 'beed6ccc-b59a-4c97-9d6b-4e84f1dc5481'))/100/60/60/24 AS 'STP Uptime (days)'
                      FROM         CustomPollerAssignment AS CustomPollerAssignment_1 INNER JOIN
                                            CustomPollers ON CustomPollerAssignment_1.PollerID = CustomPollers.PollerID INNER JOIN
                                            Nodes ON CustomPollerAssignment_1.NodeID = Nodes.NodeID INNER JOIN
                                            CustomPollerStatus AS CustomPollerStatus_1 ON
                                            CustomPollerAssignment_1.CustomPollerAssignmentID = CustomPollerStatus_1.CustomPollerAssignmentID
                      WHERE    ( (CustomPollerAssignment_1.PollerID = '099ef9d0-5059-4aae-a29a-57389a1381bb') OR
                                            (CustomPollerAssignment_1.PollerID = 'beed6ccc-b59a-4c97-9d6b-4e84f1dc5481')) AND Nodes.SysObjectID = '1.3.6.1.4.1.9.1.516'
                      ORDER BY Nodes.Caption
                        • Re: Custom Poller Inventory Report

                          My last post above shows an example SQL report which displays 2 custom node properties and 2 custom poller values.


                          If you can work out my SQL you might be able to mod my SQL code to do what you want.


                          I might play about with my code Today and post a simpler version later 


                           


                            • Re: Custom Poller Inventory Report

                              OK, I have simplified the SQL stement ( a bit)  and knocked up a rough guide on how to change my SQL to your requirments


                              You will need to have some kind of SQL knowledge to be able to work it all out.


                              I will post the code next and then the guide 


                               cheers


                                • Re: Custom Poller Inventory Report

                                  SELECT DISTINCT CustomPollerAssignment_1.NodeID,Nodes.Caption,

                                  (SELECT CustomPollerStatus.Status

                                  FROM CustomPollerStatus INNER JOIN

                                  CustomPollerAssignment ON

                                  CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID

                                  WHERE (CustomPollerAssignment_1.NodeID = CustomPollerAssignment.NodeID) AND

                                  (CustomPollerAssignment.PollerID = '61A9A17E-29FC-4077-9367-A3114B48E23B')) AS 'Temp'

                                  ,

                                  (SELECT CustomPollerStatus_2.Status

                                  FROM CustomPollerStatus AS CustomPollerStatus_2 INNER JOIN

                                  CustomPollerAssignment AS CustomPollerAssignment_2 ON

                                  CustomPollerAssignment_2.CustomPollerAssignmentID = CustomPollerStatus_2.CustomPollerAssignmentID

                                  WHERE (CustomPollerAssignment_1.NodeID = CustomPollerAssignment_2.NodeID) AND

                                  (CustomPollerAssignment_2.PollerID = '4556A5BF-C838-4CE8-927C-85CD20838C8B')) AS 'FanState'

                                  ,

                                  (SELECT CustomPollerStatus_3.Status

                                  FROM CustomPollerStatus AS CustomPollerStatus_3 INNER JOIN

                                  CustomPollerAssignment AS CustomPollerAssignment_3 ON

                                  CustomPollerAssignment_3.CustomPollerAssignmentID = CustomPollerStatus_3.CustomPollerAssignmentID

                                  WHERE (CustomPollerAssignment_1.NodeID = CustomPollerAssignment_3.NodeID) AND

                                  (CustomPollerAssignment_3.PollerID = '4C7310B6-28AD-49ED-8125-CC1DC66235A7')) AS 'MajorAlarm'

                                  ,

                                  (SELECT CustomPollerStatus_4.Status

                                  FROM CustomPollerStatus AS CustomPollerStatus_4 INNER JOIN

                                  CustomPollerAssignment AS CustomPollerAssignment_4 ON

                                  CustomPollerAssignment_4.CustomPollerAssignmentID = CustomPollerStatus_4.CustomPollerAssignmentID

                                  WHERE (CustomPollerAssignment_1.NodeID = CustomPollerAssignment_4.NodeID) AND

                                  (CustomPollerAssignment_4.PollerID = '{56144F1C-09A7-4E78-983B-B1E27F0CE317}')) AS 'MinorAlarm'

                                  ,

                                  (SELECT CustomPollerStatus_5.Status

                                  FROM CustomPollerStatus AS CustomPollerStatus_5 INNER JOIN

                                  CustomPollerAssignment AS CustomPollerAssignment_5 ON

                                  CustomPollerAssignment_5.CustomPollerAssignmentID = CustomPollerStatus_5.CustomPollerAssignmentID

                                  WHERE (CustomPollerAssignment_1.NodeID = CustomPollerAssignment_5.NodeID) AND

                                  (CustomPollerAssignment_5.PollerID = '{3DA5C360-5561-40F6-A87F-C00C6ED8BB69}')) AS 'TempAlarm'

                                  ,

                                  (SELECT CustomPollerStatus_6.Status

                                  FROM CustomPollerStatus AS CustomPollerStatus_6 INNER JOIN

                                  CustomPollerAssignment AS CustomPollerAssignment_6 ON

                                  CustomPollerAssignment_6.CustomPollerAssignmentID = CustomPollerStatus_6.CustomPollerAssignmentID

                                  WHERE (CustomPollerAssignment_1.NodeID = CustomPollerAssignment_6.NodeID) AND

                                  (CustomPollerAssignment_6.PollerID = '{C381CF5D-5705-43BF-8083-88280EA00EE4}')) AS 'PSUState'

                                   

                                  FROM CustomPollerAssignment AS CustomPollerAssignment_1 INNER JOIN

                                  CustomPollers ON CustomPollerAssignment_1.PollerID = CustomPollers.PollerID INNER JOIN

                                  Nodes ON CustomPollerAssignment_1.NodeID = Nodes.NodeID INNER JOIN

                                  CustomPollerStatus AS CustomPollerStatus_1 ON

                                  CustomPollerAssignment_1.CustomPollerAssignmentID = CustomPollerStatus_1.CustomPollerAssignmentID

                                  WHERE Nodes.SysObjectID = '1.3.6.1.4.1.9.1.516'

                                  ORDER BY Nodes.Caption

                                    • Re: Custom Poller Inventory Report

                                      Step 1)
                                      Determine CustomPollerAssignment 'PollerIDs' by running the following SQL statement


                                      Select *
                                      From CustomPollers
                                      order by UniqueName



                                      Step 2)
                                      Near the end of the example SQL script you will see the following WHERE statement


                                      WHERE     Nodes.SysObjectID = '1.3.6.1.4.1.9.1.516'


                                      This determines which nodes you wish to show in your table.
                                      In the example I am filtering out a CIsco Layer 3 switch which has a SysObjectID = '1.3.6.1.4.1.9.1.516'


                                      You would need to determine the SysObjectID of the device you want to display. This can be done by looking at the Node details page against a node in the 'Orion Network Performance Monitor'


                                      Step 3)
                                      In the main SQL statement you will see multiple SELECT statements in the following form


                                          (SELECT     CustomPollerStatus_5.Status
                                          FROM          CustomPollerStatus AS CustomPollerStatus_5 INNER JOIN
                                                                 CustomPollerAssignment AS CustomPollerAssignment_5 ON
                                                                 CustomPollerAssignment_5.CustomPollerAssignmentID = CustomPollerStatus_5.CustomPollerAssignmentID
                                          WHERE      (CustomPollerAssignment_1.NodeID = CustomPollerAssignment_5.NodeID) AND
                                                                 (CustomPollerAssignment_5.PollerID = '{3DA5C360-5561-40F6-A87F-C00C6ED8BB69}')) AS 'TempAlarm'


                                      Each of the multiple select staements relates to each custom pollers that you want to display


                                      The main example script displays 5 columns of custom pollers values. To customize my SQL, you can change the PollerID and Description of each column by changing the relevent parts of the each select statment


                                      For example you may have a custom poller called 'CPU temperature' which you want to display.
                                      First you must determine the Poller ID from Step 1. You need the long hexadecimal string.
                                      Let's say you end up with the following string -->  {01234567-0123-0123-0123-012345678901}
                                      You then determine the collumn you wish the data to be displayed in. In this example we will use collumn 5.
                                      You would therfore replace the relevent text in the select staement that refers to collumn 5.
                                      It is fairly obvious as the select statement will have various parts of the statement with '_5' in it.
                                      Once you have determined this, replace the PollerID and description with the desired PollerID details


                                          (SELECT     CustomPollerStatus_5.Status
                                          FROM          CustomPollerStatus AS CustomPollerStatus_5 INNER JOIN
                                                                 CustomPollerAssignment AS CustomPollerAssignment_5 ON
                                                                 CustomPollerAssignment_5.CustomPollerAssignmentID = CustomPollerStatus_5.CustomPollerAssignmentID
                                          WHERE      (CustomPollerAssignment_1.NodeID = CustomPollerAssignment_5.NodeID) AND
                                                                 (CustomPollerAssignment_5.PollerID = '{01234567-0123-0123-0123-012345678901}')) AS 'CPU temperature'


                                      Step 4)
                                      If you require more that 5 collumns you will need to add additional SELECT statements and Change the


                                      FOr example, if you add a sixth collumn you would add simething like



                                          (SELECT     CustomPollerStatus_6.Status
                                          FROM          CustomPollerStatus AS CustomPollerStatus_6 INNER JOIN
                                                                 CustomPollerAssignment AS CustomPollerAssignment_6 ON
                                                                 CustomPollerAssignment_6.CustomPollerAssignmentID = CustomPollerStatus_6.CustomPollerAssignmentID
                                          WHERE      (CustomPollerAssignment_1.NodeID = CustomPollerAssignment_6.NodeID) AND
                                                                 (CustomPollerAssignment_6.PollerID = '{01234567-0123-0123-0123-012345678901}')) AS 'CPU temperature'
                                       
                                      Notice that this SELECT statement has the string '_6' in in multiple times


                                      NOTE------ Also make sure that there is a , between each of the SELECT statements

                                        • Re: Custom Poller Inventory Report
                                          daveh

                                          this is superb Roger, thanks for spending the time doing this.


                                          I can see the custom poller of the object I select as per your post. Is there a way to have multiple nodes? I'd like to do a blanket query on all my nodes if that's possible, or at least just the cisco ones?


                                          thanks


                                          Dave

                                            • Re: Custom Poller Inventory Report
                                              daveh

                                              hey I found it,

                                              WHERE Nodes.SysObjectID like '1.3.6.1.4.1.9.1%'

                                              thanks again

                                                • Re: Custom Poller Inventory Report
                                                  daveh

                                                  not to be to fussy or anything :)


                                                  but can I also add in custom properties as well to the output?

                                                    • Re: Custom Poller Inventory Report

                                                      Hi Dave,


                                                      Sorry for the delay in responding. I had subscribed to this thread so that I would receive email notification but I didn't get any in my inbox!


                                                      Anyway, Yes you can add Custom props


                                                      The simplist way is to add them after the first part of the SQL statement


                                                      SELECT DISTINCT CustomPollerAssignment_1.NodeID,Nodes.Caption,


                                                      For example, if you have a custom prop called 'owner'


                                                      you would change the line to


                                                      SELECT DISTINCT CustomPollerAssignment_1.NodeID,Nodes.Caption,Nodes.owner,


                                                      Make sure you seperate each custom prop with a comma


                                                       


                                                      cheers

                                                        • Re: Custom Poller Inventory Report
                                                          daveh

                                                          Roger,


                                                          The report works great for most things, what I've noticed however is that when I insert some custom pollers no details appear in the report, even though it's definetly being collected. One of the things that doesn't show up is CiscoFlashDeviceSize - EC378A32-8A13-4B0A-8B5F-2C4215CE707E, would you know why? Is it a formatting thing? All my custom pollers are setup for RAW value.


                                                          thanks


                                                          Dave

                                                            • Re: Custom Poller Inventory Report
                                                              savell

                                                              Here is another way to report this data (using a query in conjunction with a stored proceedure to do the pivot). This way you don't need to know the PollerID's - it will show you all that have been assigned.

                                                              One small problem - we don't really use custom pollers, so my testing of this query is limited (although I sure someone will tell me if it works....)

                                                              Stored proceedure:
                                                              (taken from http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html)

                                                              --Stored Procedure: sp_Crosstab
                                                              USE NetperfMon
                                                              GO
                                                              CREATE PROCEDURE dbo.sp_Crosstab
                                                                  @DBFetch varchar(4000),
                                                                  @DBWhere varchar(2000) = NULL,
                                                                  @DBPivot varchar(4000) = NULL,
                                                                  @DBField varchar(100),
                                                                  @PCField varchar(100),
                                                                  @PCBuild varchar( 20),
                                                                  @PCAdmin varchar( 20) = NULL,
                                                                  @DBAdmin int = 0,
                                                                  @DBTable varchar(100) = NULL,
                                                                  @DBWrite varchar(160) = NULL,
                                                                  @DBUltra bit = 0
                                                              AS
                                                              SET NOCOUNT ON
                                                              DECLARE @Return int
                                                              DECLARE @Retain int
                                                              DECLARE @Status int
                                                              SET @Status = 0
                                                              DECLARE @TPre varchar(10)
                                                              DECLARE @TDo3 tinyint
                                                              DECLARE @TDo4 tinyint
                                                              SET @TPre = 'tbl'
                                                              SET @TDo3 = LEN(@TPre)
                                                              SET @TDo4 = LEN(@TPre) + 1
                                                              DECLARE @DBAE varchar(40)
                                                              DECLARE @Task varchar(8000)
                                                              DECLARE @Bank varchar(4000)
                                                              DECLARE @Cash varchar(2000)
                                                              DECLARE @Rich varchar(2000)
                                                              DECLARE @DBAI varchar(4000)
                                                              DECLARE @DBAO varchar(8000)
                                                              DECLARE @DBAU varchar(2000)
                                                              DECLARE @Name varchar(100)
                                                              DECLARE @Same varchar(100)
                                                              DECLARE @Home varchar(160)
                                                              DECLARE @Some varchar(20)
                                                              DECLARE @Work int
                                                              DECLARE @Wink int
                                                              SET @DBAE = '##Crosstab' + RIGHT(CONVERT(varchar(10),@@SPID+100000),5)
                                                              SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE
                                                              EXECUTE (@Task)
                                                              CREATE TABLE #DBAT (Work int IDENTITY(1,1), Name varchar(100))
                                                              SET @Bank = @TPre + @DBFetch
                                                              IF NOT EXISTS (SELECT * FROM sysobjects WHERE RTRIM(type) = 'U' AND name = @Bank)
                                                                  BEGIN
                                                                  SET @Bank = CASE WHEN LEFT(@DBFetch,6) = 'SELECT' THEN '(' + @DBFetch + ')' ELSE @DBFetch END
                                                                  SET @Bank = REPLACE(@Bank,         CHAR(94),CHAR(39))
                                                                  SET @Bank = REPLACE(@Bank,CHAR(45)+CHAR(45),CHAR(32))
                                                                  SET @Bank = REPLACE(@Bank,CHAR(47)+CHAR(42),CHAR(32))
                                                                  END
                                                              IF @DBWhere IS NOT NULL
                                                                  BEGIN
                                                                  SET @Cash = REPLACE(@DBWhere,'WHERE'       ,CHAR(32))
                                                                  SET @Cash = REPLACE(@Cash,         CHAR(94),CHAR(39))
                                                                  SET @Cash = REPLACE(@Cash,CHAR(45)+CHAR(45),CHAR(32))
                                                                  SET @Cash = REPLACE(@Cash,CHAR(47)+CHAR(42),CHAR(32))
                                                                  END
                                                              SET @DBField = REPLACE(@DBField,CHAR(32),CHAR(95))
                                                              SET @PCField = REPLACE(@PCField,CHAR(32),CHAR(95))
                                                              SET @PCBuild = REPLACE(@PCBuild,CHAR(32),CHAR(95))
                                                              SET @PCAdmin = REPLACE(@PCAdmin,CHAR(32),CHAR(95))
                                                              SET @DBTable = REPLACE(@DBTable,CHAR(32),CHAR(95))
                                                              SET @DBWrite = REPLACE(@DBWrite,CHAR(32),CHAR(95))
                                                              SET @DBWhere = CASE WHEN @DBWhere IS NULL THEN '' ELSE ' WHERE (' + @Cash + ') AND 0 = 0' END
                                                              SET @Some = ISNULL(@PCAdmin,'NA')
                                                              SET @Task = 'SELECT * INTO ' + @DBAE + ' FROM ' + @Bank + ' AS T WHERE 0 = 1'
                                                              IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
                                                              IF @Status = 0 SET @Status = @Return
                                                              IF @DBPivot IS NOT NULL
                                                                  BEGIN
                                                                  IF LEFT(@DBPivot,6) <> 'SELECT'
                                                                      BEGIN
                                                                      SET @Wink = 1
                                                                      SET @Work = CHARINDEX('|',(@DBPivot)+'|')
                                                                      WHILE @Work > 0
                                                                          BEGIN
                                                                          SET @Name = SUBSTRING(@DBPivot,@Wink,@Work-@Wink)
                                                                          INSERT #DBAT (Name) VALUES (@Name)
                                                                          SET @Wink = @Work + 1
                                                                          SET @Work = CHARINDEX('|',(@DBPivot)+'|',@Wink)
                                                                          END
                                                                      END
                                                                  ELSE
                                                                      BEGIN
                                                                      SET @Task = 'INSERT #DBAT (Name) ' + @DBPivot
                                                                      SET @Task = REPLACE(@Task,         CHAR(94),CHAR(39))
                                                                      SET @Task = REPLACE(@Task,CHAR(45)+CHAR(45),CHAR(32))
                                                                      SET @Task = REPLACE(@Task,CHAR(47)+CHAR(42),CHAR(32))
                                                                      IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
                                                                      IF @Status = 0 SET @Status = @Return
                                                                      END
                                                                  END
                                                              ELSE
                                                                  BEGIN
                                                                  SET @Task = '   INSERT #DBAT (Name)'
                                                                            + '   SELECT CONVERT(varchar(100),' + @DBField + ')'
                                                                            + '     FROM ' + @Bank + ' AS T ' + @DBWhere
                                                                            + ' GROUP BY CONVERT(varchar(100),' + @DBField + ')'
                                                                            + ' ORDER BY 1'
                                                                  IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
                                                                  IF @Status = 0 SET @Status = @Return
                                                                  END

                                                              UPDATE #DBAT SET Name = @Some WHERE Name IS NULL
                                                              SET @DBAI = ''
                                                              SET @DBAO = ''
                                                              SET @Rich = ''
                                                               DECLARE Fields CURSOR FAST_FORWARD FOR
                                                                SELECT C.name
                                                                  FROM tempdb.dbo.sysobjects AS O
                                                                  JOIN tempdb.dbo.syscolumns AS C
                                                                    ON C.id = O.id
                                                                   AND C.name != @DBField
                                                                   AND C.name != @PCField
                                                                   AND O.name  = @DBAE
                                                              ORDER BY C.colid
                                                              SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
                                                              OPEN Fields
                                                              SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
                                                              FETCH NEXT FROM Fields INTO @Same
                                                              SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
                                                              WHILE @@FETCH_STATUS = 0 AND @Status = 0
                                                                  BEGIN
                                                                  SET @DBAI = @DBAI + ', ' +  @Same
                                                                  FETCH NEXT FROM Fields INTO @Same
                                                                  SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
                                                                  END
                                                              CLOSE Fields DEALLOCATE Fields
                                                              SET @DBAI = SUBSTRING(@DBAI,3,4000)
                                                               DECLARE Fields CURSOR FAST_FORWARD FOR
                                                                SELECT  Name
                                                                  FROM #DBAT
                                                              ORDER BY  Work
                                                              OPEN Fields
                                                              FETCH NEXT FROM Fields INTO @Same
                                                              WHILE @@FETCH_STATUS = 0 AND @Status = 0
                                                                  BEGIN
                                                                  IF LEN(@DBAO) < 7900 - LEN(@DBField) - LEN(@PCField) - LEN(@Same) - LEN(@Same)
                                                                      BEGIN
                                                                      SET @DBAO = @DBAO + ', ' + @PCBuild + '(CASE WHEN ISNULL(CONVERT(varchar(100),' + @DBField + '),'
                                                                                        + CHAR(39) + @Some + CHAR(39) + ') = '
                                                                                        + CHAR(39) + @Same + CHAR(39) + ' THEN '
                                                                                        + @PCField + ' ELSE NULL END) AS '
                                                                                        + CHAR(91) + @Same + CHAR(93)
                                                                      END
                                                                      ELSE
                                                                      BEGIN
                                                                      SET @Status = 50000
                                                                      END
                                                                  FETCH NEXT FROM Fields INTO @Same
                                                                  END
                                                              CLOSE Fields DEALLOCATE Fields
                                                              IF @DBAdmin IN (1,3) SET @Rich = @Rich + ', ' + @PCBuild + '(' + @PCField + ') AS All_' + @PCBuild
                                                              IF @DBAdmin IN (2,3) SET @Rich = @Rich + ', COUNT('            + @PCField + ') AS All_COUNT'
                                                              IF @DBAdmin IN (2,3) SET @Rich = @Rich + ',   MIN('            + @PCField + ') AS All_MIN'
                                                              IF @DBAdmin IN (2,3) SET @Rich = @Rich + ',   MAX('            + @PCField + ') AS All_MAX'
                                                              SET ANSI_WARNINGS OFF
                                                              SET @Home = ''
                                                              SET @Name = ''
                                                              IF @DBTable IS NOT NULL
                                                                  BEGIN
                                                                  SET @Name = @DBTable
                                                                  IF LEFT(@Name,2) = '##'
                                                                      BEGIN
                                                                      IF @DBUltra = 0
                                                                          BEGIN
                                                                          SET @Task = 'IF EXISTS (SELECT * FROM   tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @Name + CHAR(39) + ') DROP TABLE ' +         @Name
                                                                          IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
                                                                          IF @Status = 0 SET @Status = @Return
                                                                          END
                                                                      END
                                                                  ELSE
                                                                      BEGIN
                                                                      IF @DBWrite IS NOT NULL SET @Home = @DBWrite + '.dbo.'
                                                                      IF @DBUltra = 0
                                                                          BEGIN
                                                                          SET @Task = 'IF EXISTS (SELECT * FROM ' + @Home + 'sysobjects WHERE name = ' + CHAR(39) + @Name + CHAR(39) + ') DROP TABLE ' + @Home + @Name
                                                                          IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
                                                                          IF @Status = 0 SET @Status = @Return
                                                                          END
                                                                      END
                                                                  END
                                                              IF @DBTable IS NOT NULL
                                                                  BEGIN
                                                                  IF @DBUltra = 0
                                                                      BEGIN
                                                                      IF @Status = 0 EXECUTE ( '   SELECT ' + @DBAI + @DBAO + @Rich
                                                                                             + '     INTO ' + @Home + @Name
                                                                                             + '     FROM ' + @Bank + ' AS T ' + @DBWhere
                                                                                             + ' GROUP BY ' + @DBAI
                                                                                             + ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR
                                                                      IF @Status = 0 SET @Status = @Return
                                                                      END
                                                                  ELSE
                                                                      BEGIN
                                                                      IF @Status = 0 EXECUTE ( '   INSERT ' + @Home + @Name
                                                                                             + '   SELECT ' + @DBAI + @DBAO + @Rich
                                                                                             + '     FROM ' + @Bank + ' AS T ' + @DBWhere
                                                                                             + ' GROUP BY ' + @DBAI
                                                                                             + ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR
                                                                      IF @Status = 0 SET @Status = @Return
                                                                      END
                                                                  END
                                                              ELSE
                                                                  BEGIN
                                                                  IF @Status = 0 EXECUTE ( '   SELECT ' + @DBAI + @DBAO + @Rich
                                                                                         + '     FROM ' + @Bank + ' AS T ' + @DBWhere
                                                                                         + ' GROUP BY ' + @DBAI
                                                                                         + ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR
                                                                  IF @Status = 0 SET @Status = @Return
                                                                  END
                                                              SET ANSI_WARNINGS ON
                                                              SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE
                                                              EXECUTE (@Task)
                                                              DROP TABLE #DBAT
                                                              SET NOCOUNT OFF
                                                              RETURN (@Status)
                                                              GO

                                                              SQL Query:

                                                              USE NetPerfMon EXECUTE sp_Crosstab
                                                              'SELECT
                                                              Nodes.Caption,
                                                              CustomPollers.UniqueName,
                                                              CustomPollerStatus.Status
                                                              FROM ((CustomPollerAssignment
                                                              LEFT JOIN CustomPollerStatus
                                                              ON CustomPollerAssignment.[CustomPollerAssignmentID] = CustomPollerStatus.[CustomPollerAssignmentID])
                                                              LEFT JOIN Nodes
                                                              ON CustomPollerAssignment.[NodeID] = Nodes.[NodeID])
                                                              LEFT JOIN CustomPollers
                                                              ON CustomPollerAssignment.[PollerID] = CustomPollers.[PollerID]
                                                              ',
                                                              NULL,
                                                              NULL,
                                                              'UniqueName',
                                                              'Status',
                                                              MAX

                                                              You could also just execute the SQL select if you aren't worried about the pivot table component- or just want to test the query results first.

                                                              Sav.

                                                              • Re: Custom Poller Inventory Report

                                                                 Maybe that object is not within that sysid you are searching under.  I use Nodes.Vendor column on a nodes join to grab cisco devices.  It's like your request but not exactly.  I use it for checking data not using sql query not orion reports

                                                                Example:

                                                                 
                                                                SELECT
                                                                 DISTINCT [NetPerfMon].[dbo].[Nodes].[Caption]
                                                                ,[NetPerfMon].[dbo].[Nodes].[Vendor]
                                                                ,[NetPerfMon].[dbo].[Nodes].[IP_Address]
                                                                ,[NetPerfMon].[dbo].[CustomPollerAssignment].[NodeID]
                                                                ,[NetPerfMon].[dbo].[CustomPollerAssignment].[AssignmentName]
                                                                ,[NetPerfMon].[dbo].[CustomPollerStatistics_Detail].[MinRate]
                                                                ,[NetPerfMon].[dbo].[CustomPollerStatistics_Detail].[AvgRate]
                                                                ,[NetPerfMon].[dbo].[CustomPollerStatistics_Detail].[MaxRate]
                                                                ,[NetPerfMon].[dbo].[CustomPollerStatistics_Detail].[Total]
                                                                ,[NetPerfMon].[dbo].[CustomPollerStatistics_Detail].[RawStatus]
                                                                ,[NetPerfMon].[dbo].[CustomPollerStatistics_Detail].[Status]
                                                                From [NetPerfMon].[dbo].[Nodes] JOIN [NetPerfMon].[dbo].[CustomPollerAssignment]
                                                                ON [NetPerfMon].[dbo].[CustomPollerAssignment].[NodeID] = [NetPerfMon].[dbo].[Nodes].[NodeID]
                                                                JOIN [NetPerfMon].[dbo].[CustomPollerStatistics_Detail]
                                                                ON [NetPerfMon].[dbo].[CustomPollerAssignment].[CustomPollerAssignmentID] = [NetPerfMon].[dbo].[CustomPollerStatistics_Detail].[CustomPollerAssignmentID]
                                                                Where [dbo].[Nodes].[Vendor] = 'Cisco'
                                                                order by [NetPerfMon].[dbo].[Nodes].[Caption] asc

                                                                • Re: Custom Poller Inventory Report


                                                                  Roger,

                                                                  The report works great for most things, what I've noticed however is that when I insert some custom pollers no details appear in the report, even though it's definetly being collected. One of the things that doesn't show up is CiscoFlashDeviceSize - EC378A32-8A13-4B0A-8B5F-2C4215CE707E, would you know why? Is it a formatting thing? All my custom pollers are setup for RAW value.

                                                                  thanks

                                                                  Dave



                                                                  I honestly can't see what is wrong !

                                                                  I have added CiscoFlashDeviceSize as a custom poller on my system and it works fine in my custom report.

                                                                  Sorry I can be of more help

                                                                  Rog 

                                                                   

                                                                   

                                        • Re: Custom Poller Inventory Report
                                          savell

                                          Of course if you are using SQL Server 2005, then you have access to the provided SQL PIVOT function rather than creating a stored proceedure to do this....


                                          Sav.