Using a custom list of Reviewers

Suleyman_Ozden

Hi all,

I am looking for a way to create and use a reviewer list based on who is head of a certain department.

I need this to enable automation for certain types of requests that need approval. In total there are 17-18 approovers.

Initially I was told the line-in manager would be approver and we created a runbook to add a review activity for the requests that needed approval (the check mark for "line manager should review" was set to true).  However the runbook was disabled again, since line in manager could NOT be approver.

I am not sure how to accomplish the automation with a custom list of approovers. "Line Manager should review" is a built-in SCSM setting that just requires the manager field in AD to have been populated. What I need is to set a reviewer based on the Affected users department attribute (or part of) in Active Directory.

So if you are a affected user from Marketing department, the reviewer four your request will be whoever is the boss for that department.

Geoff_Ross

Hi Suleyman,

There's a couple of options here. The best method in my opinion is to lookup Department managers in your organisations HR system or other. That way it will always be accurate as manages change.

Assuming a SQL backend that you can access, run a PowerShell runbook with a SQL query along the lines of

SELECT ManagerID
FROM Departments
WHERE Department = @DepartmentName

You can then look up that User's SCSM User object with

Get-SCSMObject -Class (Get-SCSMClass Microsoft.AD.User$) -Filter "EmployeeId -eq $ManagerID"

and make that user a reviewer on the Review Activity.

If that is not possible, another way would be to extend the SCSM User class with a Boolean for IsDepartmentManager and set this to true for all Department Managers. Then you can lookup an SCSM User object with the Department equal to the Users Department and IsDepartmentManager equals true.

You would need to ensure that only one person for each department has that set to true otherwise you may get issues.

Hope this helps, good luck,

Geoff

Leigh_Kilday

Suleyman_Ozden said:

Hi Geoff and Leigh. Thank you very much for your proposals. I am afraid it looks like I have to maintain a list manually.

I am imagining a simple .csv file, like Leaigh is talking about, simply listing each of the departments and the deparment manager for each. I havent done much automation myself so far, so I am looking for a way to do this. How do I add the correct reviewer to a review activity based on a manually created list of department managers?

Should be done trough Orchestrator right?

Sorry I can't export the runbook file, but I can write out the steps.

Our CSV files are structured like so:
Lookup | Domain | Username

Below are the steps in my Approver Lookup Orchestrator runbook. You'll find all of the activities in the Utilities, Text File Management, Data Manipulation and SCSM Integration Packs.

1. Initialize Data
   Parameters: RB_ID, Lookup (the key of the CSV table), RATitleMatch (so you know which RA to add the approver to), ConfigFile (we have multiple CSVs for different approval purposes)
2. Get RB [Get SCSM Object]
   Runbook Activity
   Use the RB_ID parameter
3. Get Parent WI [Invoke Runbook]
   This separate runbook is a simple PowerShell script to get the parent WI recursively until you have a top-level parent
4. Get ACs [Invoke Runbook]
   Same as above, but it goes in the other direction
5. Filtered link - Include - RA_Found from Get ACs equals True
6. Split AC GUIDs [Expand Fields]
   Output of #4 is delimited GUID string so we need to load them into fields
   
7. Get RAs [Get SCSM Object]
8. Match input to title [Compare Values]
   Title from "Get RAs" matches the pattern *RATitleMatch from "Initialize Data"*
9. Filtered link - Include - Comparison result from Match input to RA title equals True
10. Match input to CSV [Find Text]
    File: Your CSV on a fileshare somewhere. Best to build this UNC with a variable in case you move it
    Search text: Lookup from "Initialize Data"
    Result: All lines that match the text will be returned
11. Filtered link - Include - Match start from Match input to CSV equals 0, Exclude - Number of lines matched from Match input to CSV equals 0
12. Split fields [Split Fields]
    Input String: Original line from "Match input to CSV"
    Split Delimiter: ,
13. Get approvers [Get SCSM Object]
    Active Directory User
    However you've structured your user data on the CSV will change how this is configured. We are using domain and username columns so it looks like:
    Domain equals Field02
    Username equals Field03
14. Filtered link - Include - Number of objects from Get approvers is greater than 0
    
15. Create reviewer object [Create Related SCSM Object]
    Review Activity
16. Relate RA-Reviewer [Create SCSM Relationship]
    Source: Target Object Guid from "Create reviewer object"
    Target: SC Object Guid from "Get Approvers"
17. Complete RB [Update SCSM Object]
    This one just speeds up the SR's workflow

I have another link off #10
Filtered link - Include - Match input to CSV returns failed or Number of lines matched from Match input to CSV equals 0.
And another off #13
Filtered link - Include - Number of objects from Get approvers equals 0.

Both of these link to steps to create an action log entry, get the affected user of the SR and create an IR that is assigned to our team.

Note that the runbook will fail if somebody has the CSV file open (file lock).

I had always intended to replace the CSV with Organization CIs and each approver is the owner/custodian or whatever it is. This would mean there are no file locks, all data is stored within SCSM and you can easily write PowerShell scripts to update the manager - this could be triggered by a Service Request.

Geoff_Ross

Hi Suleyman,

There's a couple of options here. The best method in my opinion is to lookup Department managers in your organisations HR system or other. That way it will always be accurate as manages change.

Assuming a SQL backend that you can access, run a PowerShell runbook with a SQL query along the lines of

SELECT ManagerID
FROM Departments
WHERE Department = @DepartmentName

You can then look up that User's SCSM User object with

Get-SCSMObject -Class (Get-SCSMClass Microsoft.AD.User$) -Filter "EmployeeId -eq $ManagerID"

and make that user a reviewer on the Review Activity.

If that is not possible, another way would be to extend the SCSM User class with a Boolean for IsDepartmentManager and set this to true for all Department Managers. Then you can lookup an SCSM User object with the Department equal to the Users Department and IsDepartmentManager equals true.

You would need to ensure that only one person for each department has that set to true otherwise you may get issues.

Hope this helps, good luck,

Geoff

Leigh_Kilday

Using Orchestrator/PowerShell we're looking up external CSV files for this sort of thing. It's not an ideal solution but it's all we've had time for so far.

Suleyman_Ozden

Hi Geoff and Leigh. Thank you very much for your proposals. I am afraid it looks like I have to maintain a list manually.

I am imagining a simple .csv file, like Leaigh is talking about, simply listing each of the departments and the deparment manager for each. I havent done much automation myself so far, so I am looking for a way to do this. How do I add the correct reviewer to a review activity based on a manually created list of department managers?

Should be done trough Orchestrator right?

Adam_Dzyacky

Just some Active Directory ideas should they help (it pains me to hear "manual"):

• Department heads - PowerShell to get the highest reporting manager in single OU (assuming you have managers and their direct reports in a single OU). If you don't, you'll have to get more creative if they share some of the same Title or of course just turn to the Department attribute on the user record
• SCSM Custodians - PowerShell to get the "Managed By" object in AD in select cases

You could do this through Orchestrator, the alternative being SMA.

Leigh_Kilday

Suleyman_Ozden said:

Hi Geoff and Leigh. Thank you very much for your proposals. I am afraid it looks like I have to maintain a list manually.

I am imagining a simple .csv file, like Leaigh is talking about, simply listing each of the departments and the deparment manager for each. I havent done much automation myself so far, so I am looking for a way to do this. How do I add the correct reviewer to a review activity based on a manually created list of department managers?

Should be done trough Orchestrator right?

Sorry I can't export the runbook file, but I can write out the steps.

Our CSV files are structured like so:
Lookup | Domain | Username

Below are the steps in my Approver Lookup Orchestrator runbook. You'll find all of the activities in the Utilities, Text File Management, Data Manipulation and SCSM Integration Packs.

1. Initialize Data
   Parameters: RB_ID, Lookup (the key of the CSV table), RATitleMatch (so you know which RA to add the approver to), ConfigFile (we have multiple CSVs for different approval purposes)
2. Get RB [Get SCSM Object]
   Runbook Activity
   Use the RB_ID parameter
3. Get Parent WI [Invoke Runbook]
   This separate runbook is a simple PowerShell script to get the parent WI recursively until you have a top-level parent
4. Get ACs [Invoke Runbook]
   Same as above, but it goes in the other direction
5. Filtered link - Include - RA_Found from Get ACs equals True
6. Split AC GUIDs [Expand Fields]
   Output of #4 is delimited GUID string so we need to load them into fields
   
7. Get RAs [Get SCSM Object]
8. Match input to title [Compare Values]
   Title from "Get RAs" matches the pattern *RATitleMatch from "Initialize Data"*
9. Filtered link - Include - Comparison result from Match input to RA title equals True
10. Match input to CSV [Find Text]
    File: Your CSV on a fileshare somewhere. Best to build this UNC with a variable in case you move it
    Search text: Lookup from "Initialize Data"
    Result: All lines that match the text will be returned
11. Filtered link - Include - Match start from Match input to CSV equals 0, Exclude - Number of lines matched from Match input to CSV equals 0
12. Split fields [Split Fields]
    Input String: Original line from "Match input to CSV"
    Split Delimiter: ,
13. Get approvers [Get SCSM Object]
    Active Directory User
    However you've structured your user data on the CSV will change how this is configured. We are using domain and username columns so it looks like:
    Domain equals Field02
    Username equals Field03
14. Filtered link - Include - Number of objects from Get approvers is greater than 0
    
15. Create reviewer object [Create Related SCSM Object]
    Review Activity
16. Relate RA-Reviewer [Create SCSM Relationship]
    Source: Target Object Guid from "Create reviewer object"
    Target: SC Object Guid from "Get Approvers"
17. Complete RB [Update SCSM Object]
    This one just speeds up the SR's workflow

I have another link off #10
Filtered link - Include - Match input to CSV returns failed or Number of lines matched from Match input to CSV equals 0.
And another off #13
Filtered link - Include - Number of objects from Get approvers equals 0.

Both of these link to steps to create an action log entry, get the affected user of the SR and create an IR that is assigned to our team.

Note that the runbook will fail if somebody has the CSV file open (file lock).

I had always intended to replace the CSV with Organization CIs and each approver is the owner/custodian or whatever it is. This would mean there are no file locks, all data is stored within SCSM and you can easily write PowerShell scripts to update the manager - this could be triggered by a Service Request.

Suleyman_Ozden

Adam_Dzyacky said:

Just some Active Directory ideas should they help (it pains me to hear "manual"):

• Department heads - PowerShell to get the highest reporting manager in single OU (assuming you have managers and their direct reports in a single OU). If you don't, you'll have to get more creative if they share some of the same Title or of course just turn to the Department attribute on the user record
• SCSM Custodians - PowerShell to get the "Managed By" object in AD in select cases

You could do this through Orchestrator, the alternative being SMA.

Hi Adam.

All users are in the same OU. I can't use the "Managed By" as it will not be the line in manager that is going to be approover. Approover will be a head of department. If I do not have a source to pull the correct approovers I need, I see no other option than to create them manually.

Suleyman_Ozden

Leigh_Kilday said:

Suleyman_Ozden said:

Hi Geoff and Leigh. Thank you very much for your proposals. I am afraid it looks like I have to maintain a list manually.

I am imagining a simple .csv file, like Leaigh is talking about, simply listing each of the departments and the deparment manager for each. I havent done much automation myself so far, so I am looking for a way to do this. How do I add the correct reviewer to a review activity based on a manually created list of department managers?

Should be done trough Orchestrator right?

Sorry I can't export the runbook file, but I can write out the steps.

Our CSV files are structured like so:
Lookup | Domain | Username

Below are the steps in my Approver Lookup Orchestrator runbook. You'll find all of the activities in the Utilities, Text File Management, Data Manipulation and SCSM Integration Packs.

1. Initialize Data
   Parameters: RB_ID, Lookup (the key of the CSV table), RATitleMatch (so you know which RA to add the approver to), ConfigFile (we have multiple CSVs for different approval purposes)
2. Get RB [Get SCSM Object]
   Runbook Activity
   Use the RB_ID parameter
3. Get Parent WI [Invoke Runbook]
   This separate runbook is a simple PowerShell script to get the parent WI recursively until you have a top-level parent
4. Get ACs [Invoke Runbook]
   Same as above, but it goes in the other direction
5. Filtered link - Include - RA_Found from Get ACs equals True
6. Split AC GUIDs [Expand Fields]
   Output of #4 is delimited GUID string so we need to load them into fields
   
7. Get RAs [Get SCSM Object]
8. Match input to title [Compare Values]
   Title from "Get RAs" matches the pattern *RATitleMatch from "Initialize Data"*
9. Filtered link - Include - Comparison result from Match input to RA title equals True
10. Match input to CSV [Find Text]
    File: Your CSV on a fileshare somewhere. Best to build this UNC with a variable in case you move it
    Search text: Lookup from "Initialize Data"
    Result: All lines that match the text will be returned
11. Filtered link - Include - Match start from Match input to CSV equals 0, Exclude - Number of lines matched from Match input to CSV equals 0
12. Split fields [Split Fields]
    Input String: Original line from "Match input to CSV"
    Split Delimiter: ,
13. Get approvers [Get SCSM Object]
    Active Directory User
    However you've structured your user data on the CSV will change how this is configured. We are using domain and username columns so it looks like:
    Domain equals Field02
    Username equals Field03
14. Filtered link - Include - Number of objects from Get approvers is greater than 0
    
15. Create reviewer object [Create Related SCSM Object]
    Review Activity
16. Relate RA-Reviewer [Create SCSM Relationship]
    Source: Target Object Guid from "Create reviewer object"
    Target: SC Object Guid from "Get Approvers"
17. Complete RB [Update SCSM Object]
    This one just speeds up the SR's workflow

I have another link off #10
Filtered link - Include - Match input to CSV returns failed or Number of lines matched from Match input to CSV equals 0.
And another off #13
Filtered link - Include - Number of objects from Get approvers equals 0.

Both of these link to steps to create an action log entry, get the affected user of the SR and create an IR that is assigned to our team.

Note that the runbook will fail if somebody has the CSV file open (file lock).

I had always intended to replace the CSV with Organization CIs and each approver is the owner/custodian or whatever it is. This would mean there are no file locks, all data is stored within SCSM and you can easily write PowerShell scripts to update the manager - this could be triggered by a Service Request.

Thank you very much for this. I am very new to working with Orchestrator, but I hope I will soon find time to have a look at it.

Leigh_Kilday

Suleyman_Ozden said:

Leigh_Kilday said:

Suleyman_Ozden said:

Hi Geoff and Leigh. Thank you very much for your proposals. I am afraid it looks like I have to maintain a list manually.

I am imagining a simple .csv file, like Leaigh is talking about, simply listing each of the departments and the deparment manager for each. I havent done much automation myself so far, so I am looking for a way to do this. How do I add the correct reviewer to a review activity based on a manually created list of department managers?

Should be done trough Orchestrator right?

Sorry I can't export the runbook file, but I can write out the steps.

Our CSV files are structured like so:
Lookup | Domain | Username

Below are the steps in my Approver Lookup Orchestrator runbook. You'll find all of the activities in the Utilities, Text File Management, Data Manipulation and SCSM Integration Packs.

1. Initialize Data
   Parameters: RB_ID, Lookup (the key of the CSV table), RATitleMatch (so you know which RA to add the approver to), ConfigFile (we have multiple CSVs for different approval purposes)
2. Get RB [Get SCSM Object]
   Runbook Activity
   Use the RB_ID parameter
3. Get Parent WI [Invoke Runbook]
   This separate runbook is a simple PowerShell script to get the parent WI recursively until you have a top-level parent
4. Get ACs [Invoke Runbook]
   Same as above, but it goes in the other direction
5. Filtered link - Include - RA_Found from Get ACs equals True
6. Split AC GUIDs [Expand Fields]
   Output of #4 is delimited GUID string so we need to load them into fields
   
7. Get RAs [Get SCSM Object]
8. Match input to title [Compare Values]
   Title from "Get RAs" matches the pattern *RATitleMatch from "Initialize Data"*
9. Filtered link - Include - Comparison result from Match input to RA title equals True
10. Match input to CSV [Find Text]
    File: Your CSV on a fileshare somewhere. Best to build this UNC with a variable in case you move it
    Search text: Lookup from "Initialize Data"
    Result: All lines that match the text will be returned
11. Filtered link - Include - Match start from Match input to CSV equals 0, Exclude - Number of lines matched from Match input to CSV equals 0
12. Split fields [Split Fields]
    Input String: Original line from "Match input to CSV"
    Split Delimiter: ,
13. Get approvers [Get SCSM Object]
    Active Directory User
    However you've structured your user data on the CSV will change how this is configured. We are using domain and username columns so it looks like:
    Domain equals Field02
    Username equals Field03
14. Filtered link - Include - Number of objects from Get approvers is greater than 0
    
15. Create reviewer object [Create Related SCSM Object]
    Review Activity
16. Relate RA-Reviewer [Create SCSM Relationship]
    Source: Target Object Guid from "Create reviewer object"
    Target: SC Object Guid from "Get Approvers"
17. Complete RB [Update SCSM Object]
    This one just speeds up the SR's workflow

I have another link off #10
Filtered link - Include - Match input to CSV returns failed or Number of lines matched from Match input to CSV equals 0.
And another off #13
Filtered link - Include - Number of objects from Get approvers equals 0.

Both of these link to steps to create an action log entry, get the affected user of the SR and create an IR that is assigned to our team.

Note that the runbook will fail if somebody has the CSV file open (file lock).

I had always intended to replace the CSV with Organization CIs and each approver is the owner/custodian or whatever it is. This would mean there are no file locks, all data is stored within SCSM and you can easily write PowerShell scripts to update the manager - this could be triggered by a Service Request.

Thank you very much for this. I am very new to working with Orchestrator, but I hope I will soon find time to have a look at it.

No probs. Let me know if anything needs more explanation.