How to Enable Automated CSV-Import On-Demand in SCSM 2012 with Orchestrator

Recently, a question came up in the SCC support forums recently regarding compatibility of the CSV Connector written for Service Manager 2010 with SCSM. For those of you that are not already familiar, the Import CSV feature in System Center 2012 Service Manager (SCSM) is used to import comma separated value (CSV) files to automatically populate data.  It actually requires two files: the CSV file containing the data for the CMDB and the XML format file that describes how SCSM should interpret the data. The disadvantage of this feature versus the unsupported CSV Connector in the previous version is that this feature requires manual effort to actually import the file. I’ve provided some links to sample CSV / XML pairs in the Additional Resources section at the end of this article to help you along.

This is a “quick release” for benefit of community members. A more thoroughly tested and robust version will be included in the upcoming System Center 2012 Orchestrator Unleashed book. Please log any issues with getting this sample on the original forum thread or in the comments on this blog post and we’ll happily assist you in getting this sample working, and update this article as necessary.

Automating the Process in SCSM 2012

Since the answer to the compatibility question from the forums is “no”, I thought I would offer up an alternative using System Center 2012 Orchestrator (SCOrch) to provide a way to fully automate this process on an on-demand basis.

The sample runbook for automated   CSV import (shown in the figure below), performs the following steps:

  1. Watch for CSV files monitors a user-defined directory for a CSV file, then verifies that the matching XML format file is present. It assumes the file name prefix of the CSV file matches that of the XML file. For example, if you CSV file is named FileShare.csv, the XML file must be named FileShare.xml. To trigger the runbook, you simply need to provide the file pair. For recurring use, you can leave the XML format file in place in the monitored folder and just add a new CSV file when updates are needed. If you create that file via a script or automated export, the process becomes hands free!
  2. Get File Name Prefix splits the CSV file name at the period (.) to get the file name prefix
  3. XML format file present then checks to ensure the matching XML format file is present. If not, it logs an error message to the Application Windows Event Log and the sequence ends.
  4. If both files are present, the Perform the import activity uses PowerShell remoting to connect to the SCSM management server, loads the Service Manager PowerShell cmdlets (my example also loads the SMLets just to show you how…requires the SMLets are installed on the remote SCSM server)
  5. Based on the result of the import, a success or failure message is logged.

image

NOTE: The instructions below assume you have basic SCOrch runbook authoring knowledge, but provide some helper links in a couple of areas to assist. If you’re completely new to SCOrch, see the links in the Additional Resources section at the end of this article.

Creating the Sample Runbook

Begin by dragging a Monitor File activity onto the palette in the Runbook Designer and renaming to Watch for CSV files. Configure to monitor the directory of your choice as show in the image below. Click the Add button and configure the Filter on

File Name | Matches pattern | *.csv

image

On the Triggers tab of this activity, select Created as shown in the image below:

image

Now, drag a Split Fields activity from the Data Manipulation category onto the palette and configure with the following on the Properties tab, as shown in the image below:

  • Input String: is subscription data {Name of the file from “Watch for the CSV files”}
  • Split Delimiter: is a period. Just enter a period (.) into this field
  • Use Regular Expression: Should be set to False.

image

Next, drag a Get File Status activity onto the palette and rename to  XML format file present? We’re going to use this activity to make sure the XML format file needed to complete the CSV import is present.

On the Details tab, under File Status, configure the  File property as pictured and explained below. Basically, we’re using published data to build the file name and path for the XML file we need to verify.

  • {Origin folder from “Watch for CSV files”} – This is the path to the folder we are monitoring for new CSV files
  • Then add a backslash (\) as shown in the image
  • {Field01 from “Get File Name Prefix”} – This is the name of the file (without the .csv) from the renamed Split Field activity. Add the .xml on the end and you’re all set

Click OK and Finish to save your work.

image

Next, drag a Run .Net Script activity onto the palette and on the Details tab under Language, set the type to PowerShell.

image

In the Script field, paste the sample script provided below and configure the values within as instructed below. Once you’ve configured these values, click Finish twice to save your work.

Here is the sample script requires configuration of the following items:

  • {SCSM Admin – Username} – This is a variable containing the name of an SCSM administrator in the format domain\username. Optionally, you could instead simply type that value in below. For this an any other variable mentioned, you will need to create them before adding them into the script. (How to create variables in SCOrch can be found at http://technet.microsoft.com/en-us/library/hh440543.aspx)
  • {SCSM Admin – Password – This is a variable containing the password of the SCSM administrator account provided above. Optionally, you could instead simply type that value in below, though I don’t recommend doing this, as creating a variable allows you to check the box to encrypt the value you’ve entered.
  • {SCSM Server} – This is a variable containing the name of the remote SCSM management server.
  • {Name and path of the file from “Watch for CSV files”} – is subscription data and self-explanatory.
  • {“Name and path of the origin file from “XML format file present”} – is subscription data and self-explanatory.

# —– Start Sample Script—–

$Username = “{SCSM Admin – Username}
$Password  = “{SCSM Admin – Password}” | ConvertTo-SecureString -asPlainText -Force
$Creds       = New-Object System.Management.Automation.PSCredential($Username,$Password)

$Return = Invoke-Command -ComputerName “{SCSM Server}” -Credential $Creds -ScriptBlock {

    # Importing Service Manager and SMLets Modules
Import-Module “C:\Program Files\Microsoft System Center 2012\Service Manager\Powershell\System.Center.Service.Manager.psd1”
#Import-Module SMlets

    # Import the CSV file
Import-SCSMInstance -DataFileName “{Name and path of the file from “Watch for CSV files”}” –FormatFileName “{“Name and path of the origin file from “XML format file present”}

Start-Sleep -Seconds 10

}

# —– End Sample Script—–

NOTE: At least one user reported the “Start-Sleep -Seconds 10” at the end of the script block (right before the close curly bracket } ) eliminated a premature termination of the remote session, allowing the data import to complete.

Drag a Log Event Log Message activity onto the screen and configure it to log a message as shown in the image below. Specifically, on the Details tab:

  • Computer: localhost (the SCOrch server)
  • Message: SCSM imported csv file data successfully. The file processed was {Name and path of the file from “Watch for CSV files”}

image

 

NOTE: Using a UNC path rather than a local path could result in an access denied issue (due to Kerberos double hop). Placing the folder repository on the SCSM Server will solve this issue.

Link Filtering (Branching Logic)

The link filtering for the links to the event logging activities is pretty straightforward as well and is pictured below in the order they appear in the runbook:

“XML file missing” link

image

“CSV import failed” link

image

The link connecting the CSV import successful activity uses the default logic and requires no configuration.

As I mentioned, this is a quick release solution, so let me know how you get on with this sample and ping me here or on the forum thread with issues.

Additional Resources

Using the CSV import feature – http://blogs.technet.com/b/servicemanager/archive/2009/05/26/using-the-csv-import-feature.aspx

For SCOrch newbies, give the first three parts of my Orchestrator Jumpstart series a read and get yourself up to speed on Orchestrator concepts from simple to advanced and drop us a line with questions in the SCC Orchestrator Forum.

Special thanks to Wilson W. for his feedback based on real-word testing!

13 thoughts on “How to Enable Automated CSV-Import On-Demand in SCSM 2012 with Orchestrator

  1. Profile photo of Wilson W.Wilson W.

    Pete, this is fantastic and very timely.  I was trying to figure out how to do this just this past week as I was having trouble getting the SCSM cmdlets module loaded for some reason.   I will try this out tomorrow morning and let you know how it goes.  Thx for putting this information together!

     

    Oh, and I do believe I owe you a drink at MMS !  🙂

     

     

  2. Profile photo of Wilson W.Wilson W.

    Pete, it looks like you’re missing an explanation and screenshot for one of the activities.

     

    The “XML format file present?” activity screenshot is missing.  I am assuming that this activity is the “Get File Status” activity?

  3. Profile photo of Wilson W.Wilson W.

    That looks good Pete….when I was testing it out I had no idea what was supposed to go into that activity so I had simply hardcoded my xml file name into that field.

     

    Also, another peculiar thing I noticed is that when I cut & pasted your Powershell script into Orchestrator it translated your quote marks into open and closed quotation marks.  In some places the close quote at the end was actually an open quotes character.  I had to go back and type over all of the quote marks to just make them the standard quote marks in order to get the script to run without errors.

     

    Right now I have everything in my runbook set up exactly the way you have it explained here……the runbook seems to run and kick off properly when it detects a change in the source CSV file.  The only problem is that after it runs it doesn’t appear to make any changes in my CMDB record.   I’ve simplified my CSV import to just a couple of records and one field.  I’m double-checking everything now and will let you know how that goes…..

     

     

  4. Profile photo of Wilson W.Wilson W.

    Very odd….I have my runbook set up exactly the way you have documented.  The runbook seems to detect when the CSV changes and kicks off appropriately.  It seems to run all the way through with no errors.  But when I check my CI record in the CMDB the attribute field I am checking is not changed.  The CSV and XML files are properly formatted as I am able to manually import them via the SCSM console with no problems.

     

    I have all of my global variables set in SCORCH….I also have the SCSM console installed on the SCORCH management servers.   Do I need to add some sort of command in your script to set the execution policy to unrestricted….?

  5. Profile photo of Wilson W.Wilson W.

    So….I’ve been trying to troubleshoot my problems with getting the script to run for the past 2 days now.  At this point I’ve removed the Orchestrator runbook out of the equation and I’m simply trying to run the script with the values hard-coded in them.  When I try running the script from my Orchestrator management server it doesn’t appear to work.  -something in the invoke-command sequence is not working when it tries to reach out to my SCSM management server and run the import-scsminstance command.

    However, when I try running that same script from my workstation or laptop it works!

    I have set the execution policy of all of my servers to ‘Bypass’.  Is this some sort of CredSSP issue..?

  6. Profile photo of Wilson W.Wilson W.

    FYI, I finally figured out the issue with the Powershell script with the help of one of my co-workers who is more adept at Powershell than I am.

     

    What was happening is that when the invoke-command opened a session on my remote server, the session would open and then immediately close before the import of the data had time to be processed.

     

    Simply inserting a “Start-Sleep -Seconds 10” at the end of the script block solved the issue. (You will need to be on Powershell 2.0 or higher to use that cmdlet)

     

    Another problem was that the file paths for the XML and CSV files need to be local file paths.  Don’t use the FQDN of the server or else you are doing a double-hop.   You are already opening a remote session on that target server so any subsequent commands you execute need to be formatted as if you are local to the server.

    So basically in Orchestrator simply configure your global parameter with something like “C:\export” instead of “\\ServerXYZ\C$\export”.

     

    Anyways, this may all be obvious to someone who is proficient in Powershell, but being the noob that I am I hope this helps somebody else in a similar situation.

     

     

  7. Profile photo of joaojoao

    Hello,

    Thanks for the help, I managed to create my runbook with some changes in the end script.

    I have change the $Creds= New-Object System.Management.Automation.PSCredential to $session
    =new-pssession –computername.

    but I can only run the runbook with success when the files are on the service manager server, it is possible to run the same runbook but with the files on another server?

     

    Regards

  8. Profile photo of Wilson W.Wilson W.

    You should be able to run it with the CSV and XML files on a different server than your SCSM server.  Try opening up the NTFS permissions on your target share to see if it’s a permissions issue.

  9. Pingback: Automating Service Manager CSV Data import using Orchestrator–Addendum - SCOM and Plus

  10. Profile photo of Pete ZergerPete Zerger Post author

    Wilson, those are both good tips to enhance reliability I think. I’ll add both of those as notes and credit you. Thanks for the feedback!

Leave a Reply