MSSQL 2016: Monitoring failed

I ran into this error when using SCOM 2012 R2 for monitoring SQL 2016 and didn’t find any good solution fast on google so i decided to create one myself when i found the solution.

The error message SCOM gave me was.

After som research if found out that the run as account the agent was using tried to use c:\windows\temp to save files and it does not have access to that folder. After i gave permission to c:\windows\temp to the run as account the alert went away.

SCSM workflow to add UserInput to work item description

I often get the question from customers that are using SCSM how can we get the user input to the description. Out-of-the-box SCSM will place this in a field called UserInput.

This can be frustrating for some customers because they want to be able to send out an email like this”your request is now registered with the following information”. We would just use the normal email templates and add UserInput it would look something like this.

This is not a very good formatting to use in a email to the end-user. Therefor i have created a SCSM PowerShell workflow that will check for all new SR and IR if they contain any UserInput and if it does it will add that to the description. If there already is a description the UserInput will be added below that text. So if we look at the example a before it will look like this instead.

Now this is much better for an end-user to read. The solution will take all the questions on every request offering there is in the system and add it to the description. The solution is not sealed so feel free to modify the code to fit your need.

Below is a code snip from the service request part where i will explain what i am doing.

First we get the service request object so we have something to work with.

Save the UserInput to a variable

We need an array to save all the question with answers so we call that one $UserInput and we have to check if there is a description already. If it is save it to the userinput array.

The next step is to go throw the Questions in the XML-code. We are doing a split to get each question/answer to a row and then go row to row and extract the information we need. And save the question with answers to the array  $userinput

The last step is to add all information saved in $userinput to the description on the work item.

Tip: If you want to send out an email with your case is now registered in our system i recommend to modify this workflow some. Make the workflow set a tag in the SR class for example and when that value is met you can trigger a new workflow to send out an email.

Why should i do this? If you have an email subscription with criteria send on object created the email can be sent before the user input workflow has run and you don’t get a complete description to the end-user.

This function is not excessive tested so it’s up to you to test it and find possible bugs. The full code is available at Technet gallery, Click here for a link


SCSM: Update child incidents with parent ID in the title using PowerShell

Here is a small post i wanted to share.

A customer wanted a easy way to see if a incident is related to a parent incident without having to go into the case. They are using the Cireson Portal and the portal does not show if an Incident is a child unless you go into the Incident (See picture below).

So in order to make a quick fix that does not require Cireson to make changes to the portal i created a PowerShell script that will look for active parent incidents and go into each child and update the title so it says <title> (Child object of IR213) for example (See picture below).

The script is pretty simple done and it might generate performance loss if you schedule it on a fast interval. But that’s up to you to decide what interval the script should run at.

The script


Azure Automation – Start Azure runbooks from your own computer with PowerShell

I have been using Orchestrator for a few year and SMA also. But SMA didn’t really take off at many customers due to the requirements of Azure Pack. However i recently started looking to Azure Automation because i really like it. So i will start doing some blogposts about Azure Automation now. As i am kind of new to this some posts will be really basic but as i progress to more advance runbooks etc. the posts will advance also.

The first post will be how can you connect with PowerShell to Azure Automation. This can be done with at least two-way. One is that you enter your own credentials and the other is with certificate.

Before you start you have to download the Azure PowerShell cmdlets. They can be found here:

Enter your own credentials

This way might not be the best if you want to automate something that requires your own credentials. I recommend using certificate. However it’s easy to do this way.

Just enter the command

Now a browser till open and you can sign in to your account

To verify that it worked you can now run

The account you just added should be listed now.

Using Certificate

Run the command

A Internet browser will open and you have to login to azure and a certificate file will be downloaded.

When the file is downloaded we have to import it into our certificate storage.

To verify that the certificate is active we can run get-azureAccount

And the output should be something like this.

Id Type Subscriptions Tenants

— —- ————- ——-

BFB4C2F0B3A6000000009AE56D87F0C46B1C639BB6 Certificate 0e0000-e6de-40009f-b000c-a751f000bb651

We can also open a mmc.exe and load the certificate plugin and check the personal storage.

So what can we do now?

There are lots of commands we can utilize just see the list below.

For this post i will just show how we can call on a runbook in Azure from our computer.

    1. Create a runbook in Azure Automation

For this example i am using a parameter in the script to show you how we can pass a paramenter from our on-premis to Azure runbook. Remember to publish the runbook after you are done typing in the code.

workflow HelloWorld

In order to call the runbook from PowerShell we need to know the name of our AzureAutomation Account. You can see this easy on the portal.

  1. Start the runbook

Now we can call the runbook from PowerShell.

And if we look in the portal we can see the runbook is starting.

Why do we want to call a runbook from PowerShell on-prem?

Well i won’t go into details here but there are many reasons why we would like to do this. One example is if we have a Offering in Service Manager for example that is provision a new Azure VM. There is no AzureAutomation Connector so we can talk direct to Azure. But we could use a normal Orchestrator runbook or SMA-Runbook to call on AzureAutomation.

Working with Cireson Portal instead of SCSM Console

This is my own thoughts not from my employer or from Cireson.


This post is my own thoughts about Cireson Portal and real life experience from different customers around in Sweden that have replaced the Service Manager Console and only using the portal to do it’s day-to-day work.

We all know that the Console of Service Manager is probably the most negative about the product because it’s slow and does not work well in terminal environments for example Citrix due to high resource use.

Now the Cireson Portal is a HTML5 based portal with it’s own database that caches the work items from Service Manager to that database in order to provide as fast rendering of webpages as possible. This solution works great but it comes with a few limitations. Because it’s using an own database not all information that is stored in the normal service manger database does exists in the Cireson database, but this should not be a problem for most users.



If you have worked with Azure before you can see that the looks are similar, this is a good design choice but the placement of the buttons for Save, Apply, Cancel might not be the best place. Users


The first things customers always wants in a ITSM tool is to modify the forms to fit the company need. In SCSM you had Authoring Tool and to be honest it’s not that good of a tool. It crashes a lot and you needed to clean the XML-files after you where done.

Now in the portal Cireson has implemented a clever way of doing this and making it really simple for someone without much coding experience to do it.

This code snippet is taken from the Incident form. You have to create a Incident.js and place it in the customspace folder to apply your own customization to the portal. All this customization are passed on between portal upgrades also.

I wont go into detail here but if i wanted to remove the Impact field i would just remove the line. If i didn’t want it to be a required field i just change required: true to false. It’s just that easy on form modifications for Service Request, Change Request, Release Request, Incident Request and Service Requests.


The downside about customization’s here is that you cannot modify a activity form (Review, Manual etc) They are locked in this look for the moment. My hope is that Cireson will allow us to modify even those form so we can remove/add forms/fields we feel are necessary.


Service Catalog

If you have a good service catalog already in SCSM the catalog will just show in this portal without any modifications. The offerings do however still have the same limitations as before, we can’t create dynamic questions and so on. But it’s prettier then the old ugly SharePoint portal. And you can do some customization here also. How big the icons should be etc.



Cireson added some dashboard support a few releases ago. It comes out-of-the-box with some predefined dashboards. Now they do give a fairly good overview but it might not fit all organisations. You can create your own dashboard but at the moment of this post it’s not easy at all. It requires good SQL knowledge and more.

However Cireson has told us that they will release a admin interface for this in the future.



Other nice stuff

There are lots of functions in the portal and i wont write them all down here, but i wanted to highlight a few i find appealing because it adds good functions that customers wants.


The good things about this portal is that you can place it on a DMZ and provide access to all your work items direct from the Internet if you want. This is a good thing for service providers for example that has the need for customers to access the tickets they have created in their system.


The portal supports single-sign-on, there is a few prereq that needs to be configured but it’s not hard to get the portal to allow AD-credentials to login automatic to provide a more seamless user-interaction.

Active Directory integration on support groups. 

The portal will have the integration we always wanted to AD. The view Teams work item will show all work that my team have. This is based on mapping all support groups to a active directory group.


As of all young products they all have limitations and this product is not different. You will still depend on the SCSM Console for a few things.


As i wrote before we have dashboards in the portal but we do not have any access to any reports.


We cannot modify any templates of any kind from the portal. If we want to change for example a service request template we have to do it from the console.

Activity management

It’s not possible to add/remove/modify activities from a service request och change. We can change for example implementer and add notes etc to the activities but if we want to remove an review activity from a service request we have to do it from the SCSM Console.

Custom Area (Service Request) 

As many of you might know it’s not possible to add a value to the Area list on service requests due to the fact it’s sealed. So we often create a new list and replace the old using authoring tool in the Console.

Now in the Cireson portal we can replace the lists on all forms but not from a system perspective. For example all other places that uses the old sealed values we cannot change. So the dashboards that comes with the console will not look at correct list. All views will not display anything from the Area and so on.


The portal does support a bunch of language but it does not work as the Console does. In the Console it will always fallback to English language if a list value does not exists in another language.

If we use Swedish as language in the portal and we have a list with only English values then the list will be empty and you have to modify the XML code to add another language so the portal can show it.


My own conclusion here is that from an analyst and a end-user perspective you can work with all your normal work items from the portal only. There is no need to use the Console to create a work item, resolve it or approve a new change etc. Everything can be achieved from the portal.

I know there is a few other 3rd party apps out there on the market also but i have not tried them yet.

If you are using SCSM and still using the portal that comes with SCSM you should check this out. I have work quite much with the portal the last months so if you have any question feel free to add a comment. Or head over to

Close ALL work items in SCSM with PowerShell

I was at a customer that had several 1000 work items, Incident, Service Requests, Change, Activities etc. I wanted to close all items and make the test environment clean. So i created a PowerShell script based on SMlets that will take all work items and close them.

Be careful so you don’t run this script on a production environment :)


Get a grip on updated SR/IR by End-users in Service Manager


As many of you that are using Service Manager are aware on is that it’s not that easy to view if an item is updated by an end-user from the Exchange Connector for example.

There is a few ways to get a grip on this situation. I know that some people are using SCSM Workflows to do this but i think it’s more easy to do with orchestrator and you can do more things with Orchestrator. So if you have it in place why not use it?

First we have to extend the SR and Incident class with new boolean. Lets use UpdatedByEndUserStatus as an example. Set the value to default false.

The point of this extension is that when a end-user comment is added we are going to change the value on UpdatedByEndUserStatus to true and have a view that lists only SR and IR with that status.

And when an analyst adds a comment to the log we remove this flag automatic with orchestrator. So if we are using for example Cireson Portal we can have a conversation in the console.

I send a email to the user. Waits for the respons and when the respons are here we can see the case in the view. When i send another respons the case is removed from the view. So only when it’s visible on the view we have something new in the case.

Extending the Class

I am not going to tell how to extend the SR class. There are plenty of blogs that will provied this information. Here are a few blogs on extending classes in SCSM.

Setting up the Orchestrator Runbook(s)

We have to create 2 runbooks. One for analyst and one for End-user updates. We are going to change the values on the extended attribut we created when we extended the class.



We need to monitor the class Trouble Ticket Analyst comments. When an analyst enters a comment to a SR or IR it is in this class it is saved.

runbook_monitor_analyst_monitorNext step is to get the relationship to what SR or IR we want. In this example i am only doing it for SR.


After we have the relationship we have to fetch the SR we want to update with correct status.

Now when we have the SR we can update the status on the property we added when we extended the class. In this picture i don’t have a boolean value i am using a list instead. If you are using a boolean just set it too false instead. runbook_monitor_analyst_updatesr


Now we have to create the same runbook again but now we should monitor the class Trouble Ticket User Comments Make sure to change the last step also set the boolean value to true.

Create a view

When this is done we can now create a view that have UpdatedByEndUser = true or whatever you have configured your settings as. And now we have a nice view that lists updated cases.

viewWhen an analyst see this item and goes into it and send out a email using Cireson portal, or the someone of the other 3rd party send email apps out there. This work item will vanish from this view as there are nothing to see until a user answer the email and then the item is back on the view.

Hope this can help some people out there. It’s not a fancy way to do it but it gets the job done without spending much time to configure it.


UR5 For Service Manager 2012 released

Update rollup 5 for system center service manager 2012 R2 was just released.

You can download it here

      Bug fixes included in this update:


      The System Center 2012 R2 Service Manager Update Rollups are cumulative, hence this UR5 contains new fixes for the following issues along with fixes shipped with System Center 2012 R2 Service Manager UR4, UR3, and UR2.


    1. Change request still stays “in progress” when last activity is skipped and all previous activities are completed.
    2. ActualStartDate and ActualEndDate field values for Change Request and Release Request do not set.
    3. Multiple System Center Service Manager Connectors to System Center Configuration Manager execute and complete successfully. However, the start, finish and status property values are not updated in the Service Manager Console under the “Connectors” view and event 3334 is logged.
    4. Trying to delete a Service Request template that is being used by a Request Offering causes a cryptic and non-user friendly error message.
    5. The “Group By” functionality in a View does not work correctly when “Group By” column has empty fields.
    6. The Console crashes when the user tries to open an already opened attachment from a Work Item form.
    7. Opening the Views for Groups takes a long if there are large number of explicit members in the groups.
    8. The OM CI connector fails if one tries to import a distributed application that contains an instance of the ‘Hyper-V Virtual Network Adapter’ class.
    9. Monitoring Host process crashes if there exists a Notification after the user who created it was deleted or has moved to a hidden organizational unit (OU) in the Active Directory.
    10. An exception is raised if the user tries to open a sorted View which is created by using TypeProjection on custom Class that is neither Abstract and nor a first Concrete class.
    11. An Exception is thrown when a user tries to open the “Service Components” tab on the Service Maps form, when there are large number of services in CMDB.
    12. Enhanced in event logging for DW jobs.
      Logging the batch start and completion events for all DW job categories in event log. The start and completion event will include the following:
      Process Category

      1. Process Name
      2. Batch Id
      3. Batch Start or completion Time
    13. Enhanced in event logging for DW cube processing.
      1. Time taken by Cube’s batch id to complete will be logged in seconds.
      2. Information whether the cube is processed under Analysis Services that are running in SQL Server Standard Edition or the SQL Server Enterprise Edition.
      3. Event logs will be added during processing of each Dimension and Measure Group for each partition (if applicable) for both enterprise and standard SQL editions.
    14. Added a new PowerShell cmdlet Get-SCDWInfraLocations Cmdlet on the Service Manager management server to retrieve the following location information about its data warehouse infrastructure:
      1. Service Manager database
      2. Service Manager data warehouse database
      3. Service Manager Analysis server database
      4. Data Warehouse Reporting Server database and Report Server URL


Service Manager 2012 PowerShell Exchange “Connector”

There is a Exchange connector for Service Manager but in my experience i think it lacks a bit of features. And it is not possible to configure it as much as i want to. To create a work around for the exchange connector i created a Powershell based “connector”

This is a kind of concept that shows you that you can create awesome stuff with Powershell and tweak you’re mail integration a lot more then with the normal connector.

The “connector” works this way, you schedule it on a management server for a interval of 5 minutes and it will use the exchange web service to look in a mailbox for emails. The script also depends on SMlets. You can download The Exchange web service here and SMlets here

By default it process all emails that has a IR prefix or SR prefix. If it finds a email that does not contain a valid prefix it is processed as trash and an email is sent to the mail sender that it contained a invalid case number.

You can change this behavior to allow all mail that does not contain a prefix or SR/IR so the “connector” will create a new incident with that content. The new incident will use a specified Incident template.

I have tested the script from a Windows 2012 R2 server with Service Manager 2012 R2 (works with UR4 also) and on a office 365 mailbox. It should work against Exchange 2010 for example if auto discovery is configured.

Functions in version 1.0

  • Incident Comments from email
  • Service Requests comments from email
  • Incident attached files added to the Incident
  • Service Request attached files added to the service request
  • Ability to create new incidents from email based on incident templates
  • If the sender of the email does not exists as a CI in service manager database an internal account will be created for that user.
  • All processed emails are kept in a folder in the mailbox
  • If the incident or service request is in the status closed the user gets an email saying the mail was not processed due to cases being closed.

Here is an example for a email that contains attached file. The subject for this email was IR122 and contained the file IR122.txt and the text “This is a test comment sent into IR122 with an attached file”


After the powershell script has run we can look in the action log and you can now see that there is an entry from me and a attached file (Bifogad fil in Swedish)


And on the related object page we can see the attached file.email_after_incident_process_relatedItems

Functions that i am working on in version 1.1

  • Be able to resolve incident by sending [resolved] in the email tag
  • Be able to complete service requests by sending [completed] in the email tag
  • Splitting the code into more functions and modules to get an more easy overview of the code


The script is available on technet gallery. Here is the link.

If you want something more added in future versions just throw in a comment below.


New year and new job

So 2015 wlll be a fun new year.  I am starting at the company Lumagate in January. I will be a part of the service delivery and automation team. With Focus around SCSM and SMA/SCO.

I have not worked as a consulant before, So now i will be in contact with many different installations and will probably come in contact with other problems that i can blog about then i had at my current employer.

So the blog will most likley have more focus around SCSM/SMA/SCO. My hope is that the blog posts that are coming will be more advanced and more detailed on different stuff. It’s a pretty big area with automation and service delivery.

So happy new year.