4 Replies Latest reply on May 9, 2018 7:57 AM by j sachs

    Report for unmanaged nodes

    johnlad

      Ok hopefully someone can give me some direction, I am wanting to run a report to display all unmanaged and muted nodes and interfaces, I also need to see who they were unamanged by, wasn't sure if this can be created on the reports page or if I need to run report writer?

        • Re: Report for unmanaged nodes
          mesverrum

          Type the name of your question into the search box and I think you'll find at least a dozen threads with solutions.  I usually use a custom swql  query resource for this one.

          • Re: Report for unmanaged nodes
            jamesatloop1

            Crack open a new custom table report and select custom sql as datasource. Enter the following and enter your database name instead of 'mydatabase'.

             

             

            SELECT TOP (100000) [NodeID]

                  ,[ObjectSubType]

                  ,[IP_Address]

                  ,[IP_Address_Type]

                  ,[DynamicIP]

                  ,[UnManaged]

                  ,[UnManageFrom]

                  ,[UnManageUntil]

                  ,[Caption]

                  ,[DNS]

                  ,[Community]

                  ,[RWCommunity]

                  ,[SysName]

                  ,[Vendor]

                  ,[SysObjectID]

                  ,[Description]

                  ,[Location]

                  ,[Contact]

                  ,[RediscoveryInterval]

                  ,[PollInterval]

                  ,[VendorIcon]

                  ,[IOSImage]

                  ,[IOSVersion]

                  ,[GroupStatus]

                  ,[StatusDescription]

                  ,[Status]

                  ,[StatusLED]

                  ,[ChildStatus]

                  ,[EngineID]

                  ,[MachineType]

                  ,[IsServer]

                  ,[Severity]

                  ,[StatCollection]

                  ,[Allow64BitCounters]

                  ,[SNMPV2Only]

                  ,[AgentPort]

                  ,[SNMPVersion]

                  ,[TotalMemory]

                  ,[External]

                  ,[EntityType]

                  ,[CMTS]

                  ,[BlockUntil]

                  ,[IPAddressGUID]

                  ,[CustomStatus]

                  ,[Category]

                  ,[CustomCategory]

                  ,[EffectiveCategory]

              FROM ['mydatabase'].[dbo].[NodesData] where status like '8'

            • Re: Report for unmanaged nodes
              j sachs

              I created this query and transferred into Report Writer.  On the home page, I added a frame for 'Report from Report Writer' and selected the report I created.   This will show all currently unmanaged nodes.

               

              The facility number is just a custom property so you can remove or add your own.

               

              SELECT  ni.[NodeID]

                    ,ni.[StatusLED]

                    ,ni.[Caption]

                    ,ni.[IP_Address]

                    ,ni.[Vendor]

                    ,ni.[MachineType]

                    ,ncp.[FacilityNumber]

               

                FROM [SolarWindsOrion].[dbo].[NodesData] ni

                Inner Join [SolarWindsOrion].[dbo].[NodesCustomProperties] ncp ON ncp.NodeID = ni.NodeID

               

              Where

              UnManageFrom <> '1899-12-30 00:00:00.000'  AND

              UnManaged = '1'

                • Re: Report for unmanaged nodes
                  j sachs

                  Sorry I didn't read your entire post.   In adding the AuditingEvents table, I was able to see the user account responsible for unmanaging the node.  

                   

                  SELECT TOP 1000 ni.[NodeID]

                        ,ni.[StatusLED]

                        ,ni.[Caption]

                        ,ni.[IP_Address]

                        ,ni.[Vendor]

                        ,ni.[MachineType]

                        ,ncp.[FacilityNumber]

                       ,ae.[AccountID]

                       ,ae.[TimeLoggedUtc]

                   

                   

                    FROM [SolarWindsOrion].[dbo].[NodesData] ni

                    Inner Join [SolarWindsOrion].[dbo].[NodesCustomProperties] ncp ON ncp.NodeID = ni.NodeID

                    Inner Join [SolarWindsOrion].[dbo].[AuditingEvents] ae on ae.NetworkNode = ni.nodeID

                   

                  Where

                   

                  UnManageFrom <> '1899-12-30 00:00:00.000'  AND

                  UnManaged = '1' AND

                  ae.ActionTypeID = '28'

                   

                  Order by ae.TimeLoggedUtc DESC