Need SQL query to find out-of-date machines

Hello dharamsale,

please take a look at Re: Sophos Reporting.

Note that the Reporting Interface is already installed with SEC 5.1.

HTH

Christian

Hi Christian,

how do i use reporting service, for this do i need to install some think or any tool required. 

i want to genrate daily following reports 

1. machines differ from policy 

2. unmanaged computers list  

3. manage computer list 

if you provide query for this it will be very grate 

Hello dharamsale,

guess reporting managed/unmanaged  computers this way doesn't make much sense - computers usually don't get unmanaged by themselves. If you synchronize with AD to detect new computers you can set up email alerts from the console.

What will you do with the (daily) reports? I'm asking because usually one would try to resolve any issues found (especially of you don't just collect numbers but identify the clients) and as you'd then use SEC which provides the appropriate views (managed/unmanaged, non-compliant) I wonder what you could need the additional reports for.   

As said, SEC 5.1 already has the Reporting Interface views in the database. You don't need and extra tools to use them. An example would be:

SELECT c.Name 
      ,p.PolicyTypeName
      ,p.ComplianceName
  FROM [SOPHOS51].[Sophos Reporting Interface].[vPolicyComplianceData] AS p 
       INNER JOIN [SOPHOS51].[dbo].[Computers] AS c ON  c.ID = p.ComputerID 
     WHERE p.ComplianceID IN (8,6) 
      ORDER BY c.name

 which lists computer and their policies in the state "Differs from policy" or "Awaiting policy transfer"

Christian