Sophos SQL\ DB Query - Sophos EC 51

HI,

Not sure if you need to parse the XML in teh SQL but this will give you the info you need.

select p.PolicyXML, gp.PathAndName from [dbo].Policies as p 
inner join [dbo].[GroupPolicyMapping] as pgm 
inner join GroupPathAndNameTable() as gp on gp.GroupID = pgm.GroupID
on pgm.PolicyID = p.ID
where p.type = 7
and 
gp.PathAndName =N'Servers\g1\g2'

This will give you XML which will have the sections such as:

<ac:applicationSet behaviour="allow">
    <ac:application value="CompuTrace" />
    <ac:application value="LogMeIn" />
    <ac:application value="RealVNC" />
    <ac:application value="Remote Desktop Connection (V7 and higher)" />
    <ac:application value="Remote Desktop Connection 6.0" />
    <ac:application value="TeamViewer" />
    <ac:application value="TeamViewer Quick Support" />
    <ac:application value="TightVNC" />
    <ac:application value="UltraVNC" />
  </ac:applicationSet>

Regards,

Jak

Hello,

the XML is the obvious place, Jak - but as far as I can see it lists only individually authorized applications (perhaps also the added new ones for All added by Sophos in the future) but it does not list the applications contained in an Authorized Application Type (class). Indeed it seems to list only blocked classes and authorized applications.

Christian

Hi jak,

thx for the quick reply.

It works for my purposes and is what i asked for.

Can you please explain about "where p.type= 7" ?

Where does 7 come from?

Many thanks

Hello AK,

policies have different types (Updating, AV and so on) and type 7 is an Application Control policy.

Christian

Hi Christian,

Thx for the info.

Is this documented somewhere?

Kind Regards

AK

HI,

It's in the "Enumerations" table.  With SEC 5.0+ it's installed by default, prior to that installing the Sophos Reporting Interface will add it:

So for this one:

SELECT * FROM [dbo].[Enumerations] where EnumID = 17

Will get you what you want.  If you're now wondering about 17, I just had a scan through the table :)

Regards,

Jak

Hello AK,

Is this documented somewhere?

more or less :smileyhappy:

SELECT * 
  FROM [Sophos Reporting Interface].[vPolicyComplianceData] 
   WHERE ComputerID=1

Christian

Thx a bunch Christain.