Query Install Errors & Update Errors

HI,

Interms of a SQL query to get machines that have an outstanding reboot required message the following should get you going:

SELECT distinct c.Name FROM Errors as e with (nolock)
inner join ComputersAndDeletedComputers as c with (nolock)
on c.ID=e.ComputerID
where 
e.Source = 'ALC' 
and e.number = 109
and e.Outstanding = 1

 As Reboot Required has a code in SEC as 6D, which is 109 decimal.  

As for those machines with install errors.  There is a filter in SEC called "Computers with installation errors".  This eventually calls the SQL function: 'ComputerListWithInstallErrors]', you can look at the SQL in that and the conditions really boil down to those below:

SELECT distinct c.Name FROM ComputersAndDeletedComputers as c with (nolock)
		     WHERE c.InstallFailureReason <> 0
               AND c.InstallFailureReason <> -2

As for Updating errors specifically. You can use the first SQL query with a minor change... 71 and 6b are the common errors so... 71 = 113 dec and 6b = 107, making the query:

SELECT distinct c.Name FROM Errors as e with (nolock)
inner join ComputersAndDeletedComputers as c with (nolock)
on c.ID=e.ComputerID
where 
e.Source = 'ALC' 
and (e.number = 113 or e.number=107)
and e.Outstanding = 1

 Hope these get you going.

It's worth mentioning that you can do a Ctrl-C/Ctrl-V on the computer list view to get a report if what you see is in view.

Regards,

Jak

Awesome jak!  Much appreciated.

Is there a table (or some reference) that indicate what the errors.Source values can be and what they mean?  Also, the errors .number relative to the errors .Source?

I do use the console but for quick reports, I find that its' a bit clumsy.  I'm a programmer, by trade/experience, and have my own web-based reporting system that I'm setting up to quickly show these values and dump them to excel, if needed.

Thanks again!

Hi,

No problem! There isn't a lookup table, the easiest thing might be to just add to the lookup table you construct as and when new errors come in.  I.e. Copy the string as seen in SEC and convert the hex to decimal for the DB lookup.

Each component managed it's own errors, I know that interms of "source" to "component":

Source/Component

SAV=SAV

ALC=AutoUpdate
SCF=Firewall
SDDM=SUM
SDDMAlert=SUM alerts 

I assume you're aware of the reporting interface?  Might be worth installing that if it makes a few queries simpler, plus they're more likely to continue working in the future.  The only "lookup" table is the Enumerations table as used by the reporting interface.  This is now installed by default it seems with the SOPHOS50 (SEC 5) database.

Regards,

Jak