Way to remove orphaned machines from SGN directory?

Hi Dimitri,

thank you very much for bringing this one up.

There is no way to remove machines from the Management Center via SQL; however, you could use the API to remove a machien from the MC.

This is the easy part... unfortunately there is no time stamp in 5.508 that would show you the last connection date of a client - this is planned to be available in 5.60. The only indication that you could use would be the last certificate received but that would only make sense if the last global change was less than 6 month. You could then mabye remove all machines whoms last policy received date is older than 6 month  - again all depending on your environment.

The last policy received date is included in KBA 109925 (http://www.sophos.com/support/knowledgebase/article/109925.html)

Regards

Dan

I found a slightly different way to detect orphaned / inactive machines, but it takes some time.

At your Root / Domain level, right click and choose "Request Inventory Refresh".  This will update the "Refresh Requested" column of every device in inventory with today's date.  The next time that device checks in, however, the date disappears from that field.  This means that if you wait a week or two (or a month depending) any device in your Inventory list that still shows the date in Refresh Requested hasn't checked in since and you can assume them inactive and purge them yourself.

It's a dirty, manual way to do it but it works

After I do a refresh request to all computers after 90 days the if the PCs haven't refreshed I use a SQL query to remove all PCs that still have "Refresh Requested" value.  Not sure if this is recommended to do but it's been working for me for the past year and a half.  This is what my sql query looks like.

delete from ivt_machines
--select * from ivt_machines
where exists (select safe_guard_dir.sgd_name from safe_guard_dir
                        where safe_guard_dir.sgd_id = ivt_machines.ima_machine_id
                              and ivt_machines.ima_refresh_requested > 0
                              and safe_guard_dir.sgd_name not like '%-%')

You can put the "--" in front of the first line and delete from the second if you just want to see the number of PCs it will delete.  Run the query as shown to do the actual deletion.

Also, i wanted to point out that we have department loaner laptops that don't normally talk to the network that often and we use a naming convention of "computername-dept" which is why we place that last line to disregard any computers that have a "-" in the name.

Hope this helps.

Hi Rex,

with regards to your procedure I have to tell you that you should NOT perform any actions against the SafeGuard DB using pure SQL! SafeGuard has some internal mechanisms that could become corrupted when altering the DB.

I would recommend to alter the query into a select statement and to use this output in combination with API to delete the machines properly.

Besides this a last connection date is reported into the DB as of version 5.60

Regards

Dan