More examples of queries

Example 1: Which policies a certain person changed over the past 60 days

SELECT EventId, Timestamp, TargetSubType, Action, TargetName, ParameterType, ParameterValue, Result

FROM SophosSecurity.Reports.vAuditEventsAll

WHERE (Timestamp>=DATEADD(dd,-60,GETUTCDATE())) 
AND (TargetType='Policy') 
AND (UserName='GS22K8R264\Administrator')

ORDER BY Timestamp DESC
Note In a statement, instead of listing the fields you want to include in the report, you can type "SELECT *" to select all fields in the database view.

Example 2: Which policies were applied to a certain group in the past six months

SELECT *

FROM SophosSecurity.Reports.vAuditEventsAll

WHERE (Timestamp>=DATEADD(mm,-6,GETUTCDATE()))
AND (TargetType='Policy') 
AND (Action='Assign') 
AND (ParameterType='Group') 
AND (ParameterValue='\Oxford\UK-Servers')

ORDER BY EventId DESC
Note If the group for which you are creating a report is a subgroup of another group, you will need to either type the full path to the group or use the "ends with" statement (provided the name of the group is unique). For example, to create a report for the group \Oxford\UK-Servers, you can type either of the following:
  • ParameterValue='\Oxford\UK-Servers'
  • ParameterValue Like '%UK-Servers'

Example 3: What group changes were made by a certain person over the past three months

The following statement will result in a report showing what groups were created, deleted, moved or renamed and what computers were assigned to groups by the user in the past three months. 

SELECT *

FROM SophosSecurity.Reports.vAuditEventsAll

WHERE (Timestamp>=DATEADD(mm,-3,GETUTCDATE()))
AND (UserName='GS22K8R264\Administrator')
AND ((TargetType='Group') OR ((TargetType='Computer') AND (Action='Assign')))

Example 4: What changes were made to a certain group over the past three months

SELECT *

FROM SophosSecurity.Reports.vAuditEventsAll

WHERE (Timestamp>=DATEADD(mm,-3,GETUTCDATE()))
AND (ParameterValue='\Oxford\UK-Desktops')