Job Auditing and SQL Queries

SkySync supports an Auditing feature that allows for full reporting of all writes, transfers and deletions performed by jobs. 

Because of the increased data collected and the size needed in the SkySync tracking database to record all of the audit data, the Auditing feature requires SkySync to use SQL Server or SQL Server Express. See the link below for a guide on configuring SkySync to use SQL Server

Audits can be viewed in the Job History Tab. To access the Job History from the Job Control screen, right-click on the job and select Job History, or highlight the job and select Job History from the pop up menu. Select the "Audits" tab in the lower section of the Execution History window:

 

 

If email notifications are configured, the job summary emailed will have a .csv attachment containing all of the audit data for that job run. 

Additionally, auditing data can be accessed with a SQL query or via the SkySync Web Service. The queries are listed below: 

 

TSQL (SQL Server) query: 

DECLARE @pageindex as Int = 0; 

DECLARE @pageSize as Int = 1000; 

DECLARE @offset as Int = @pageindex * @pageSize; 

WITH CTE AS ( 

       SELECT ID, JobID, ExecutionID, IsContainer, IsDirectionSwitched, ConnectionID, Path, DesiredName, Size, Version, SourceConnectionID, SourcePath, OperationType, OperationOnTicks, Reason, ROW_NUMBER() 

       OVER (ORDER BY OperationOnTicks DESC) r FROM TransferAudits 

) SELECT * FROM CTE WHERE r > @offset AND r <= (@offset + @pageSize); 

 

Omit CTE to query all data: 

SELECT ID, JobID, ExecutionID, IsContainer, IsDirectionSwitched, ConnectionID, Path, DesiredName, Size, Version, SourceConnectionID, SourcePath, OperationType, OperationOnTicks, Reason 

FROM TransferAudits 

ORDER BY OperationOnTicks DESC 

 

Links to retrieve/export the Auditing Data via the SkySync Web Service:

 

Retrieve the first 100 audits: 

 

Retrieve the first 100 audits for job X: 

 

Retrieve the first 100 audits for job X, execution ID of Y: 

 

Export all audits: 

 

Export all audits for job X: 

 

Export all audits for job X, execution ID of Y: 

 

To query failures, use the previous three queries, substituting "failures.csv" for "audits.csv".

For example, to export all failures for job X, execution ID of Y: 

 

For these queries, the "pageSize" default and maximum are both 100. 

 

Please contact Portal Architects Support for assistance with this article.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk