How many unmanaged computers are there attached to the Notification Server?

book

Article ID: 181529

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
What is the SQL query I can use to determine how many unmanaged computers there are?

Answer

If a computer becomes unmanaged on a lower Notification Server, and you are using Inventory Forwarding, then that computer will show as unmanaged on the parent Notification Server as well. You can use article 38550 to use a notification policy to delete those computers from the parent Notification Server. Sometimes computers are inadvertently set to unmanaged, so a Notification policy should be set up to alert you if too many computers are unmanaged and will be purged eventually.

Here is the SQL behind this policy:

SELECT [Number] = CASE  
WHEN (  
 select count(*) AS [Unmanaged]   
 from vresource where ismanaged=0  
 and resourcetypeguid = '493435F7-3B17-4C4C-B07F-C23E7AB7781F'  
) >40> THEN (select count(*) AS [Unmanaged]   
  from vresource where ismanaged=0  
  and resourcetypeguid = '493435F7-3B17-4C4C-B07F-C23E7AB7781F')  
ELSE NULL  
END

Note: This will return true if the total number of unmanaged machines is over 40. You should change this number based on how many nodes are in your environment and how many you expect to be retired on a daily basis.

Use this SQL for a report:

SELECT i.name as 'Computer Name', v.ismanaged, v.guid,
i.createddate as 'Created Date', i.modifieddate as 'Modified Date'
FROM vresource v
JOIN item i on i.guid = v.guid
where v.ismanaged=0  
AND v.resourcetypeguid = '493435F7-3B17-4C4C-B07F-C23E7AB7781F'
order by  i.modifieddate desc