I have my Notification Server settings configured so computers that have not been updated in the past 'N' months are removed from my database and no longer using up my licenses. This purge doesn't work quite right though. I have computers who have never done a Last Configuration Request, Last Inventory Received and/or Last Event Received that I wanted purged as well. How can I include these computers who have never sent a configuration request or inventory in the purge?
They can't be included in the purge, because there is no Last Request/Received date for the purge to compare the date to (if there's no date, it's not considered older than N months). It is possible to remove them however by creating a collection and deleting the computers yourself.
This collection will need to be created in two parts, the first collection includes every computer in your database as a reference point for the second collection to get what you're actually looking for. For this example I'll call the reference (Complete computer list) collection 'Full Computers List', and the final collection I'll call 'Never Updated'.
First, create the 'Full Comptuers List' collection. To do this, go to the Resources tab, under Collections (you can put this collection anywhere you want under Collections, I'll put it in the My Collections subfolder I created) go to My Collections (or wherever you chose to place it), right-click the folder and select New -> Collection. On the New Collection page, name your collection (for this example I put Full Computers List), expand the Query section, and select Enter SQL Directly. In the SQL Query box put the following query:
select distinct vc.guid from Evt_NS_Client_Config_Request ccr
Join vcomputer vc on vc.guid = ccr.[ResourceGuid]
Apply the changes. Now create the second collection. Again, create a new collection like you did in the first step, but in the SQL query put the following:
select Guid from vResource where Guid in
select Guid from vComputerResource
Now before you apply the changes, expand the Explicit Inclusions and Exclusions, and click in the Exclusions section on Select a collection. Browse to the Full Comptuers List collection, check it, click Apply to save the Collection Selector (it should have changed the Exclusions to say Full Computers List) then click Apply to save this Never Updated collection.
The Never Updated collection will now show you an entire list of computers who do not have a Last Configuration Request. You can now go through this list and delete the unwanted computers.
WARNING: If you attempt to delete too many at a time you will get a timeout error. It's suggested you start by deleting 10, and then work your way up until you find a comfortable number to delete.
NOTE: This method works best on smaller amounts. It would take too long to use this method to remove thousands of entries. If you have more than you're comfortable manually removing once you have the collection, you may wish to find another method of removing the computers.
NOTE: This will identify only those computers who have never requested a configuration (Last Configuration Request will not have any date, or two dashes - -), if you want it to include also ones who's Last Inventory Received and Last Event Received is blank you will need to change the SQL query in the first collection to include those fields also.