Skip to main content

Cherwell

· 32 min read
Pradeep Kadambar
Pradeep Kadambar
Creator
Cherwell Service Management is a cloud-based IT service management solution that helps IT teams in organizations to implement, automate and upgrade service and support processes.

Identities ❌ | Accounts ✔️ | Groups ✔️ | App Roles ❌ | Entitlements ❌
Assumptions

This guide is intended for On Premise deployment mode.

Application​

Steps

  1. Login to RSA G&L console as System Administrator.
  2. Navigate to Resources > Application.
  3. Click on Create Application and select Other Application.
  4. Enter the details as shown below.
    img
  5. Click Finish.

Account Collector​

This section explains the process for configuring an account collector for the Cherwell application. We will use the MySQL database collector for this purpose.

Steps

  1. Login to RSA G&L console as System Administrator.
  2. Navigate to Resources > Applications.
  3. Click on Cherwell.
  4. Navigate to Collectors tab and click on Create Account Collector. Enter the details as shown below.

    img
  5. Click Next and enter the database details. Use the Test Connection to make sure the connection details are accurate.

    img
  6. Click Next. Select the appropriate data that you intend to collect.

    img
  7. Click Next. On the Account Data page, enter the following query to collect account information.
SELECT
  -- Local accounts
  auth.StandardIdentifier as accountName, 
  auth.LastPasswordResetDT as lastPasswordSetDate, 
  auth.AccountLocked as isLocked 
FROM 
  dbo.TrebuchetAuth auth 
WHERE 
  auth.StandardIdentifier <> '' 
UNION 
SELECT
  -- Domain accounts
  CASE WHEN LEFT(auth.WindowsIdentifier, 7) = 'DOMAIN\\' 
  THEN REPLACE(auth.WindowsIdentifier,'DOMAIN\\','') 
  ELSE auth.WindowsIdentifier  
  END as accountName, 
  auth.LastPasswordResetDT as lastPasswordSetDate,
  auth.AccountLocked as isLocked
FROM 
  dbo.TrebuchetAuth auth
WHERE auth.StandardIdentifier <> ''

The following are the important attributes

img

  1. Click Next. On the User Account Mappings Data configuration page enter the following query to map the Cherwell account to user identity.
SELECT
  -- Local accounts
  auth.StandardIdentifier as accountName, 
  auth.StandardIdentifier as userID
FROM 
  dbo.TrebuchetAuth auth 
WHERE 
  auth.StandardIdentifier <> '' 
UNION 
SELECT
  -- Domain accounts
  CASE WHEN LEFT(auth.WindowsIdentifier, 7) = 'DOMAIN\\' 
  THEN REPLACE(auth.WindowsIdentifier,'DOMAIN\\','') 
  ELSE auth.WindowsIdentifier  
  END as accountName,
  auth.StandardIdentifier as userID
FROM 
  dbo.TrebuchetAuth auth
WHERE auth.StandardIdentifier <> ''

The following are the important attributes

img

  1. Click Next. In the Group Data configuration section enter the following query to collect groups from Cherwell.
SELECT 
  -- Local accounts
  auth.StandardIdentifier as accountName, 
  groups.DefName as groupName,
  groups.DefDescription as description,
  groups.DefOwner as owner
FROM 
  dbo.TrebuchetAuth auth,
  dbo.TrebuchetSecGroups groups
WHERE 
  auth.SecurityGroupID = groups.DefID 
  AND auth.StandardIdentifier <> '' 
UNION 
SELECT 
  -- Domain accounts
  CASE WHEN LEFT(auth.WindowsIdentifier, 7) = 'DOMAIN\\' 
  THEN REPLACE(auth.WindowsIdentifier,'DOMAIN\\','') 
  ELSE auth.WindowsIdentifier  
  END as accountName,
  groups.DefName as groupName,
  groups.DefDescription as description,
  groups.DefOwner as owner
FROM 
  dbo.TrebuchetAuth auth,
  dbo.TrebuchetSecGroups groups
WHERE auth.SecurityGroupID = groups.DefID
  AND auth.StandardIdentifier <> ''

The following are the important attributes

img

  1. In the Account Membership Data configuration section enter the following query to collect groups membership information from Cherwell.
SELECT 
  -- Local accounts
  auth.StandardIdentifier as accountName, 
  groups.DefName as groupName
FROM 
  dbo.TrebuchetAuth auth,
  dbo.TrebuchetSecGroups groups
WHERE 
  auth.SecurityGroupID = groups.DefID 
  AND auth.StandardIdentifier <> '' 
UNION 
SELECT 
  -- Domain accounts
  CASE WHEN LEFT(auth.WindowsIdentifier, 7) = 'DOMAIN\\' 
  THEN REPLACE(auth.WindowsIdentifier,'DOMAIN\\','') 
  ELSE auth.WindowsIdentifier  
  END as accountName,
  groups.DefName as groupName
FROM 
  dbo.TrebuchetAuth auth,
  dbo.TrebuchetSecGroups groups
WHERE auth.SecurityGroupID = groups.DefID
  AND auth.StandardIdentifier <> ''
  1. Click Next. In the User Resolution Rules, map the user to account

    img
  2. Click Next. In the Member Account Resolution Rules, map the account collector.

    img
  3. Click Next. In the Group Owner Resolution Rules, map the account collector.

    img
  4. Click Finish. Use the Test function to make sure the configurations are accurate.

Entitlement Collector​

This section explains the process for configuring an entitlement collector to collect application roles from Cherwell application.

Steps

  1. Login to RSA G&L console as AveksaAdmin.
  2. Navigate to Resources > Applications.
  3. Click on Cherwell.
  4. Navigate to Collectors tab and click on Create Entitlement Collector. Enter the details as shown below.

    Alt text
  5. Click Next and enter the database connection details as shown in Step 5 of Account Collector.
  6. Click Next. Select the data types that the collector will be collecting from Cherwell.

    Alt text
  7. Click Next. On the Column Names, map the attribute names.

    Alt text
  8. Click Next. Under Application Role Data section, use the following query to collect app roles from Cherwell.
SELECT 
  dbo.TrebuchetAuth.StandardIdentifier AS Identifier, 
  dbo.TrebuchetSecGroups.DefName 
FROM 
  [dbo].[TrebuchetAuth], 
  [dbo].[TrebuchetSecGroups] 
WHERE 
  dbo.TrebuchetAuth.SecurityGroupID = [dbo].[TrebuchetSecGroups].DefID 
  AND dbo.TrebuchetAuth.StandardIdentifier <> '' 
UNION 
SELECT 
  CASE WHEN left(dbo.TrebuchetAuth.WindowsIdentifier, 5) = 'dom1\\' 
    THEN replace(dbo.TrebuchetAuth.WindowsIdentifier,' dom1\ ','') 
    ELSE dbo.TrebuchetAuth.WindowsIdentifier 
  END AS Identifier, 
  dbo.TrebuchetSecGroups.DefName FROM [dbo].[TrebuchetAuth] ,[dbo].[TrebuchetSecGroups]
WHERE dbo.TrebuchetAuth.SecurityGroupID = [dbo].[TrebuchetSecGroups].DefID
AND dbo.TrebuchetAuth.WindowsIdentifier <> ''

The following are the important attributes

img

  1. Click Next. Under Resource-Action Entitlements Data section, use the following query to collect resource action mapping from Cherwell.
SELECT 
  dbo.TrebuchetAuth.StandardIdentifier AS Identifier, 
  dbo.TrebuchetSecGroups.DefName 
FROM 
  [dbo].[TrebuchetAuth], 
  [dbo].[TrebuchetSecGroups] 
WHERE 
  dbo.TrebuchetAuth.SecurityGroupID = [dbo].[TrebuchetSecGroups].DefID 
  AND dbo.TrebuchetAuth.StandardIdentifier <> '' 
UNION 
SELECT 
  CASE WHEN left(dbo.TrebuchetAuth.WindowsIdentifier, 5) = 'dom1\\' 
    THEN replace(dbo.TrebuchetAuth.WindowsIdentifier,' dom1\ ','') 
    ELSE dbo.TrebuchetAuth.WindowsIdentifier 
  END AS Identifier, 
  dbo.TrebuchetSecGroups.DefName FROM [dbo].[TrebuchetAuth] ,[dbo].[TrebuchetSecGroups]
WHERE dbo.TrebuchetAuth.SecurityGroupID = [dbo].[TrebuchetSecGroups].DefID
AND dbo.TrebuchetAuth.WindowsIdentifier <> ''
  1. Click Next. On the Account Data section, enter the query below to collect mappings for account to admin roles.
SELECT 
  dbo.TrebuchetAuth.StandardIdentifier AS Identifier, 
  dbo.TrebuchetSecGroups.DefName 
FROM 
  [dbo].[TrebuchetAuth], 
  [dbo].[TrebuchetSecGroups] 
WHERE 
  dbo.TrebuchetAuth.SecurityGroupID = [dbo].[TrebuchetSecGroups].DefID 
  AND dbo.TrebuchetAuth.StandardIdentifier <> '' 
UNION 
SELECT 
  CASE WHEN left(dbo.TrebuchetAuth.WindowsIdentifier,5) = 'dom1\\' 
    THEN replace(dbo.TrebuchetAuth.WindowsIdentifier,' dom1\ ','') 
    ELSE dbo.TrebuchetAuth.WindowsIdentifier 
  END AS Identifier, 
  dbo.TrebuchetSecGroups.DefName FROM [dbo].[TrebuchetAuth] ,[dbo].[TrebuchetSecGroups]
WHERE dbo.TrebuchetAuth.SecurityGroupID = [dbo].[TrebuchetSecGroups].DefID
AND dbo.TrebuchetAuth.WindowsIdentifier <> ''
  1. Click Next. In the User Evaluation, map the account to the ADC.

    img
  2. Click Next and then click Finish. Use the Test function to make sure the configurations are accurate.
COL021