How to create reports in SafeGuard Enterprise

  • N.º del artículo: 109925
  • Actualizado: 22 may 2014


Issue
How to create a report in SafeGuard Enterprise

Known to apply to the following Sophos product(s) and version(s)


SafeGuard Management Center

Operating systems
All supported operating systems

What To Do

Currently, SafeGuard Enterprise offers only limited integrated reporting functionality. However, it is possible to gather information such as the name and number of encrypted computers, and other information, by using SQL commands.

It is planned to have full reporting functionality in an upcoming release.

Below is a selection of reports that can be obtained using the currently available SQL commands required to retrieve information. Please note that these require the clients to connect to the SafeGuard Enterprise back-end. These SQL commands cannot be used for a standalone installation.

Report 1. A list of all encrypted Clients and the last time that a policy was received

use SafeGuard

SELECT
SAFE_GUARD_DIR.SGD_name as 'Machine name',
IVT_MACHINES.IMA_ENCRYPTED_DRIVES as 'Drive encrypted',
IVT_MACHINES.IMA_UNENCRYPTED_DRIVES as 'Drive not encrypted',
IVT_MACHINES.IMA_LAST_POLICY_RECEIVED as 'Last policy received on'

FROM IVT_MACHINES INNER JOIN
SAFE_GUARD_DIR ON IVT_MACHINES.IMA_MACHINE_ID = SAFE_GUARD_DIR.SGD_ID
WHERE (IVT_MACHINES.IMA_ENCRYPTED_DRIVES IS NOT NULL)


Report 2. A list showing which users are assigned to which computer

use SafeGuard

SELECT USR_ID, USR_LOGON_NAME, USR_FIRST_NAME, USR_LAST_NAME, USR_EMAIL, machines.*

FROM
(SELECT SGD_NAME,SGD_ID,UMA_USER_ID,SGD_DSN,SGD_SCHEMA_CLASS_NAME
FROM Safe_Guard_DIR INNER JOIN
USR_MACHINE_ASSIGN ON Safe_Guard_DIR.SGD_ID = USR_MACHINE_ASSIGN.UMA_MACHINE_ID) AS MACHINES INNER JOIN USERS ON USERS.USR_ID = MACHINES.UMA_USER_ID


Report 3. A list of all installed features

This is a general script that can be used for several tasks. Please remove comments in order to receive the required information

/* Selecting all installed features on all available machines*/

use SafeGuard
SELECT
SAFE_GUARD_DIR.SGD_name as 'Machine name',
IVT_INST_FEATURES.IIF_FEATURE as 'Feature Installed',
convert (VARCHAR,IVT_INST_FEATURES.IIF_MODIFY_DATE,3) as 'Last modified'
FROM IVT_INST_FEATURES INNER JOIN
SAFE_GUARD_DIR ON IVT_INST_FEATURES.IIF_MACHINE_ID = SAFE_GUARD_DIR.SGD_ID
order by SAFE_GUARD_DIR.SGD_name


/* Selecting all machines that have SafeGuard Enterprise Device Encryption installed */

use SafeGuard
SELECT
SAFE_GUARD_DIR.SGD_name as 'Machine name',
IVT_INST_FEATURES.IIF_FEATURE as 'Feature Installed',
convert (VARCHAR,IVT_INST_FEATURES.IIF_MODIFY_DATE,3) as 'Last modified'
FROM IVT_INST_FEATURES INNER JOIN
SAFE_GUARD_DIR ON IVT_INST_FEATURES.IIF_MACHINE_ID = SAFE_GUARD_DIR.SGD_ID where IVT_INST_FEATURES.IIF_FEATURE like 'Device%'
order by SAFE_GUARD_DIR.SGD_name


/* Selecting all machines that have SafeGuard Enterprise Data Exchange installed */

use SafeGuard
SELECT
SAFE_GUARD_DIR.SGD_name as 'Machine name',
IVT_INST_FEATURES.IIF_FEATURE as 'Feature Installed',
convert (VARCHAR,IVT_INST_FEATURES.IIF_MODIFY_DATE,3) as 'Last modified'
FROM IVT_INST_FEATURES INNER JOIN
SAFE_GUARD_DIR ON IVT_INST_FEATURES.IIF_MACHINE_ID = SAFE_GUARD_DIR.SGD_ID where IVT_INST_FEATURES.IIF_FEATURE like 'Data%'
order by SAFE_GUARD_DIR.SGD_name


/* Selecting all machines that have SafeGuard Enterprise Configuration Protection installed */

use SafeGuard
SELECT
SAFE_GUARD_DIR.SGD_name as 'Machine name',
IVT_INST_FEATURES.IIF_FEATURE as 'Feature Installed',
convert (VARCHAR,IVT_INST_FEATURES.IIF_MODIFY_DATE,3) as 'Last modified'
FROM IVT_INST_FEATURES INNER JOIN
SAFE_GUARD_DIR ON IVT_INST_FEATURES.IIF_MACHINE_ID = SAFE_GUARD_DIR.SGD_ID
where IVT_INST_FEATURES.IIF_FEATURE like 'Configuration%'
order by SAFE_GUARD_DIR.SGD_name

/* List all machines that use a Management Center license */

use safeguard
SELECT
SAFE_GUARD_DIR.SGD_name as 'Machine name'
FROM IVT_MACHINES INNER JOIN
SAFE_GUARD_DIR ON IMA_MACHINE_ID = SAFE_GUARD_DIR.SGD_ID


Report 4. A list of all computers together with drive status

use SafeGuard

SELECT
SAFE_GUARD_DIR.SGD_name as 'Machine name',
IVT_MACHINES.IMA_ENCRYPTED_DRIVES as 'Drive enc.',
IVT_MACHINES.IMA_UNENCRYPTED_DRIVES as 'Drive not enc.'

FROM IVT_MACHINES INNER JOIN
SAFE_GUARD_DIR ON IVT_MACHINES.IMA_MACHINE_ID = SAFE_GUARD_DIR.SGD_ID


Report 5. A list of all Security Officers as seen in the Management Center

use SafeGuard
SELECT
dbo.SEC_OFFICER.SOF_ENABLED AS Enabled,
dbo.SEC_OFFICER.SOF_SHORT_NAME AS Name,
dbo.SEC_OFFICER.SOF_DN AS Description,
dbo.SEC_OFFICER.SOF_CP_NUMBER AS [Cell Phone],
dbo.SEC_OFFICER.SOF_EMAIL AS Email,
dbo.SEC_OFFICER.SOF_VALID_FROM AS [Validity From],
dbo.SEC_OFFICER.SOF_VALID_UNTIL AS [Validity To],
dbo.SEC_OFFICER.SOF_TOKEN_LOGON AS [Token Logon],
dbo.SEC_OFFICER.SOF_CERTIFICATE AS Certificate,
dbo.SGN_ROLES.SGR_NAME AS Role,
dbo.SEC_OFFICER.SOF_CREATION_DATE AS [Creation Date],
dbo.SEC_OFFICER.SOF_CREATED_BY AS [Created By],
dbo.SEC_OFFICER.SOF_MODIFY_DATE AS [Modification Date],
dbo.SEC_OFFICER.SOF_MODIFIED_BY AS [Modified By]
FROM         dbo.SEC_OFFICER INNER JOIN
dbo.SEC_OFF_ROLES ON dbo.SEC_OFFICER.SOF_ID = dbo.SEC_OFF_ROLES.SOR_SOF_ID INNER JOIN
dbo.SGN_ROLES ON dbo.SEC_OFF_ROLES.SOR_ROLE_ID = dbo.SGN_ROLES.SGR_ID
ORDER BY Name


Report 6. A list of all Security Officers, their role, and their Users and Computers objects access

USE SafeGuard
SELECT
dbo.SEC_OFFICER.SOF_SHORT_NAME AS 'Name',
dbo.SGN_ROLES.SGR_NAME AS 'Role',
CASE
WHEN dbo.SEC_OFF_SGD.SOS_RIGHTS = 2 THEN 'Read Only'
WHEN dbo.SEC_OFF_SGD.SOS_RIGHTS = 3 THEN 'Full Access'
WHEN dbo.SEC_OFF_SGD.SOS_RIGHTS = 0 THEN 'Access Denied'
END AS 'Permission',
dbo.SAFE_GUARD_DIR.SGD_NAME AS 'Object Name',
dbo.SAFE_GUARD_DIR.SGD_DSN AS 'Object DSN',
dbo.SAFE_GUARD_DIR.SGD_SCHEMA_CLASS_NAME AS 'Object Type'
FROM SEC_OFFICER
JOIN dbo.SEC_OFF_SGD ON dbo.SEC_OFFICER.SOF_ID = dbo.SEC_OFF_SGD.SOS_SOF_ID
JOIN dbo.SAFE_GUARD_DIR ON dbo.SAFE_GUARD_DIR.SGD_ID = dbo.SEC_OFF_SGD.SOS_SGD_ID
JOIN dbo.SEC_OFF_ROLES ON dbo.SEC_OFFICER.SOF_ID = dbo.SEC_OFF_ROLES.SOR_SOF_ID
JOIN dbo.SGN_ROLES ON dbo.SGN_ROLES.SGR_ID = dbo.SEC_OFF_ROLES.SOR_ROLE_ID
ORDER BY 'Name', 'Permission'


Report 7. A list of where all policies have been applied in the Management Center

USE SafeGuard
SELECT
POLICY.POL_NAME as 'Policy Name',
SAFE_GUARD_DIR.SGD_NAME as 'Object Name',
SAFE_GUARD_DIR.SGD_SCHEMA_CLASS_NAME as 'Object Class',
SAFE_GUARD_DIR.SGD_DSN as 'Object Path'
FROM OU_POLICY_ASSIGN
INNER JOIN POLICY ON OU_POLICY_ASSIGN.OAS_POL_ID = POLICY.POL_ID
INNER JOIN SAFE_GUARD_DIR ON OU_POLICY_ASSIGN.OAS_SGD_ID = SAFE_GUARD_DIR.SGD_ID
ORDER BY SAFE_GUARD_DIR.SGD_DSN

 

Report 8. Create a list of all Users in the SafeGuard Database that have a certificate assigned but no User Machine Assignment

USE SafeGuard

SELECT SGD_ID,
SGD_NAME,
SGD_DSN,
SGD_CREATION_DATE,
SGD_MODIFY_DATE
FROM dbo.SAFE_GUARD_DIR SGD INNER JOIN
dbo.USERS ON SGD_ID = dbo.USERS.USR_ID INNER JOIN
dbo.CERTIFICATES_ASSIGN CA ON SGD_ID = CA.CAS_SGD_ID INNER JOIN
dbo.CERTIFICATES C ON CA.CAS_CRT_ID = C.CRT_ID
WHERE NOT EXISTS (SELECT * FROM [USR_MACHINE_ASSIGN] UMA WHERE SGD.SGD_ID = UMA.UMA_USER_ID)AND SGD_SCHEMA_CLASS_NAME like 'user' OR SGD_SCHEMA_CLASS_NAME like 'localuser'


 

 
Si necesita más ayuda, póngase en contacto con soporte técnico.

Valore el artículo

Muy malo Excelente

Comentarios