I'm looking for a script / query to track when / who disables a user account as well removes. I have besalerts that give me when but I need to track who is doing what to satisfy an audit.

Thanks!

__________________

That would be a good report to have.

Do you use roles at your company for people to access the BES?

__________________
You may know me as Juwaack68

Yes I use Enterprise Admin and Senior Help Desk. Unfortunately both can disable and remove accounts.

__________________

We use Senior Help Desk (and I'm the only Ent. Admin) for 15 of our Helpdesk people, so I know what you mean. Would be nice to know what they are doing for users when people call in.

Would also be nice to see when passwords are set (both activation and locking pwds), service books sent, and IT Policy assigned (which one and when).

__________________
You may know me as Juwaack68

Hint: Take a look at the serverconfighistory table of the db

Isn't there a resource kit tool that shows who did what on the BES domain? I can't check for the exact name myself, as I am on a non-Windows machine at the moment.

__________________

Yes. AdminHistory.exe. It exports a LOT of information to the CSV, so you could run this once a day and parse the log lines you need specifically.

__________________
BCSA (4.1, 5.0) | BCSD (4.1, 5.0)

The views expressed by me on Port3101.org are my own and do not necessarily reflect the views of my employer.

Thanks for the memory refresh

__________________

select * from BESMgmt.dbo.serverconfighistory order by changedatetime desc

Will get you started.

---------------------------------------------------------------------
-- 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 Research in Motion Limited.
---------------------------------------------------------------------

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @userId INT
DECLARE @plugInId INT
DECLARE @typeName char(50)
DECLARE @typeID INT
DECLARE @referenceId INT


-- The start of the date range. The format is: YYYYMMDD HH:MM:SSAM/PM, example: 20090511 11:00:00PM
-- SET @startDate = CONVERT(DATETIME,'20111116 12: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, '20111217 11:00:00PM')

-- The user ID of the user that performed the action
-- SET @userId = 10

-- Set the reference ID to find the group or user based upon the ID.
-- SET @referenceId = 1


/*
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.)

=== BAS_PLUG_IN_IDENTIFIER ===
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 ===
DISPATCHER_SERVICE
DISPATCHER_HOST_SERVICE_INSTANCE
USER
SERVICE_INSTANCE
SERVICE

=== Email Exchange PlugIn ===
EMAIL_HOST_SERVICE_INSTANCE
USER_EMAIL_SETTINGS
USER
SERVICE_INSTANCE
SERVICE

=== Email Domino PlugIn ===
EMAIL_HOST_SERVICE_INSTANCE
USER_EMAIL_SETTINGS
USER
SERVICE_INSTANCE
SERVICE

=== Email GroupWise PlugIn ===
EMAIL_HOST_SERVICE_INSTANCE
USER_EMAIL_SETTINGS
USER
SERVICE_INSTANCE
SERVICE

=== MDSS_PLUG_IN_IDENTIFIER ===
CERTIFICATE_UPDATE
SERVER_CERTIFICATE

=== MDSCS_PLUG_IN_IDENTIFIER ===
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,

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,

BASLocaleStrings.StringValue,
BASAuditEventDetails.OldValue,
BASAuditEventDetails.NewValue
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)) AND
((@referenceId IS NULL) OR (BASAuditEventHeaders.ReferenceId = @referenceId))
ORDER BY
BASAuditEventHeaders.AuditEventHeaderId,
BASAuditEventDetails.Seque