Thread Info
  • State Not Answered
  • Locked Locked
  • Replies 3 replies
  • Subscribers 1 subscriber
  • Views 5671 views
  • Users 0 members are here
Options
Suggested
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Need SQL query to find out-of-date machines

dharamsale

Hi All,

i need to genrate report for Out-of-date computers but this i can't do it from Sophos report and i come to know i need to write SQL query so i am not sure about sophos database tablses so please can any one share me SQL Query. 

:35633


This thread was automatically locked due to age.
Parents
  • 0

    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

    :35651
Reply
  • 0

    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

    :35651
Children
No Data