You can change the Start Date and End Date in the below script and execute the script in the SQL Management Studio.
---------------------------------------------------------------------
-- BAS_Audit_SQL_Script.sql
-- This SQL script queries the audit records based on user-defined
-- parameters, and displays the results in a human-readable form.
-- (C) 2009 - 2010 Research in Motion Limited.
---------------------------------------------------------------------
BEGIN TRY
DROP function getReconciliationType
END TRY
BEGIN CATCH
PRINT 'Could not remove the getReconciliationType function'
END CATCH
GO
BEGIN TRY
DROP function getEmailAddressByUserId
END TRY
BEGIN CATCH
PRINT 'Could not remove the getEmailAddressByUserId function'
END CATCH
GO
BEGIN TRY
DROP function getFieldDescription
END TRY
BEGIN CATCH
PRINT 'Could not remove the getFieldDescription function'
END CATCH
GO
BEGIN TRY
DROP function getReferenceObjectDescription
END TRY
BEGIN CATCH
PRINT 'Could not remove the getReferenceObjectDescription function'
END CATCH
GO
CREATE FUNCTION getEmailAddressByUserId (@UserId int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @Return varchar(255)
DECLARE @USER_TYPE int
DECLARE @ACTION_DELETE int
SET @USER_TYPE = 15
SET @ACTION_DELETE = 2
SET @Return = 'not found' -- default return value
-- define a constant for the email address fieldId in a user delete record
DECLARE @FieldID_EmailAddress numeric(38,0);
SET @FieldID_EmailAddress = 1031864527934364821304223630113;
-- this is the case where the user still exists in BAS
IF EXISTS (SELECT EmailAddress FROM BASUsers WHERE UserId = @UserId)
SELECT @Return = EmailAddress FROM BASUsers WHERE UserId = @UserId
-- this is the case when the user no longer exists in BAS, we will attempt to find a delete record in the audit table
ELSE
SELECT @Return = OldValue FROM BASAuditEventDetails, BASAuditEventHeaders WHERE BASAuditEventHeaders.ActionCode = @ACTION_DELETE AND BASAuditEventHeaders.TypeId = @USER_TYPE AND BASAuditEventHeaders.ReferenceId = @UserId AND BASAuditEventDetails.FieldId = @FieldID_EmailAddress AND BASAuditEventDetails.AuditEventHeaderId = BASAuditEventHeaders.AuditEventheaderId
RETURN @Return
END
GO
CREATE FUNCTION getReferenceObjectDescription (@auditHeaderId int, @auditEventType int, @action int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @Return varchar(255)
DECLARE @USER_TYPE int
DECLARE @DEVICE_TYPE int
DECLARE @ACTION_KILL int
DECLARE @DEVICE_PASSWORD_TYPE int
DECLARE @UserId int
SET @Return = 'n/a' -- default return value
-- we can display the email address for a user operation
SET @USER_TYPE = 15
-- we can also display the user's email address for a DEVICE KILL operation
SET @DEVICE_TYPE = 19
SET @ACTION_KILL = 4
-- we can also display the user's email address for a DEVICE PASSWORD operation
SET @DEVICE_PASSWORD_TYPE = 29
-- define variable for the fieldId of the OldValue where we will be looking for the userId
DECLARE @FieldID_UserId_OldValue numeric(38,0);
SET @FieldID_UserId_OldValue = 1228714078796831390366063996582;
-- If this is a DEVICE KILL or a DEVICE PASSWORD operation
IF ((@auditEventType = @DEVICE_TYPE AND @action = @ACTION_KILL) OR (@auditEventType = @DEVICE_PASSWORD_TYPE))
BEGIN
-- in this case we grab the userId from the OldValue of the details field
SELECT @UserId = OldValue FROM BASAuditEventDetails WHERE AuditEventHeaderId = @auditHeaderId AND FieldId = @FieldID_UserId_OldValue
-- get the email address
SELECT @Return = dbo.getEmailAddressByUserId(@UserId)
END
-- This is a user operation
ELSE IF (@auditEventType = @USER_TYPE)
BEGIN
-- If this a simple user operation we obtain the userId from the ReferenceId column of this audit event
SELECT @UserId = ReferenceId FROM BASAuditEventHeaders WHERE AuditEventHeaderId = @auditHeaderId
-- get the email address
SELECT @Return = dbo.getEmailAddressByUserId(@UserId)
END
RETURN @Return
END
GO
CREATE FUNCTION getReconciliationType (@auditEventHeaderIdArg int, @auditEventFieldId numeric(38), @auditEventQualifier varchar(100))
RETURNS varchar(50)
AS
BEGIN
DECLARE @Return varchar(50)
DECLARE @SoftwareConfigType varchar(10)
DECLARE @PolicyType varchar(10)
DECLARE @BASPlugInId varchar(10)
SET @Return = 'n/a' -- default return value
SET @SoftwareConfigType = 0
SET @PolicyType = 1
SET @BASPlugInId = '0'
-- define variables for the field Ids that we care about
DECLARE @FieldID_User_ItemId numeric(38,0), @FieldID_User_Rank numeric(38,0), @FieldID_User_Reason numeric(38,0), @FieldID_User_PlugIn numeric(38,0), @FieldID_User_Type numeric(38,0)
DECLARE @FieldID_Group_ItemId numeric(38,0), @FieldID_Group_Rank numeric(38,0), @FieldID_Group_Reason numeric(38,0), @FieldID_Group_PlugIn numeric(38,0), @FieldID_Group_Type numeric(38,0)
SET @FieldID_User_ItemId = -486126491011637892517342999743
SET @FieldID_User_Rank = -780836063801462850911005811676
SET @FieldID_User_Reason = 8121505435217415026685546837119
SET @FieldID_User_PlugIn = -827691586774290504376062343384
SET @FieldID_User_Type = 1989485137097803263459336297703
SET @FieldID_Group_ItemId = -148916219886973681979585881795
SET @FieldID_Group_Rank = -867731059422040740553870998936
SET @FieldID_Group_Reason = -933665789316099401271539979272
SET @FieldID_Group_PlugIn = 1133246823832015469649452479815
SET @FieldID_Group_Type = -678231652774650518910275796375
-- avoid doing any SELECTs if the fieldId we are looking at isn't one of the reconciliation object fields
IF @auditEventFieldId NOT IN
-- Field IDs for '/bas.User/reconciliationObjectServiceAttributes'
(@FieldID_User_ItemId,@FieldID_User_Rank,@FieldID_User_Reason,@FieldID_User_PlugIn,@FieldID_User_Type,
-- Field IDs for '/bas.Group/reconciliationObjectServiceAttributes'
@FieldID_Group_ItemId,@FieldID_Group_Rank,@FieldID_Group_Reason,@FieldID_Group_PlugIn,@FieldID_Group_Type)
RETURN @Return
-- get the reconciliation object ID
DECLARE @OldID varchar(50), @NewID varchar(50)
SELECT @OldID = OldValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_ItemId, @FieldID_Group_ItemId)
SELECT @NewID = NewValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_ItemId, @FieldID_Group_ItemId)
-- get the PlugInId
DECLARE @PlugInIdOld varchar(50), @PlugInIdNew varchar(50)
SELECT @PlugInIdOld = OldValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_PlugIn, @FieldID_Group_PlugIn)
SELECT @PlugInIdNew = NewValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_PlugIn, @FieldID_Group_PlugIn)
-- get the service type
DECLARE @ServiceTypeOld varchar(50), @ServiceTypeNew varchar(50)
SELECT @ServiceTypeOld = OldValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_Type, @FieldID_Group_Type)
SELECT @ServiceTypeNew = NewValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_Type, @FieldID_Group_Type)
-- pick the ID of the object from either the old or the new values
DECLARE @ObjectId varchar(50)
IF @OldId IS NULL
SET @ObjectId = @NewID
ELSE
SET @ObjectId = @OldId
-- make sure that the value of the Plug-in ID is '0', for BAS plug-in
IF (COALESCE (@PlugInIdOld, @PlugInIdnew) = @BASPlugInId)
IF (COALESCE (@ServiceTypeOld, @ServiceTypeNew) = @SoftwareConfigType)
SET @Return = 'Software Configuration (Id: ' + @ObjectId + ')'
ELSE IF (COALESCE (@ServiceTypeOld, @ServiceTypeNew) = @PolicyType)
SET @Return = 'IT Policy (Id: ' + @ObjectId + ')'
RETURN @Return
END
GO
CREATE FUNCTION getFieldDescription (@StringValue varchar(2072), @FieldId varchar(50))
RETURNS varchar(3072)
AS
BEGIN
DECLARE @Return varchar(3072)
IF @FieldId IS NULL
SET @Return = '(no detail fields found)'
ELSE IF @StringValue IS NULL
SET @Return = '(no string found)'
ELSE
SET @Return = @StringValue
RETURN @Return
END
GO
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @userId INT
DECLARE @plugInId INT
DECLARE @typeName char(50)
DECLARE @typeID INT
-- The start of the date range. The format is: YYYYMMDD HH:MM:SSAM/PM, example: 20090511 11:00:00PM
SET @startDate = CONVERT(DATETIME,'20131125 03:00:00AM')
-- The start of the date range. The format is: YYYYMMDD HH:MM:SSAM/PM, example: 20090511 11:00:00PM
SET @endDate = CONVERT(DATETIME, '20131126 12:00:00PM')
-- The user ID of the user that performed the action
-- SET@userId = 10
/*
The type of the audit event. The valid values are listed below, grouped by plugin.
(If you specify a value for this typeName variable then you must also specify a value for the plugInId variable below. The plugin ID
to use for the plugInId parameter is specified beside each plugin name.)
=== BAS_PLUG_IN_IDENTIFIER (plug-in ID: 0) ===
IT_POLICY
IT_POLICY_RULE
IT_POLICIES_AND_TEMPLATES
WLAN_TEMPLATE
WLAN_CONFIGURATION
APPLICATION_CONTROL_POLICY
APPLICATION
APPLICATION_ROOT
ORGANIZATION
ROLE
SOFTWARE_CONFIGURATION
OS_CONFIGURATION
OS_BUNDLE
SOFTWARE_CONFIGURATION_NETWORK_SHARE
GROUP
USER
SERVER
SERVICE_INSTANCE
SERVICE
DEVICE
DEVICE_WIRELESS_ACTIVATION_SETTINGS
DEVICE_MAINTENANCE_STATUS_USER_ATTRIBUTES
DEVICE_MAINTENANCE_SETTINGS_USER_ATTRIBUTES
USER_ENABLE_BLACKBERRY_ATTRIBUTES
DISABLE_BLACKBERRY_USER_ATTRIBUTES
VPN_CONFIGURATION
VOIP_CONFIGURATION
LICENSE
ORGANIZATION_BRANDING_INFORMATION
DEVICE_PASSWORD
USER_ACTIVATION_PASSWORD
=== DISPATCHER_PLUG_IN_IDENTIFIER (plug-in ID: 1) ===
DISPATCHER_SERVICE
DISPATCHER_HOST_SERVICE_INSTANCE
USER
SERVICE_INSTANCE
SERVICE
=== Email Exchange PlugIn (plug-in ID: 100) ===
EMAIL_HOST_SERVICE_INSTANCE
USER_EMAIL_SETTINGS
USER
SERVICE_INSTANCE
SERVICE
=== Email Domino PlugIn (plug-in ID: 101) ===
EMAIL_HOST_SERVICE_INSTANCE
USER_EMAIL_SETTINGS
USER
SERVICE_INSTANCE
SERVICE
=== Email GroupWise PlugIn (plug-in ID: 102) ===
EMAIL_HOST_SERVICE_INSTANCE
USER_EMAIL_SETTINGS
USER
SERVICE_INSTANCE
SERVICE
=== MDSS_PLUG_IN_IDENTIFIER (plug-in ID: 105) ===
CERTIFICATE_UPDATE
SERVER_CERTIFICATE
=== MDSCS_PLUG_IN_IDENTIFIER (plug-in ID: 106) ===
ACCESS_CONTROL_RULES
=== All other plugins ==
SERVICE_INSTANCE
SERVICE
*/
--SET @typeName = 'USER'
-- The plugin ID.
--SET @plugInID = 0
---------------------------------------------------------------------------------------------------
--- DO NOT MODIFY SQL BELOW THIS LINE. All arguments that can be configured are above this line.
---------------------------------------------------------------------------------------------------
--Convert Type name to a type id
IF @pluginID IS NOT NULL AND @typeName IS NOT NULL
BEGIN
SET @typeID =
CASE @pluginID
--- BAS_PLUG_IN_IDENTIFIER = 0
WHEN 0 THEN
CASE @typeName
WHEN 'IT_POLICY' THEN 0
WHEN 'IT_POLICY_RULE' THEN 1
WHEN 'IT_POLICIES_AND_TEMPLATES' THEN 2
WHEN 'WLAN_TEMPLATE' THEN 3
WHEN 'WLAN_CONFIGURATION' THEN 4
WHEN 'APPLICATION_CONTROL_POLICY' THEN 5
WHEN 'APPLICATION' THEN 6
WHEN 'APPLICATION_ROOT' THEN 7
WHEN 'ORGANIZATION' THEN 8
WHEN 'ROLE' THEN 9
WHEN 'SOFTWARE_CONFIGURATION' THEN 10
WHEN 'OS_CONFIGURATION' THEN 11
WHEN 'OS_BUNDLE' THEN 12
WHEN 'SOFTWARE_CONFIGURATION_NETWORK_SHARE' THEN 13
WHEN 'GROUP' THEN 14
WHEN 'USER' THEN 15
WHEN 'SERVER' THEN 16
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
WHEN 'DEVICE' THEN 19
WHEN 'DEVICE_WIRELESS_ACTIVATION_SETTINGS' THEN 20
WHEN 'DEVICE_MAINTENANCE_STATUS_USER_ATTRIBUTES' THEN 21
WHEN 'DEVICE_MAINTENANCE_SETTINGS_USER_ATTRIBUTES' THEN 22
WHEN 'USER_ENABLE_BLACKBERRY_ATTRIBUTES' THEN 23
WHEN 'DISABLE_BLACKBERRY_USER_ATTRIBUTES' THEN 24
WHEN 'VPN_CONFIGURATION' THEN 25
WHEN 'VOIP_CONFIGURATION' THEN 26
WHEN 'LICENSE' THEN 27
WHEN 'ORGANIZATION_BRANDING_INFORMATION' THEN 28
WHEN 'DEVICE_PASSWORD' THEN 29
WHEN 'USER_ACTIVATION_PASSWORD' THEN 30
ELSE -1
END
--- DISPATCHER_PLUG_IN_IDENTIFIER = 1
WHEN 1 THEN
CASE @typeName
WHEN 'DISPATCHER_SERVICE' THEN 0
WHEN 'DISPATCHER_HOST_SERVICE_INSTANCE' THEN 1
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 'USER' THEN 15
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
ELSE -1
END
--- Email Exchange PlugIn
WHEN 100 THEN
CASE @typeName
WHEN 'EMAIL_HOST_SERVICE_INSTANCE' THEN 0
WHEN 'USER_EMAIL_SETTINGS' THEN 1
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 'USER' THEN 15
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
ELSE -1
END
--- Email Domino PlugIn
WHEN 101 THEN
CASE @typeName
WHEN 'EMAIL_HOST_SERVICE_INSTANCE' THEN 0
WHEN 'USER_EMAIL_SETTINGS' THEN 1
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 'USER' THEN 15
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
ELSE -1
END
--- Email GroupWise PlugIn
WHEN 102 THEN
CASE @typeName
WHEN 'EMAIL_HOST_SERVICE_INSTANCE' THEN 0
WHEN 'USER_EMAIL_SETTINGS' THEN 1
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 'USER' THEN 15
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
ELSE -1
END
--- MDSS_PLUG_IN_IDENTIFIER = 105
WHEN 105 THEN
CASE @typeName
WHEN 'CERTIFICATE_UPDATE' THEN 0
WHEN 'SERVER_CERTIFICATE' THEN 1
ELSE -1
END
--- MDSCS_PLUG_IN_IDENTIFIER = 106
WHEN 106 THEN
CASE @typeName
WHEN 'ACCESS_CONTROL_RULES' THEN 0
ELSE -1
END
--- all other plugins
ELSE
CASE @typeName
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
ELSE -1
END
END
END
SELECT
BASAuditEventHeaders.AuditEventHeaderId,
BASAuditEventDetails.Sequence,
BASUsers.UserId as BASUserId,
CASE WHEN BASUsers.LoginName IS NULL THEN '[external user]' ELSE BASUsers.LoginName END as BASLoginName,
BASUsers.DisplayName as BASDisplayName,
BASAuditEventHeaders.ChangeDateTime,
BASServices.PlugInId,
BASServices.Name,
BASAuditEventHeaders.TypeId,
CASE BASAuditEventHeaders.PlugInId
--- BAS_PLUG_IN_IDENTIFIER = 0
WHEN 0 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'IT_POLICY'
WHEN 1 THEN 'IT_POLICY_RULE'
WHEN 2 THEN 'IT_POLICIES_AND_TEMPLATES'
WHEN 3 THEN 'WLAN_TEMPLATE'
WHEN 4 THEN 'WLAN_CONFIGURATION'
WHEN 5 THEN 'APPLICATION_CONTROL_POLICY'
WHEN 6 THEN 'APPLICATION'
WHEN 7 THEN 'APPLICATION_ROOT'
WHEN 8 THEN 'ORGANIZATION'
WHEN 9 THEN 'ROLE'
WHEN 10 THEN 'SOFTWARE_CONFIGURATION'
WHEN 11 THEN 'OS_CONFIGURATION'
WHEN 12 THEN 'OS_BUNDLE'
WHEN 13 THEN 'SOFTWARE_CONFIGURATION_NETWORK_SHARE'
WHEN 14 THEN 'GROUP'
WHEN 15 THEN 'USER'
WHEN 16 THEN 'SERVER'
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
WHEN 19 THEN 'DEVICE'
WHEN 20 THEN 'DEVICE_WIRELESS_ACTIVATION_SETTINGS'
WHEN 21 THEN 'DEVICE_MAINTENANCE_STATUS_USER_ATTRIBUTES'
WHEN 22 THEN 'DEVICE_MAINTENANCE_SETTINGS_USER_ATTRIBUTES'
WHEN 23 THEN 'USER_ENABLE_BLACKBERRY_ATTRIBUTES'
WHEN 24 THEN 'DISABLE_BLACKBERRY_USER_ATTRIBUTES'
WHEN 25 THEN 'VPN_CONFIGURATION'
WHEN 26 THEN 'VOIP_CONFIGURATION'
WHEN 27 THEN 'LICENSE'
WHEN 28 THEN 'ORGANIZATION_BRANDING_INFORMATION'
WHEN 29 THEN 'DEVICE_PASSWORD'
WHEN 30 THEN 'USER_ACTIVATION_PASSWORD'
ELSE ''
END
--- DISPATCHER_PLUG_IN_IDENTIFIER = 1
WHEN 1 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'DISPATCHER_SERVICE'
WHEN 1 THEN 'DISPATCHER_HOST_SERVICE_INSTANCE'
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 15 THEN 'USER'
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
ELSE ''
END
--- Email Exchange PlugIn
WHEN 100 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'EMAIL_HOST_SERVICE_INSTANCE'
WHEN 1 THEN 'USER_EMAIL_SETTINGS'
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 15 THEN 'USER'
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
ELSE ''
END
--- Email Domino PlugIn
WHEN 101 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'EMAIL_HOST_SERVICE_INSTANCE'
WHEN 1 THEN 'USER_EMAIL_SETTINGS'
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 15 THEN 'USER'
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
ELSE ''
END
--- Email GroupWise PlugIn
WHEN 102 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'EMAIL_HOST_SERVICE_INSTANCE'
WHEN 1 THEN 'USER_EMAIL_SETTINGS'
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 15 THEN 'USER'
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
ELSE ''
END
--- MDSS_PLUG_IN_IDENTIFIER = 105
WHEN 105 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'CERTIFICATE_UPDATE'
WHEN 1 THEN 'SERVER_CERTIFICATE'
ELSE ''
END
--- MDSCS_PLUG_IN_IDENTIFIER = 106
WHEN 106 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'ACCESS_CONTROL_RULES'
ELSE ''
END
--- all other plugins
ELSE
CASE BASAuditEventHeaders.TypeId
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
ELSE ''
END
END AS TypeText,
BASAuditEventHeaders.ReferenceId,
dbo.getReferenceObjectDescription(BASAuditEventHeaders.AuditEventHeaderId, BASAuditEventHeaders.TypeId, BASAuditEventHeaders.ActionCode) as ReferenceObjectDescription,
CASE BASAuditEventHeaders.ActionCode
WHEN 0 Then 'Create'
WHEN 1 Then 'Update'
WHEN 2 Then 'Delete'
WHEN 3 Then 'Import'
WHEN 4 Then 'Kill'
ELSE 'unknown'
END AS ActionCode,
dbo.getFieldDescription(BASLocaleStrings.StringValue, CONVERT(varchar(50), BasAuditEventDetails.FieldId)) as FieldDescription,
BASAuditEventDetails.OldValue,
BASAuditEventDetails.NewValue,
dbo.getReconciliationType(BASAuditEventHeaders.AuditEventHeaderId, BASAuditEventDetails.FieldId, BASAuditEventDetails.Qualifier) as ReconciliationObjectType
FROM
BASAuditEventHeaders LEFT OUTER JOIN BASAuditEventDetails ON BASAuditEventHeaders.AuditEventHeaderId = BASAuditEventDetails.AuditEventHeaderId
LEFT OUTER JOIN BASLocaleStrings ON 'AUDITABLE_FIELD_DESC_' + CONVERT(varchar(50), BasAuditEventDetails.FieldId) = BASLocaleStrings.StringId and BASLocaleStrings.Locale = 'en_US'
LEFT OUTER JOIN BASUsers ON BASAuditEventHeaders.UserId = BASUsers.UserId
LEFT OUTER JOIN BASServices ON BASAuditEventHeaders.PlugInId = BASServices.PlugInId
WHERE
BASServices.Type = 0 AND
((@startDate IS NULL) OR (BASAuditEventHeaders.changeDateTime > @startDate)) AND
((@endDate IS NULL) OR (BASAuditEventHeaders.changeDateTime < @endDate)) AND
((@userId IS NULL) OR (BASAuditEventHeaders.UserId = @userId)) AND
((@plugInID IS NULL) OR (BASAuditEventHeaders.PlugInId = @plugInID)) AND
((@typeID IS NULL) OR (BASAuditEventHeaders.TypeId = @typeID))
ORDER BY
BASAuditEventHeaders.AuditEventHeaderId,
BASAuditEventDetails.Qualifier,
BASAuditEventDetails.FieldId;
---------------------------------------------------------------------
-- BAS_Audit_SQL_Script.sql
-- This SQL script queries the audit records based on user-defined
-- parameters, and displays the results in a human-readable form.
-- (C) 2009 - 2010 Research in Motion Limited.
---------------------------------------------------------------------
BEGIN TRY
DROP function getReconciliationType
END TRY
BEGIN CATCH
PRINT 'Could not remove the getReconciliationType function'
END CATCH
GO
BEGIN TRY
DROP function getEmailAddressByUserId
END TRY
BEGIN CATCH
PRINT 'Could not remove the getEmailAddressByUserId function'
END CATCH
GO
BEGIN TRY
DROP function getFieldDescription
END TRY
BEGIN CATCH
PRINT 'Could not remove the getFieldDescription function'
END CATCH
GO
BEGIN TRY
DROP function getReferenceObjectDescription
END TRY
BEGIN CATCH
PRINT 'Could not remove the getReferenceObjectDescription function'
END CATCH
GO
CREATE FUNCTION getEmailAddressByUserId (@UserId int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @Return varchar(255)
DECLARE @USER_TYPE int
DECLARE @ACTION_DELETE int
SET @USER_TYPE = 15
SET @ACTION_DELETE = 2
SET @Return = 'not found' -- default return value
-- define a constant for the email address fieldId in a user delete record
DECLARE @FieldID_EmailAddress numeric(38,0);
SET @FieldID_EmailAddress = 1031864527934364821304223630113;
-- this is the case where the user still exists in BAS
IF EXISTS (SELECT EmailAddress FROM BASUsers WHERE UserId = @UserId)
SELECT @Return = EmailAddress FROM BASUsers WHERE UserId = @UserId
-- this is the case when the user no longer exists in BAS, we will attempt to find a delete record in the audit table
ELSE
SELECT @Return = OldValue FROM BASAuditEventDetails, BASAuditEventHeaders WHERE BASAuditEventHeaders.ActionCode = @ACTION_DELETE AND BASAuditEventHeaders.TypeId = @USER_TYPE AND BASAuditEventHeaders.ReferenceId = @UserId AND BASAuditEventDetails.FieldId = @FieldID_EmailAddress AND BASAuditEventDetails.AuditEventHeaderId = BASAuditEventHeaders.AuditEventheaderId
RETURN @Return
END
GO
CREATE FUNCTION getReferenceObjectDescription (@auditHeaderId int, @auditEventType int, @action int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @Return varchar(255)
DECLARE @USER_TYPE int
DECLARE @DEVICE_TYPE int
DECLARE @ACTION_KILL int
DECLARE @DEVICE_PASSWORD_TYPE int
DECLARE @UserId int
SET @Return = 'n/a' -- default return value
-- we can display the email address for a user operation
SET @USER_TYPE = 15
-- we can also display the user's email address for a DEVICE KILL operation
SET @DEVICE_TYPE = 19
SET @ACTION_KILL = 4
-- we can also display the user's email address for a DEVICE PASSWORD operation
SET @DEVICE_PASSWORD_TYPE = 29
-- define variable for the fieldId of the OldValue where we will be looking for the userId
DECLARE @FieldID_UserId_OldValue numeric(38,0);
SET @FieldID_UserId_OldValue = 1228714078796831390366063996582;
-- If this is a DEVICE KILL or a DEVICE PASSWORD operation
IF ((@auditEventType = @DEVICE_TYPE AND @action = @ACTION_KILL) OR (@auditEventType = @DEVICE_PASSWORD_TYPE))
BEGIN
-- in this case we grab the userId from the OldValue of the details field
SELECT @UserId = OldValue FROM BASAuditEventDetails WHERE AuditEventHeaderId = @auditHeaderId AND FieldId = @FieldID_UserId_OldValue
-- get the email address
SELECT @Return = dbo.getEmailAddressByUserId(@UserId)
END
-- This is a user operation
ELSE IF (@auditEventType = @USER_TYPE)
BEGIN
-- If this a simple user operation we obtain the userId from the ReferenceId column of this audit event
SELECT @UserId = ReferenceId FROM BASAuditEventHeaders WHERE AuditEventHeaderId = @auditHeaderId
-- get the email address
SELECT @Return = dbo.getEmailAddressByUserId(@UserId)
END
RETURN @Return
END
GO
CREATE FUNCTION getReconciliationType (@auditEventHeaderIdArg int, @auditEventFieldId numeric(38), @auditEventQualifier varchar(100))
RETURNS varchar(50)
AS
BEGIN
DECLARE @Return varchar(50)
DECLARE @SoftwareConfigType varchar(10)
DECLARE @PolicyType varchar(10)
DECLARE @BASPlugInId varchar(10)
SET @Return = 'n/a' -- default return value
SET @SoftwareConfigType = 0
SET @PolicyType = 1
SET @BASPlugInId = '0'
-- define variables for the field Ids that we care about
DECLARE @FieldID_User_ItemId numeric(38,0), @FieldID_User_Rank numeric(38,0), @FieldID_User_Reason numeric(38,0), @FieldID_User_PlugIn numeric(38,0), @FieldID_User_Type numeric(38,0)
DECLARE @FieldID_Group_ItemId numeric(38,0), @FieldID_Group_Rank numeric(38,0), @FieldID_Group_Reason numeric(38,0), @FieldID_Group_PlugIn numeric(38,0), @FieldID_Group_Type numeric(38,0)
SET @FieldID_User_ItemId = -486126491011637892517342999743
SET @FieldID_User_Rank = -780836063801462850911005811676
SET @FieldID_User_Reason = 8121505435217415026685546837119
SET @FieldID_User_PlugIn = -827691586774290504376062343384
SET @FieldID_User_Type = 1989485137097803263459336297703
SET @FieldID_Group_ItemId = -148916219886973681979585881795
SET @FieldID_Group_Rank = -867731059422040740553870998936
SET @FieldID_Group_Reason = -933665789316099401271539979272
SET @FieldID_Group_PlugIn = 1133246823832015469649452479815
SET @FieldID_Group_Type = -678231652774650518910275796375
-- avoid doing any SELECTs if the fieldId we are looking at isn't one of the reconciliation object fields
IF @auditEventFieldId NOT IN
-- Field IDs for '/bas.User/reconciliationObjectServiceAttributes'
(@FieldID_User_ItemId,@FieldID_User_Rank,@FieldID_User_Reason,@FieldID_User_PlugIn,@FieldID_User_Type,
-- Field IDs for '/bas.Group/reconciliationObjectServiceAttributes'
@FieldID_Group_ItemId,@FieldID_Group_Rank,@FieldID_Group_Reason,@FieldID_Group_PlugIn,@FieldID_Group_Type)
RETURN @Return
-- get the reconciliation object ID
DECLARE @OldID varchar(50), @NewID varchar(50)
SELECT @OldID = OldValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_ItemId, @FieldID_Group_ItemId)
SELECT @NewID = NewValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_ItemId, @FieldID_Group_ItemId)
-- get the PlugInId
DECLARE @PlugInIdOld varchar(50), @PlugInIdNew varchar(50)
SELECT @PlugInIdOld = OldValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_PlugIn, @FieldID_Group_PlugIn)
SELECT @PlugInIdNew = NewValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_PlugIn, @FieldID_Group_PlugIn)
-- get the service type
DECLARE @ServiceTypeOld varchar(50), @ServiceTypeNew varchar(50)
SELECT @ServiceTypeOld = OldValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_Type, @FieldID_Group_Type)
SELECT @ServiceTypeNew = NewValue FROM BASAuditEventDetails WHERE auditEventHeaderId = @auditEventHeaderIdArg AND Qualifier = @auditEventQualifier AND FieldId IN (@FieldID_User_Type, @FieldID_Group_Type)
-- pick the ID of the object from either the old or the new values
DECLARE @ObjectId varchar(50)
IF @OldId IS NULL
SET @ObjectId = @NewID
ELSE
SET @ObjectId = @OldId
-- make sure that the value of the Plug-in ID is '0', for BAS plug-in
IF (COALESCE (@PlugInIdOld, @PlugInIdnew) = @BASPlugInId)
IF (COALESCE (@ServiceTypeOld, @ServiceTypeNew) = @SoftwareConfigType)
SET @Return = 'Software Configuration (Id: ' + @ObjectId + ')'
ELSE IF (COALESCE (@ServiceTypeOld, @ServiceTypeNew) = @PolicyType)
SET @Return = 'IT Policy (Id: ' + @ObjectId + ')'
RETURN @Return
END
GO
CREATE FUNCTION getFieldDescription (@StringValue varchar(2072), @FieldId varchar(50))
RETURNS varchar(3072)
AS
BEGIN
DECLARE @Return varchar(3072)
IF @FieldId IS NULL
SET @Return = '(no detail fields found)'
ELSE IF @StringValue IS NULL
SET @Return = '(no string found)'
ELSE
SET @Return = @StringValue
RETURN @Return
END
GO
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @userId INT
DECLARE @plugInId INT
DECLARE @typeName char(50)
DECLARE @typeID INT
-- The start of the date range. The format is: YYYYMMDD HH:MM:SSAM/PM, example: 20090511 11:00:00PM
SET @startDate = CONVERT(DATETIME,'20131125 03:00:00AM')
-- The start of the date range. The format is: YYYYMMDD HH:MM:SSAM/PM, example: 20090511 11:00:00PM
SET @endDate = CONVERT(DATETIME, '20131126 12:00:00PM')
-- The user ID of the user that performed the action
-- SET@userId = 10
/*
The type of the audit event. The valid values are listed below, grouped by plugin.
(If you specify a value for this typeName variable then you must also specify a value for the plugInId variable below. The plugin ID
to use for the plugInId parameter is specified beside each plugin name.)
=== BAS_PLUG_IN_IDENTIFIER (plug-in ID: 0) ===
IT_POLICY
IT_POLICY_RULE
IT_POLICIES_AND_TEMPLATES
WLAN_TEMPLATE
WLAN_CONFIGURATION
APPLICATION_CONTROL_POLICY
APPLICATION
APPLICATION_ROOT
ORGANIZATION
ROLE
SOFTWARE_CONFIGURATION
OS_CONFIGURATION
OS_BUNDLE
SOFTWARE_CONFIGURATION_NETWORK_SHARE
GROUP
USER
SERVER
SERVICE_INSTANCE
SERVICE
DEVICE
DEVICE_WIRELESS_ACTIVATION_SETTINGS
DEVICE_MAINTENANCE_STATUS_USER_ATTRIBUTES
DEVICE_MAINTENANCE_SETTINGS_USER_ATTRIBUTES
USER_ENABLE_BLACKBERRY_ATTRIBUTES
DISABLE_BLACKBERRY_USER_ATTRIBUTES
VPN_CONFIGURATION
VOIP_CONFIGURATION
LICENSE
ORGANIZATION_BRANDING_INFORMATION
DEVICE_PASSWORD
USER_ACTIVATION_PASSWORD
=== DISPATCHER_PLUG_IN_IDENTIFIER (plug-in ID: 1) ===
DISPATCHER_SERVICE
DISPATCHER_HOST_SERVICE_INSTANCE
USER
SERVICE_INSTANCE
SERVICE
=== Email Exchange PlugIn (plug-in ID: 100) ===
EMAIL_HOST_SERVICE_INSTANCE
USER_EMAIL_SETTINGS
USER
SERVICE_INSTANCE
SERVICE
=== Email Domino PlugIn (plug-in ID: 101) ===
EMAIL_HOST_SERVICE_INSTANCE
USER_EMAIL_SETTINGS
USER
SERVICE_INSTANCE
SERVICE
=== Email GroupWise PlugIn (plug-in ID: 102) ===
EMAIL_HOST_SERVICE_INSTANCE
USER_EMAIL_SETTINGS
USER
SERVICE_INSTANCE
SERVICE
=== MDSS_PLUG_IN_IDENTIFIER (plug-in ID: 105) ===
CERTIFICATE_UPDATE
SERVER_CERTIFICATE
=== MDSCS_PLUG_IN_IDENTIFIER (plug-in ID: 106) ===
ACCESS_CONTROL_RULES
=== All other plugins ==
SERVICE_INSTANCE
SERVICE
*/
--SET @typeName = 'USER'
-- The plugin ID.
--SET @plugInID = 0
---------------------------------------------------------------------------------------------------
--- DO NOT MODIFY SQL BELOW THIS LINE. All arguments that can be configured are above this line.
---------------------------------------------------------------------------------------------------
--Convert Type name to a type id
IF @pluginID IS NOT NULL AND @typeName IS NOT NULL
BEGIN
SET @typeID =
CASE @pluginID
--- BAS_PLUG_IN_IDENTIFIER = 0
WHEN 0 THEN
CASE @typeName
WHEN 'IT_POLICY' THEN 0
WHEN 'IT_POLICY_RULE' THEN 1
WHEN 'IT_POLICIES_AND_TEMPLATES' THEN 2
WHEN 'WLAN_TEMPLATE' THEN 3
WHEN 'WLAN_CONFIGURATION' THEN 4
WHEN 'APPLICATION_CONTROL_POLICY' THEN 5
WHEN 'APPLICATION' THEN 6
WHEN 'APPLICATION_ROOT' THEN 7
WHEN 'ORGANIZATION' THEN 8
WHEN 'ROLE' THEN 9
WHEN 'SOFTWARE_CONFIGURATION' THEN 10
WHEN 'OS_CONFIGURATION' THEN 11
WHEN 'OS_BUNDLE' THEN 12
WHEN 'SOFTWARE_CONFIGURATION_NETWORK_SHARE' THEN 13
WHEN 'GROUP' THEN 14
WHEN 'USER' THEN 15
WHEN 'SERVER' THEN 16
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
WHEN 'DEVICE' THEN 19
WHEN 'DEVICE_WIRELESS_ACTIVATION_SETTINGS' THEN 20
WHEN 'DEVICE_MAINTENANCE_STATUS_USER_ATTRIBUTES' THEN 21
WHEN 'DEVICE_MAINTENANCE_SETTINGS_USER_ATTRIBUTES' THEN 22
WHEN 'USER_ENABLE_BLACKBERRY_ATTRIBUTES' THEN 23
WHEN 'DISABLE_BLACKBERRY_USER_ATTRIBUTES' THEN 24
WHEN 'VPN_CONFIGURATION' THEN 25
WHEN 'VOIP_CONFIGURATION' THEN 26
WHEN 'LICENSE' THEN 27
WHEN 'ORGANIZATION_BRANDING_INFORMATION' THEN 28
WHEN 'DEVICE_PASSWORD' THEN 29
WHEN 'USER_ACTIVATION_PASSWORD' THEN 30
ELSE -1
END
--- DISPATCHER_PLUG_IN_IDENTIFIER = 1
WHEN 1 THEN
CASE @typeName
WHEN 'DISPATCHER_SERVICE' THEN 0
WHEN 'DISPATCHER_HOST_SERVICE_INSTANCE' THEN 1
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 'USER' THEN 15
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
ELSE -1
END
--- Email Exchange PlugIn
WHEN 100 THEN
CASE @typeName
WHEN 'EMAIL_HOST_SERVICE_INSTANCE' THEN 0
WHEN 'USER_EMAIL_SETTINGS' THEN 1
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 'USER' THEN 15
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
ELSE -1
END
--- Email Domino PlugIn
WHEN 101 THEN
CASE @typeName
WHEN 'EMAIL_HOST_SERVICE_INSTANCE' THEN 0
WHEN 'USER_EMAIL_SETTINGS' THEN 1
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 'USER' THEN 15
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
ELSE -1
END
--- Email GroupWise PlugIn
WHEN 102 THEN
CASE @typeName
WHEN 'EMAIL_HOST_SERVICE_INSTANCE' THEN 0
WHEN 'USER_EMAIL_SETTINGS' THEN 1
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 'USER' THEN 15
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
ELSE -1
END
--- MDSS_PLUG_IN_IDENTIFIER = 105
WHEN 105 THEN
CASE @typeName
WHEN 'CERTIFICATE_UPDATE' THEN 0
WHEN 'SERVER_CERTIFICATE' THEN 1
ELSE -1
END
--- MDSCS_PLUG_IN_IDENTIFIER = 106
WHEN 106 THEN
CASE @typeName
WHEN 'ACCESS_CONTROL_RULES' THEN 0
ELSE -1
END
--- all other plugins
ELSE
CASE @typeName
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 'SERVICE_INSTANCE' THEN 17
WHEN 'SERVICE' THEN 18
ELSE -1
END
END
END
SELECT
BASAuditEventHeaders.AuditEventHeaderId,
BASAuditEventDetails.Sequence,
BASUsers.UserId as BASUserId,
CASE WHEN BASUsers.LoginName IS NULL THEN '[external user]' ELSE BASUsers.LoginName END as BASLoginName,
BASUsers.DisplayName as BASDisplayName,
BASAuditEventHeaders.ChangeDateTime,
BASServices.PlugInId,
BASServices.Name,
BASAuditEventHeaders.TypeId,
CASE BASAuditEventHeaders.PlugInId
--- BAS_PLUG_IN_IDENTIFIER = 0
WHEN 0 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'IT_POLICY'
WHEN 1 THEN 'IT_POLICY_RULE'
WHEN 2 THEN 'IT_POLICIES_AND_TEMPLATES'
WHEN 3 THEN 'WLAN_TEMPLATE'
WHEN 4 THEN 'WLAN_CONFIGURATION'
WHEN 5 THEN 'APPLICATION_CONTROL_POLICY'
WHEN 6 THEN 'APPLICATION'
WHEN 7 THEN 'APPLICATION_ROOT'
WHEN 8 THEN 'ORGANIZATION'
WHEN 9 THEN 'ROLE'
WHEN 10 THEN 'SOFTWARE_CONFIGURATION'
WHEN 11 THEN 'OS_CONFIGURATION'
WHEN 12 THEN 'OS_BUNDLE'
WHEN 13 THEN 'SOFTWARE_CONFIGURATION_NETWORK_SHARE'
WHEN 14 THEN 'GROUP'
WHEN 15 THEN 'USER'
WHEN 16 THEN 'SERVER'
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
WHEN 19 THEN 'DEVICE'
WHEN 20 THEN 'DEVICE_WIRELESS_ACTIVATION_SETTINGS'
WHEN 21 THEN 'DEVICE_MAINTENANCE_STATUS_USER_ATTRIBUTES'
WHEN 22 THEN 'DEVICE_MAINTENANCE_SETTINGS_USER_ATTRIBUTES'
WHEN 23 THEN 'USER_ENABLE_BLACKBERRY_ATTRIBUTES'
WHEN 24 THEN 'DISABLE_BLACKBERRY_USER_ATTRIBUTES'
WHEN 25 THEN 'VPN_CONFIGURATION'
WHEN 26 THEN 'VOIP_CONFIGURATION'
WHEN 27 THEN 'LICENSE'
WHEN 28 THEN 'ORGANIZATION_BRANDING_INFORMATION'
WHEN 29 THEN 'DEVICE_PASSWORD'
WHEN 30 THEN 'USER_ACTIVATION_PASSWORD'
ELSE ''
END
--- DISPATCHER_PLUG_IN_IDENTIFIER = 1
WHEN 1 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'DISPATCHER_SERVICE'
WHEN 1 THEN 'DISPATCHER_HOST_SERVICE_INSTANCE'
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 15 THEN 'USER'
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
ELSE ''
END
--- Email Exchange PlugIn
WHEN 100 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'EMAIL_HOST_SERVICE_INSTANCE'
WHEN 1 THEN 'USER_EMAIL_SETTINGS'
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 15 THEN 'USER'
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
ELSE ''
END
--- Email Domino PlugIn
WHEN 101 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'EMAIL_HOST_SERVICE_INSTANCE'
WHEN 1 THEN 'USER_EMAIL_SETTINGS'
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 15 THEN 'USER'
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
ELSE ''
END
--- Email GroupWise PlugIn
WHEN 102 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'EMAIL_HOST_SERVICE_INSTANCE'
WHEN 1 THEN 'USER_EMAIL_SETTINGS'
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 15 THEN 'USER'
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
ELSE ''
END
--- MDSS_PLUG_IN_IDENTIFIER = 105
WHEN 105 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'CERTIFICATE_UPDATE'
WHEN 1 THEN 'SERVER_CERTIFICATE'
ELSE ''
END
--- MDSCS_PLUG_IN_IDENTIFIER = 106
WHEN 106 THEN
CASE BASAuditEventHeaders.TypeId
WHEN 0 THEN 'ACCESS_CONTROL_RULES'
ELSE ''
END
--- all other plugins
ELSE
CASE BASAuditEventHeaders.TypeId
--these are here to handle a bug that was in BAS 5.0.0 but removed in BAS 5.0.1
WHEN 17 THEN 'SERVICE_INSTANCE'
WHEN 18 THEN 'SERVICE'
ELSE ''
END
END AS TypeText,
BASAuditEventHeaders.ReferenceId,
dbo.getReferenceObjectDescription(BASAuditEventHeaders.AuditEventHeaderId, BASAuditEventHeaders.TypeId, BASAuditEventHeaders.ActionCode) as ReferenceObjectDescription,
CASE BASAuditEventHeaders.ActionCode
WHEN 0 Then 'Create'
WHEN 1 Then 'Update'
WHEN 2 Then 'Delete'
WHEN 3 Then 'Import'
WHEN 4 Then 'Kill'
ELSE 'unknown'
END AS ActionCode,
dbo.getFieldDescription(BASLocaleStrings.StringValue, CONVERT(varchar(50), BasAuditEventDetails.FieldId)) as FieldDescription,
BASAuditEventDetails.OldValue,
BASAuditEventDetails.NewValue,
dbo.getReconciliationType(BASAuditEventHeaders.AuditEventHeaderId, BASAuditEventDetails.FieldId, BASAuditEventDetails.Qualifier) as ReconciliationObjectType
FROM
BASAuditEventHeaders LEFT OUTER JOIN BASAuditEventDetails ON BASAuditEventHeaders.AuditEventHeaderId = BASAuditEventDetails.AuditEventHeaderId
LEFT OUTER JOIN BASLocaleStrings ON 'AUDITABLE_FIELD_DESC_' + CONVERT(varchar(50), BasAuditEventDetails.FieldId) = BASLocaleStrings.StringId and BASLocaleStrings.Locale = 'en_US'
LEFT OUTER JOIN BASUsers ON BASAuditEventHeaders.UserId = BASUsers.UserId
LEFT OUTER JOIN BASServices ON BASAuditEventHeaders.PlugInId = BASServices.PlugInId
WHERE
BASServices.Type = 0 AND
((@startDate IS NULL) OR (BASAuditEventHeaders.changeDateTime > @startDate)) AND
((@endDate IS NULL) OR (BASAuditEventHeaders.changeDateTime < @endDate)) AND
((@userId IS NULL) OR (BASAuditEventHeaders.UserId = @userId)) AND
((@plugInID IS NULL) OR (BASAuditEventHeaders.PlugInId = @plugInID)) AND
((@typeID IS NULL) OR (BASAuditEventHeaders.TypeId = @typeID))
ORDER BY
BASAuditEventHeaders.AuditEventHeaderId,
BASAuditEventDetails.Qualifier,
BASAuditEventDetails.FieldId;
No comments:
Post a Comment