Orchestrator Runbook for Email Notification for ConfigMgr Software Catalog Requests [2020 Updated]

Estimated reading time: 4 minutes

I’ve had a number of requests about how I created our email notification for requests for software made via our ConfigMgr software catalog. The solution is very simple and very basic, but until I get our Service Manager up and running (which does the job much better..), this is working for us so hopefully will help you to be it if you use it permanently or as a quick fix before you install Service Manager.

You first need to have Orchestrator installed and ready to use, once up and running and configured you need to then create a new Runbook using the settings as below….

Setup the Schedule

The first thing you will want to get setup is a working day schedule…the last thing you want is multiple emails through the night, weekends and holidays.

Go into your Schedules folder, right-click and select New > Schedule

Give the Schedule a name (Working Hours)

In the Details tab, you can now select your working days of the week (mine is Mon-Fri) and for the occurrence, I just select them all!

You can then set you actual working hours by clicking the Hours button…mine is set to 7am-6pm, blue is permitted, white is denied…

You can then also click on the Exceptions tab and add in public holidays and set them to Disallow – I’ve added the public holidays in the UK up until the end of the year.

Create the Runbook

Once you have done this, click Finish and this is the first part complete – why you need to do this and where it will be used will become clear later…

You then need to create a new Runbook. Once you have, we are looking at having a final setup like below:

What this does is monitors the Date/Time, checks the schedule (working hours we just created), runs a database query and then emails, below will explain the configuration contained within all this….

The Monitor Date/Time is how often you want the Runbook to look at the database for information and how often you want this to run. I have mine running every hour at 15 minutes past….

Next, we have the link line to the next part – never underestimate how important these links areas I did when I first started with Runbooks!

This link should be set as below looking for success – basically, if it gets a successful response from the Monitor Date/Time it moves onto the next stage..

Then we move onto the Check Schedule, this is where we add the Working Hours schedule that we setup earlier..

Then we move to the next link line, under the Include tab, you should set as the same as below, this means it continues if it gets a true response from the schedule (if its outside of the working schedule, it’ll go now further..).

SQL Query for Data Check

You then need a Query Database part, under Details, you will need to use the following SQL code:

SELECT
v_UserAppRequests.DisplayName,
v_UserAppRequests.Unique_User_Name0,
v_UserAppRequests.Netbios_Name0,
v_UserAppRequests.Comments,
v_UserAppRequests.LastChanged
FROM v_UserAppRequests
INNER JOIN v_R_System_Valid
ON
v_UserApprequests.Netbios_Name0 = v_R_System_Valid.Netbios_Name0
WHERE (v_UserAppRequests.CurrentState =1)

Remember to also setup your Connection details to your own ConfigMgr SQL database.

Then, again, we move onto the link line. Under the Include tab, make sure that you have it set to the same as below…

Setup the Email Notification

The last bit is completely down to how you want to setup your email. Go into your Check Email part and add the email address you want it set to, how you want the email to be laid out (I’ve created a fancy HTML part for this but you dont have to.

The only part you may want to include is the information from the database, to do this, right click and Subscribe > Publish Data. You then click to click “Full line as a string with fields separated by ‘;’ and click ok, this will add the username, software requested etc to the email.

Remember to configure the Advanced and Connect tabs though or the email will not get sent.

Once complete, start your Runbook and away you go!

Feedback

If you have any questions or feedback on this guide, please feel free to leave us a message below in our comments section.