cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Report for unmanaged nodes

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?

Labels (1)
0 Kudos
5 Replies
Level 12

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'

0 Kudos

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

0 Kudos

This doesn't work for me on the Web Reports.  I'm getting a query error.  It states the query is invalid.  Would you all be able to determine if there is an issue with the script when trying to run it through the Web Reports? 

0 Kudos
Level 13

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'

0 Kudos

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.

- Marc Netterfield, Github
0 Kudos