SQL REQUEST

Hello Gill Affoum,

while my French is good enough to read your post it isn't when it comes to replying, I hope English is ok.

Please see Jak's reply in this Sophos Reporting thread. Haven't tested if it still works, I assume it does - if not it should give you nevertheless enough information to come up with a solution.

Christian

I have to create a script for a tool that get information directly from SQL database. The different section for this report is :

Name, OperatingSystem, SAVVersion, LastScanDateTime, LastScanName, UpToDate

With the following script, i can get all informaion execpt "UpToDate"

Could someone please help me to get this information from the DB. I think i must join several table to get this info but i don't know which ones.

the actuel script without the section "UpToDate"

select distinct Name, OperatingSystem =

 CASE

  When OperatingSystem = '9' then 'Windows 2003'

  When OperatingSystem = '34' then 'Windows 2008 R2'

  When OperatingSystem = '18' then 'Windows 2008'

  When OperatingSystem = '36' then 'Windows 2012'

  When OperatingSystem = '39' then 'Windows 2012 R2'

  When OperatingSystem = '41' then 'Windows 2016'

end,

SAVVersion,DomainName, convert(varchar,LastScanDateTime,103) LastScanDateTime, LastScanName

from

  [dbo].[Computers] c,[Sophos Reporting Interface].[vPolicyComplianceData] vp

where

  vp.ComputerID = c.ID

How can can I get by script the same resultat of the "UpTodate" section in the console

Hi Gill Affoum 

You can reach out to our professional services team to help you with this. Please check this link for the details. 

Hello Gill Affoum,

remembered that I have outlined the required logic here.
A q&d snippet that implements it. It's probably a little bit paranoid, hope it helps:
DECLARE @timediff INT = DATEDIFF(hh,GETUTCDATE(),GETDATE())
Select c.Name, p.Expired, p.ExpiryTime,
  UpToDate =
  CASE
     WHEN p.Expired = 0 and p.ExpiryTime IS NOT NULL THEN 'yes'
     WHEN p.Expired = 1 AND ISNULL(p.ExpiryTime,0) > dateadd( hh, -1, GETUTCDATE() ) THEN 'yes'
       WHEN p.Expired = 1 AND p.ExpiryTime IS NOT NULL AND ISDATE(p.ExpiryTime) = 1 THEN CONVERT(varchar,dateadd(hh,1+@timediff,p.Expirytime),120)
     ELSE 'Unknown'
  END
       FROM [Sophos Reporting Interface].[vComputerHostData] AS c
       LEFT OUTER JOIN [Sophos Reporting Interface].[vComputerPackageMapping] pm ON pm.ComputerID = c.ComputerID
       LEFT OUTER JOIN [Sophos Reporting Interface].[vPackageData] p ON p.PackageID = pm.PackageID

Christian

Hello Gill Affoum,

remembered that I have outlined the required logic here.
A q&d snippet that implements it. It's probably a little bit paranoid, hope it helps:
DECLARE @timediff INT = DATEDIFF(hh,GETUTCDATE(),GETDATE())
Select c.Name, p.Expired, p.ExpiryTime,
  UpToDate =
  CASE
     WHEN p.Expired = 0 and p.ExpiryTime IS NOT NULL THEN 'yes'
     WHEN p.Expired = 1 AND ISNULL(p.ExpiryTime,0) > dateadd( hh, -1, GETUTCDATE() ) THEN 'yes'
       WHEN p.Expired = 1 AND p.ExpiryTime IS NOT NULL AND ISDATE(p.ExpiryTime) = 1 THEN CONVERT(varchar,dateadd(hh,1+@timediff,p.Expirytime),120)
     ELSE 'Unknown'
  END
       FROM [Sophos Reporting Interface].[vComputerHostData] AS c
       LEFT OUTER JOIN [Sophos Reporting Interface].[vComputerPackageMapping] pm ON pm.ComputerID = c.ComputerID
       LEFT OUTER JOIN [Sophos Reporting Interface].[vPackageData] p ON p.PackageID = pm.PackageID

Christian