Home Analyst Portal
Options

report or query of SR / IR support group AD mapping

Mike_StormsMike_Storms Customer Adept IT Monkey ✭✭
Is there a way to get a report or run a query to get display of the mapping tables for SR/IR AD-Group that I could download into a excel spreadsheet?

Comments

  • Options
    R_DelawderR_Delawder Member Adept IT Monkey ✭✭
    the following query should get the support group to AD group mapping from the servicemanagement database for service request class (you can get the other mappings by changing the parent enumeration id to be the id for the other enumeration types (i.e. incident) Select DisplayString as SupportGroup,DisplayName as Adgroup from SupportGroupMapping_CI$DomainGroup_Enumeration inner join DisplayString on SupportGroupMapping_CI$DomainGroup_Enumeration.EnumerationId=DisplayString.ElementID right join CI$DomainGroup on SupportGroupMapping_CI$DomainGroup_Enumeration.DomainGroupId=CI$DomainGroup.Id where CI$DomainGroup.Id in (select DomainGroupId from SupportGroupMapping_CI$DomainGroup_Enumeration) and elementid in (select EnumerationID from Enumeration where ParentEnumerationID = '23c243f6-9365-d46f-dff2-03826e24d228')
  • Options
    Mike_StormsMike_Storms Customer Adept IT Monkey ✭✭
    thanks! where do you get the ParentEnumerationID for the other types?
  • Options
    R_DelawderR_Delawder Member Adept IT Monkey ✭✭
    there are several ways you can get it. the easiest is probably to just query the SM database. to find the them you can use: SELECT EnumTypeId, EnumTypeName FROM EnumType WHERE EnumTypeName like '%incident%' SELECT EnumTypeId, EnumTypeName FROM EnumType WHERE EnumTypeName like '%Servicerequest%' in the results you will get the ID as well as the name. locate the names of your enum for the support group\tier queue. for me names: IR: IncidentTierQueuesEnum SR: ServiceRequestSupportGroupEnum ID: SR is: 23c243f6-9365-d46f-dff2-03826e24d228 IR is: c3264527-a501-029f-6872-31300080b3bf so the 2 queries for me would be. Select DisplayString as SupportGroup,DisplayName as Adgroup from SupportGroupMapping_CI$DomainGroup_Enumeration inner join DisplayString on SupportGroupMapping_CI$DomainGroup_Enumeration.EnumerationId=DisplayString.ElementID right join CI$DomainGroup on SupportGroupMapping_CI$DomainGroup_Enumeration.DomainGroupId=CI$DomainGroup.Id where CI$DomainGroup.Id in (select DomainGroupId from SupportGroupMapping_CI$DomainGroup_Enumeration) and elementid in (select EnumerationID from Enumeration where ParentEnumerationID = '23c243f6-9365-d46f-dff2-03826e24d228') Select DisplayString as SupportGroup,DisplayName as Adgroup from SupportGroupMapping_CI$DomainGroup_Enumeration inner join DisplayString on SupportGroupMapping_CI$DomainGroup_Enumeration.EnumerationId=DisplayString.ElementID right join CI$DomainGroup on SupportGroupMapping_CI$DomainGroup_Enumeration.DomainGroupId=CI$DomainGroup.Id where CI$DomainGroup.Id in (select DomainGroupId from SupportGroupMapping_CI$DomainGroup_Enumeration) and elementid in (select EnumerationID from Enumeration where ParentEnumerationID = 'c3264527-a501-029f-6872-31300080b3bf')
  • Options
    R_DelawderR_Delawder Member Adept IT Monkey ✭✭
    I noticed that my query wasn't getting child groups. (for example if you have a service desk support group with a child group named "password resets" under it) 
    to correct for this i modified the query a little 

    Select DisplayString as SupportGroup,DisplayName as Adgroup 
    from SupportGroupMapping_CI$DomainGroup_Enumeration inner join DisplayString on SupportGroupMapping_CI$DomainGroup_Enumeration.EnumerationId=DisplayString.ElementID 
    right join CI$DomainGroup on SupportGroupMapping_CI$DomainGroup_Enumeration.DomainGroupId=CI$DomainGroup.Id 
    where CI$DomainGroup.Id in (select DomainGroupId from SupportGroupMapping_CI$DomainGroup_Enumeration) 
    and elementid in (select EnumerationID from Enumeration where ParentEnumerationID in (select EnumerationID from Enumeration where ParentEnumerationID = '23c243f6-9365-d46f-dff2-03826e24d228') or ParentEnumerationID = '23c243f6-9365-d46f-dff2-03826e24d228')
Sign In or Register to comment.