SQL Script to pull the Group Name and respective members of the Group

SELECT b.GroupName, c.[DisplayName]
FROM dbo.BASUserGroupMembershipV a left join dbo.BASGroupNamesV b on a.[GroupId] = b.[GroupId]
                                                      left join dbo.UserConfig c on a.UserConfigId = c.[Id]
                                                      Where a.GroupId = '4'
UNION
SELECT b.GroupName, c.[DisplayName]
FROM dbo.BASUserGroupMembershipV a left join dbo.BASGroupNamesV b on a.[GroupId] = b.[GroupId]
                                                      left join dbo.UserConfig c on a.UserConfigId = c.[Id]
                                                      Where a.GroupId = '5'

SQL Script to pull Role Name and Role ID

Login to the SQL Server and expand the databases and click the database.

Click New Query & Paste the below query

SELECT  Name, NameAndDescriptionInstanceId as 'RoleId' 
      FROM BASLocaleNamesAndDescriptions 
      WHERE NameAndDescriptionTypeId = 8 
      AND PlugInId = 0
      AND Locale = 'en_US'

Click Execute

From the results you can copy the information of Role Name and Role Id.

SQL Script to pull Group Name and Group ID

Login to the SQL Server and expand the databases and click the database.

Click New Query & Paste the below query

SELECT  Name, NameAndDescriptionInstanceId as 'GroupId' 
      FROM BASLocaleNamesAndDescriptions 
      WHERE NameAndDescriptionTypeId = 7 
      AND PlugInId = 0
      AND Locale = 'en_US'

Click Execute

From the results you can copy the information of Group Name and Group Id.

BAS Audit SQL Script

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;

Administrator user who has permission on group is unable to manage the users from BAS Console

I will summarize the entire issue, resolution and analysis for this post.

Issue: 
Administrator user who has permissions to manage the users were unable to manage the group.

Resolution: 
Group is missing in the BAS Console on which the role is assigned for the administrator user to manage the users. Quickly new group with the same name has been created and the users have associated with the newly created group

In order to add bulk users to the group, you can achieve if you follow the below steps

1. Login to the Blackberry Administration Service Console
2. Click Manage Groups
3. Click the Group for which bulk users (who are existing on BAS Console) needs to be added
4. Click Add users to group membership (Group > Manage Groups > View Group)
5. Click Import users from a list
6. Browse the csv file and click OK ( Note: Csv file should have the fields User Id, Display Name, PIN, Email Address)
7. Click Add to Group membership

If you are not sure how to get the user id and PIN, you can export the entire user list from which you can get the required details to fill the csv file.

I have referred the KB19858 for the above task.

This will fix the issue.

Analysis to understand how the issue caused:

Role has been assigned to the administrator user to manage the group with few restrictions like they won't be having permissions to delete the group. So, other administrators who have full rights on the entire blackberry solution like Security Administrators role.

But now the challenge is to find the administrator user who deleted the group.

To track the changes i have referred KB19251 which has the script which you can download and audit the changes. However after i have downloaded the script which didn't gave desired result. So, i have worked and modified the script to get the desired result. I will post the script information in my next blog.

You need to login to the SQL server which the blackberry servers are pointing.

1. Open Microsoft SQL Server Management Studio
2. Expand Databases and click the BESMgmt (Principal, Synchronized)
3. Click New Query
4. Paste the SQL script
5. Click Execute
6. Copy and paste the results and search for Delete and Group attributes and then you will get the administrator user with the below entities

AuditEventHeaderId	BAS User Id	BAS Display Name	Change Date Time	Type Id	Type Text	Reference Id	Action Code	Field Description	Old Value
57438	27	****	11/25/2013 16:27	14	GROUP	13	Delete	Group identifier	13

Finally you got the administrator user who has deleted the group.... :)

Administrator User cannot modify users from Lync Control Panel

Administrator user is unable to modify users from Lync Control Panel and receiving below error

"Active Directory operation failed on "***.contoso.com". You cannot retry this operation:
"Insufficient access rights to perform the operation
00002098 SecErr: DSID-031150889 problem 4003 INSUFF ACCESS RIGHTS) data 0

To resolve the problem, follow the below steps

Check the administrator user in which security group for which Lync Delegation has been provided
Go to user AD properties -> Security Tab -> Advance -> Permission -> Check the unchecked box "Include inheritable permissions from this object's parent.

After performing the above steps, issue got fixed.

Lync Delegation for Management and Administration for users on OU

Create a Universal Security Group in Active Directory with name "BLR-LyncUserAdmins"

RBAC roles used to define the management tasks that users allowed to carry out, and to determine the scope in which users will be allowed to perform these tasks.

Perform the following command from Lync Management Shell:

New-CsAdminRole -Identity "BLR-LyncUserAdmins" -ConfigScopes "site:1" -UserScopes "OU:ou=Users,dc=nl,dc=Contoso,dc=com" -Template CSUserAdministrator

BLR-LyncUserAdmins:- Name of Universal Security Group should be created by Windows Administrator. Please check if group has been created before running the above command.

CSUserAdministrator: - Administrator Security group that provide required permission to perform activities. Command will automatically create this group.

Get-CsAdminRole BLR-LyncUserAdmins (After running this command it will show below information)

Identity: BLR-LyncUserAdmins
SID: ******
IsStandardRole: False
Cmdlets: {Name=Disable-CSUser, Name=Enable-CSUser, Name=Get-CSAdUser, Name=Get-CSUser.....}
ConfigScopes: {Site:1}
UserScopes: {OU:ou=Users,dc=Contoso,dc=com}
Template: CSUserAdministrator

Lync Monitoring Server Error cannot impersonate user for data source

After i got access to Lync Infrastructure, i tried to access the monitoring reports dashboard by clicking 1-MonitoringServer-3 from Lync Control Panel

I received the below error

To fix the issue, i have verified the settings in CDRDB and QMSDB for credentials stored securely in the report server and found it is having another administrator details who left the organization. I have provided my user account details and clicked apply.

In order to modify the settings for those data sources we should access the reports configuration as below
http://monitoringServeFQDN/reports

After the modification of user details and credentials stored securely in the report server, i am able to successfully open the Dashboard of Monitoring Server Reports.