Video Screencast Help

ServiceDesk VIP Field Set Automatically from AD Group

Created: 28 Aug 2013 • Updated: 10 Dec 2013
Language Translations
MarkWarmack's picture
+1 1 Vote
Login to vote

Here is an automatic way to mark Affected Users with VIP field in ServiceDesk 7.5, with these caveats:

  • We have one team that creates and maintains Active Directory (AD) user accounts. 
  • We also have an easy rule on who gets a VIP flag (CEO and two levels of reports) so it is easy looking on Organizational Chart to determine who should be a member of the AD Security Group.

Building on one process that can be accomplished in Helpdesk v6:
http://www.symantec.com/connect/articles/give-your-companys-upper-management-vip-helpdesk-status-they-deserve.

HOW WE DID IT:

1. Create an Active Directory (AD) Security Group  (for this example, the name is SymantecServiceDeskVIPs)

2. Microsoft Sync and import Groups in ServiceDesk from specific OU where new Security Group was created.
 ServiceDesk > Admin > Active Directory > Sync Profiles > YOUR AD Sync Profile

3. Find the group and verify the users are in the AD Group that has been imported into ServiceDesk.
 ServiceDesk > Admin > Users > Accounts . List Groups > search for name: SymantecServiceDeskVIPs

NOTE:  Click 'Edit Group' on SymantecServiceDeskVIPs > copy "Group ID" GUID.  You will need this later.

4. On your SMP, Create an Automation Policy (Manage > Policies > Automation Policies)

 Name:  ServiceDesk Set VIP from AD Group Policy
 Schedule:  Repeat every 1 day (time scheduled AFTER AD import runs)
 Data Source:  Raw SQL Query

              USE processmanager75  --(OR WHATEVER YOU CALLED YOUR SERVICEDESK DATABASE)
              UPDATE [User]
             SET [User].VIP = 1
             FROM processmanager75.dbo.[User]
             WHERE [UserID] IN
             (SELECT U.[userid] FROM [UserReferenceIDLookup] UR
             left join processmanager75.dbo.[User] U
             on UR.[UserID] = U.[userid]
             WHERE UR.[ReferenceID] = 'e06e07f8-04f5-11e3-b407-005056a45ffe') --YOURDOMAIN\SymantecServiceDeskVIPs GUID

5. Login to your ServiceDesk, click on Reports, create new report and use this SQL:
ServiceDesk > More > Reports > Incident Management folder > Add Report icon in the top right.

 Name:  VIP Users List
 On User table, select fields: First, Last, DisplayName, VIP.

ServiceDeskVIP2.jpg

This is what the auto generated report SQL looks like:
             select
                 UserTable.FirstName as [UserTable.FirstName],
                 UserTable.LastName as [UserTable.LastName],
                 UserTable.DisplayName as [UserTable.DisplayName],
                 UserTable.VIP as [UserTable.VIP]
             from
                 [User] as UserTable with (NOLOCK) left outer join
                 UserAddress as UserAddress0 with (NOLOCK) on
                     ((UserTable.UserID = UserAddress0.UserID))
             where
                 ('3DCA0263-D5EF-48ae-BF96-A161989DFB8E' = '3DCA0263-D5EF-48ae-BF96-A161989DFB8E' and
                 UserTable.AccountActive = 'True' and
                 UserTable.VIP = 'True' and
                 UserTable.DisplayName like '%%')
             order by
                 [UserTable.VIP] asc

NOTE:  When you run the report, it should match the number you viewed in the imported Security Group.

ServiceDeskVIP1.jpg

6. As a bonus, we were also worried about someone editing a user or creating a manual user and making them VIP without 
anyone knowing.  So we created another Automation Policy to take *away* VIP status *IF NOT* in the AD Security.

7. On your SMP, create a second Automation Policy (Manage > Policies > Automation Policies)

 Name: ServiceDesk Remove VIP field if NOT in Security Group
 Schedule:  Repeat every 1 day (time scheduled AFTER AD import runs)
 Data Source:  Raw SQL Query

             USE processmanager75        --(OR WHATEVER YOU CALLED YOUR SERVICEDESK DATABASE)
             UPDATE [User] SET [User].VIP = 0
             FROM processmanager75.dbo.[User]
             WHERE [UserID] NOT IN
             (SELECT U.[userid] FROM [UserReferenceIDLookup] UR
             left join processmanager75.dbo.[User] U
             on UR.[UserID] = U.[userid]
             WHERE UR.[ReferenceID] = 'e06e07f8-04f5-11e3-b407-005056a45ffe') --YOURDOMAIN\SymantecServiceDeskVIPs GUID

 

Hope this article helps take one small process off your plate....