Dead Client Reporting

Question

Long story short - I just inherited managing Sophos from another department in IT, so I have yet to go through my Sophos training (I just finished scheduling it, it just hasn't happened yet). I've been going through the help docs, etc.

What I'd like to do is groom the Sophos Enterprise Console. Right now there are a lot of clients in the console that aren't reporting in - I realize some of this is normal (laptops, shut-down, etc), but I also know that as computers have been retired and ewasted, they have not been removed from the console. Is there a way to groom by "last message received from computer" or some other field to weed out the computers that will never check-in again? Or is there some other way of doing this that you know.

Any help is appreciated!

This thread was automatically locked due to age.

QC · Accepted Answer

Too many questions for this time of day (9pm here). For today I'll just mention that you can sort by any column in any view of SEC by clicking on the column header. The name is in any view, Last Message Time on the computer details.
To a certain extent you can export what you see in SEC (I've never used it and don't have access right now but you can play with it). A little bit of experimenting should enable you to produce some reports for management ;-)

[Edit several hours later]:

You can Copy the entire contents of a view from SEC by clicking somewhere in the view (e.g. a client's name), then Ctrl-C (or choose Edit/Copy from the menu bar) and paste to a spreadsheet for example.

Another option is the Sophos Reporting Interface tool (available on the Downloads page) - please see the documentation.

The following q&d query (based on the one posted by Jak) might help you with identifying duplicates:

SELECT    c.Name,
   c.Description,
   c.DomainName,
   c.OperatingSystem,
   c.Managed,
   c.Deleted,
   c.Connected,
   c.insertedat,
   c.DNSName,
   dbo.IPAddressText(c.IPAddress) AS IPAddress,
   c.MessageSystemAddress,
   g.Name
FROM [SOPHOSxx].[dbo].[ComputersAndDeletedComputers] AS c
    INNER JOIN ComputerGroupMapping AS cgm ON cgm.ComputerID = c.ID
        INNER JOIN Groups AS g ON g.ID = cgm.GroupID
WHERE c.Name IN(
   SELECT c.Name
   FROM [SOPHOSxx].[dbo].[ComputersAndDeletedComputers] AS c
        WHERE Deleted = 0
   GROUP BY c.Name
   HAVING ( COUNT(c.Name) > 1)
    )
ORDER BY c.Name

Use the appropriate name for SOPHOSxx (e.g. SOPHOS45 for SEC 4.5)

This should give you all computer names which appear (i.e. can be seen) more than once in SEC. These might be different (i.e. valid) computers which happen to have identical names or duplicates created because SEC couldn't match If you omit the WHERE Deleted = 0 the result will also contain those which have been manually deleted (and some flagged as deleted by SEC when a "duplicate" was found - the logic has changed with the SEC versions).

HTH

Christian

ruckus · Answer

To extract a quick list of how many computers haven't reported for a while use:

sqlcmd -E -S .\sophos -d sophos50 -Q "select name from computersanddeletedcomputers where managed=1 and lastmessagetime<'MM/DD/YYYY'" > C:
onReportingComputerList.txt 
 Change MM/DD/YYYY for a reasonable date (e.g., 01/31/2012 for computers that have stopped reported from 31st January). Also change the 'sophos50' if you don't have Enterprise Console v.5.0. To find out what the database name is see article 17323 .

You can safely play with the date and get comfortable with the number of computers returned. Then, as suggested, use PurgeDB.exe with the -HistoryLengthInDays= option to clear dead endpoints out of the console. Any computers that you purge, that then do just happen to report in, will pop back into the console. :23313