Monday, June 29, 2015

Hello Affected User - I Fixed Your Incident is it OK if I resolve? Hello? Are You There? Anyone?!

9:38 PM Posted by A , 1 comment

I'm not even going to waste time with an introduction, let's just get to the scenario.

You're an incredibly customer service oriented manager/individual/analyst. You follow up with all of your Affected User's once you've resolved their Incident. But before you mark it as "Resolved" in Service Manager you may want their sign-off either via Email or verbal. As such, you may spend an unreasonable amount of time trying to get a hold of the Affected User once you've solved their issue for said confirmation. 

Now I know what some of you are thinking, "Whatever. I'm marking the Incident as Resolved and moving on. I'm not going to waste my time on contacting them. They'll call back if they are really having an issue." Which, sure. That is a valid approach. The cynic in me absolutely agrees with you. However the half of me obsessed with customer service says otherwise. Because if anything, I feel as though I'm being arrogant in my "resolution" I'm assuming that since the Affected User isn't talking I can just mark it as resolved. I really want to show the employee community a lot of love and care. After all they are my customers and I do have to work with them. Plus, I especially don't like this from a reporting stand point either. If anything I'm building some "false" data. Sure I could select the Resolution Category as maybe something more custom like "No Response from Employee" but that the only puts further onus on analyst making a new correct decision and not possibly skewing data again. Blehhh...

Ok, here's my idea. Create a new Status like "Pending User Confirmation to Resolve" underneath your "Pending" status (sure it's a bit wordy, but I'm just trying to be descriptive here). Once an Incident is set to that status, have Orchestrator scour Incidents every day at some specific time in that specific status daily looking to see the last person to update the Incident. If it's anyone but the Affected User, send the Affected User an email asking if the Incident can be resolved. After 5 days of inactivity, begin emailing the Affected User and the Affected User's manager (as defined by Active Directory). Perhaps after 10 days of inactivity, it goes to the Affected User, the Affected User's manager, and then their manager or maybe your IT department's Service Desk manager, etc. etc..

Wow. That seems a bit email spammy. I mean...I get it...but...hrm.

Make no mistake, I'm not saying you should do this. I'm not saying you should try to convince your organization adopt this policy. But what I am saying is "Here's exactly how to do this with Orchestrator and PowerShell if you were the least bit curious so follow along and modify to your needs if you'd like!"

Step 1 - Create the Orchestrator Runbook
As per usual, I'm the first to admit that I'd rather do everything with PowerShell. I know Orchestrator is awesome n all, but the "looping" of activities based on how many times the last one just gets annoying sometimes. Not only that, this is a scenario where that is going to happen a lot and an unknown amount of times. So I'll do what I can in Orchestrator and then off to PowerShell!

 As I said, the idea here is that...
  1. Everyday at 10am
  2. Get All Incidents with a status of "Pending User Confirmation"
  3. Get the Relationship between the Incident and the Affected user
  4. Get the Affected User
  5. Get the Relationship between the Incident and the Assigned To User
  6. Get the Assigned To User
  7. Create a text file on the Orchestrator server in something like c:\scsm\automation\requestResolutionConfirmation\$irNumber-$affectedUserUserName
  8. Get the Relationship between the Incident and the Trouble Ticket Action Log
  9. As long as the Action Log has items...
  10. Get the Entire Trouble Ticket Action Log
  11. Format the Trouble Ticket Action Log "Entered Date" to a friendlier format like "05-21-2015"
  12. Append lines to the text files that were created using commas to separate values. Each line will look like...
    1. EnteredDate,LogGUID,EnteredBy,AssignedToUserName
      1. You don't have to use the GUID, but I am for the sake of building so you can see you are in fact generating unique Action Log items within the entirety of the Action Log text file. Not to mention, you'll just ignore them in the downstream PowerShell parsing.
  13. Using a PowerShell script file, we're going to...
    1. Read the entire directory of Action Logs
    2. Sort the Action Log's text file's contents by most recently entered date (when they get appended they aren't in order. I can't figure out the reason why Orchestrator picks the order it does). When sorted the most recently entered item will be the first line of the text file.
    3. Parse each Action Log text file's first line (which would be the most recent Action Log item and will get you the last entered date and who entered it)
    4. Use a Get-ADPrincipalGroupMember to enumerate the Enter By user's groups and figure out if any of them are a match to a SCSM analyst group as defined in your Active Directory
      1. If they are (which means the last comment was left by an analyst) then email the Affected User
  14. Finally, delete all the text files created several steps ago
Alright. Lay out your Activities
Get all the activities out on your runbook canvas and wire them up in the following order like the above picture
1. Monitor Date/Time
    a. optional: Check Schedule
2. Get Object
3. Get Relationship
4. Get Object
5. Get Relationship
6. Get Object
7. Run .NET Script (PowerShell)
8. Get Relationship
9. Get Object
10. Format Date/Time
11. Append Line
12. Run .NET Script
13. Delete File
Deep Breath. Build Time.
So the first activity is the Monitor Date/Time object and I've set it to 10am. That way every day this runbook will kickoff at said time. Simple.
I don't want every day. I just need Monday through Friday or some custom defined set of days for my organization.

No problem and fair point! If you want to do this, we'll need to make the step immediately following this a Check Schedule activity. This activity will then validate if it's running within an acceptable time frame. If you're new to Orchestrator...yes. This does seem like a very roundabout way of scheduling. Just keep spending time in the product and this seeming disconnect of activities will make a great deal more sense.

Optional Steps, Adding a Check Schedule Activity to Ensure this Runbook only runs certain days of the week

Whoa whoa whoa. Where did you get Monday through Friday!? I don't have that.

 Finally, when you wire it up to the next activity (Get Object/Get Incidents to Resolve). You'll need to double click the arrow/link between them and set the following.

In doing this, we are in fact verifying the Check Schedule activity actually conforms to the schedule we care about. This is a hugely necessary step, otherwise you might as well not have the Check Schedule activity there in the first place!

Next, we need to use Get Object which I'll name to "Get Incidents to Resolve". We'll connect to our Service Manager instance and filter incidents whose status is "Pending User Confirmation to Resolve". This activity will run once for every incident it finds. So if there are 12 Incidents in this status it will run 12 times.

Next, let's get our Affected User of the Incident. We'll do this by using a Get Relationship activity and another Get Object (renamed Get Affected User) immediately afterwards.


In our "Object Guid" text field for this Get Relationship activity, we'll right click and select Published Data. Then we'll select SC Object Guid from "Get Incidents to Resolve" in our previous step.


Now in our Get Affected User (Get Object) activity, we'll select the following. Our Class is Active Directory User (i.e. the Related Class of our Get Relationship activity). For our filter's we'll add one for SC Object Guid and set it equal to the Related Object Guid of the previous step. I put that in bold letters, because I want to draw attention to the very fine distinction between it and other similar sounding things in that last.


Finally, we need to return only Affected User from those Incidents. To ensure we'll only get the Affected Users and not "Created by" or "Assigned To" double click the arrow between Get Relationship and our Get Affected User activity. Then go to "Exclude" and hit the Add button.

 Now click on the text that was just added to the list and select Relationship Class.

Now change equals to does not equal and change value to Affected User. Then hit Finish.

So before we keep just hauling along, what have we done here exactly? Why was this step important? If you're new to Service Manager or Orchestrator, you may not frequently think about all the types of things stored in just an Incident. There is an Affected User (Active Directory User), there is an Assigned To user (Active Directory User), there is a...and the list goes on. Had we not added this step, we'd return all the related Active Directory User's as they relate to a single instance of an Incident. By using the Exclude tab, we can throw out all the users/relationships we don't want to appear downstream. This is important for the following step...

Our Get Object activity (renamed Get Affected User) will use a filter of SC Object Guid from the previous step. BUT! Since we told the previous step to only return user's whose class as it relates to the Incident is the Affected User - we know confidently that this activity, regardless of how many times it runs will always be the Affected User object's of the Incidents and nothing else.

Next, we'll do almost the same exact thing but instead for the Assigned To user. So grab a Get Relationship and Get Object and wire them up after Get Affected User. When you are wiring up this Get Relationship don't forget to get the SC Object Guid from the Get Incidents to Resolve step and not the previous step!

The only difference between the following and above steps is the Link between our new Get Relationship and Get Assigned To user, will be an exclude condition for "Relationship Class does not equal Assigned To User"


Again, immediately following our Get Relationship activity we'll have a Get Object that will be renamed Get Assigned To.

Next, we need to create a Text File for each one of the Incidents we need to evaluate to alert on. To do this, grab a Run .NET Script activity from System Activities. These text files will be created on the Orchestrator server so you may want to go create a directory for SCSM Automation tasks such as this one on Orchestrator's C: drive.

Ugh. Text files? Are you kidding me?

No. I'm not kidding you. This is one of those "Come on Orchestrator!" jabs I can't help but have because we'll be using this as a crutch for two things...
  1. SMLets while documented working within Orchestrator aren't perfect. I'm a huge fan of them, but since they are open source, it means you're on your own in terms of support and troubleshooting. Specifically, I tried them about 78 different ways in the eventual PowerShell script that is going to do the notification but the "-smobject" parameter always passes null. As a result we had to do all the building up until this point to get Assigned To, Affected User, etc.
  2. We need a way to store the Action Log (temporarily), sort, parse, and evaluate it. If this can be done in Orchestrator it is beyond me how it could be accomplished. So I'm moving to simply building the data I need, store it, do all my logic in a PowerShell script, and then delete files as I go within the script before a final "clean up" activity.
  3. Alright. I really seriously hate that it's 2015 and I've had to resort to text files for this. Also, it means this list 3 things instead of 2. Just...ugh. You know what. It's fine. It's fine. I'm moving on.

 Our Run .NET Script activity is a PowerShell file one liner.

Since I can't resize those windows, the PowerShell line I have is...
new-item c:\scsm\automation\requestResolutionConfirmation\{ID from "Get Incidents to Resolve"}-{UserName from "Get Affected User"} -ItemType file

Which means our file once created will have a file name like IR25083-dzaka.txt

So now we have text files, for every incident in this status, with the affected user's username in the title of the file separated by a hyphen. Next let's start writing the Action Log into the text files to do that it's time to Get Relationship.

Are you getting the hang of this yet?
Again, wire up to your Service Manager instance, define your Object Class as Incident, set the SC Object Guid equal to your "Get Incidents to Resolve" step from the beginning. Then set the Related Class to Trouble Ticket Action Log.

Then grab your Get Object next (renamed to "Get Action Log Items")


And of course once again, grab a filter property, set the SC Object Guid equal to the Related Object Guid from your previous Get Relationship (IR to Action Log) step.

Holy crap when is this over...

Don't worry, we're really close. That was step 9 out of 13 or 14 depending on if you had the extra Schedule activity at the beginning. Onto step 10! Format the Entered Date date of the Action Log.

Using the Format Date/Time Activity, we just take in the Entered Date as published data from the previous step of Get Action Log Items and change the Output Format to be MM-dd-yyyy.

NOTE: Do not change this output format unless you plan on also changing the format in the final PowerShell script!

Now in our penultimate step (Append Line), we're going to format the order of the data that is going to go into our text files we created a few steps back.

As you can see the order in the append line is...

1. Format Result from "Format Date/Time"
2. SC Object Guid from "Get Action Log Items"
3. Entered by from "Get Action Log Items"
4. Username from "Get Assigned To"

This text files contains NO SPACES and has comma separated values for the final step of PowerShell parsing. Ready? Because here's the script...

PowerShell Parsing, Just a Bit of Lead In
Again, as I opened this post I'm doing what I can in Orchestrator (setting up all my data) and then going to do the actual parsing/logic/notifying with raw PowerShell code. I've tried formatting this code the last several days now and it''s just really frustrating. So here's a link to the PS1 file on my TechNet page.

Since the TechNet article doesn't do much in the way of a description it's worth pointing out the following in the script...

Line 1: Edit to be your Orchestrator's server name
Lines 58-72: Service Manager stores the entered by value as domain\username. 3rd party companies such as Cireson store it as the display name. If you are leveraging multiple technologies I draw attention to this section so you can continue to do so!
Line 78: You will have to have your Support Tier groups as actual Active Directory groups in order to the match.
Line 81 + 87: This is the actual "Days Past" logic in action. Get creative here! 0 days past I did nothing. 1 day past I emailed...and then two days past you could email...
Line 105: Change to use your SMTP server

In summary, the script is parsing your Action Log text files trying to figure out the following...
1. Who was the last person to update the Action Log?
2. Was that person an Analyst or the Affected User?
3. If it was the Affected User, I won't take any action because they must have said something and the analyst didn't take it out of this special status yet
4. If it was an Analyst, I will take action because I'm assuming the analyst must be asking something since it's still in this status

The other thing that I'm personally divided on in this solution is who the email should come from? It could come from the scsm workflow account's email address that is used by your Exchange connector so when the Affected User hits reply it updates the Incident. Then again, it could be spoofed to come from the analyst so it looks like more of a personal touch/communication. In either scenario, the Exchange Connector would process the email assuming you put your scsm workflow account's email address in the CC field. Since the only match it does is the FROM SMTP address field it's your call. Get creative!

In Closing...
I hope SOMEONE found this helpful!

SQL Reporting - A Tip on "Affected User" Reports

8:16 PM Posted by A No comments

I previously published a blogpost detailing how to get started with an Affected User report and it struck me as I was working the other day that there was arguably vital step I had missed. So if you haven't noticed it, corrected my work, or possibly using it in your own reporting this is your heads up!

There are some cases where an Incident in the report will return itself multiple times. It may not be obvious, but you will see every Affected User the Incident has had.

What? How is that possible? I want to yell at you.

Alright. I welcome the criticism in this case, so before you go on a tirade let me explain why that happens. The WorkItemAffectedUserVw table contains a property called DeletedDate. This date will generally be null, but when you flip the Affected User of an Incident the field has value. Needless to say, this only takes an extra step in the WHERE clause to make sure you grabbing the current Affected User.

WHERE user.DeletedDate is null

WITH THIS said. I've already updated my TechNet posting to calling out in the comments how to quickly turn this on/off for yourself. The query has had nothing altered with the exception of the single extra WHERE statement. The link hasn't change so feel free to get the updated version here.