Question What is the SQL query I can use to determine how many unmanaged computers there are?
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
Imported Document ID: HOWTO8602
Subscribing will provide email updates when this Article is updated. Login is required.