Create a query

This example shows how to query the data source you just created for the information about changes to the Data Control policies over the past three months.

In the Choose Data Source dialog box, clear the Use the Query Wizard to create/edit queries check box.
Select the data source you created in the previous steps (in this example, SophosAuditing) and click OK.
The Microsoft Query dialog box displays Query from SophosAuditing with the default table, vAuditEventsAll, which you selected when you created the data source.
Do one of the following:
- Create a query in the design view.
  1. In the Microsoft Query dialog box, on the Criteria menu, click Add Criteria.
  2. In the Add Criteria dialog box, next to Field, select Timestamp. Ensure that the Operator field is blank. In the Value field, type:
    >=DATEADD(mm,-3,GETUTCDATE())
    Use the list separator specified in Region and Language settings in Control Panel. For example, if your list separator is a semicolon, use semicolons instead of commas in the statement above. You may receive the error message "Extra ')'" if you use an incorrect list separator.
    Click Add. The criterion is added to Query from SophosAuditing.
  3. In the Add Criteria dialog box, next to Field, select TargetType. In the Operator field, select equals. In the Value field, select or type Policy.
    Click Add. The criterion is added to Query from SophosAuditing.
  4. In the Add Criteria dialog box, next to Field, select TargetSubType. In the Operator field, select equals. In the Value field, select or type Data control.
    Click Add. The criterion is added to Query from SophosAuditing.
    In the Add Criteria dialog box, click Close.
  5. In the Microsoft Query dialog box, add fields from vAuditEventsAll to the query by double-clicking on them. Alternatively, you can add a field to the query by dragging it from the table to the display area.
- Create a query in the SQL view.
  1. In Microsoft Query, click the SQL button and type your SQL statement, for example:
```
SELECT EventId, Timestamp, UserName, HostIPAddress, Action, TargetName, ParameterType, ParameterValue, Result
                        
FROM SophosSecurity.Reports.vAuditEventsAll 
                     
WHERE (Timestamp>=DATEADD(mm,-3,GETUTCDATE())) 
AND (TargetType='Policy') 
AND (TargetSubType='Data control')
                      
ORDER BY EventId ASC
                    
```
    Click OK.
To save the query, on the File menu, click Save.

Create a query

Machine translation disclaimer