Create SRs in bulk from an Excel file

Paul_Dalkin · February 2018

Hi, we don't use the Cireson Asset Manager (we have our own solution) however has anyone managed to amend the Excel import function to directly create SRs from rows in a xls rather than adding CIs to the CMDB?

We have a rolling programme of hardware updates - with their own workflows as they involve different teams across our site.

If the team that did the procurement could create the SRs for deployment from the delivery xls without having to manually input the request offering fields would speed things up immeasurably.

David_Morris1 · April 2018

to do this you would have to create a custom excel addin in Visual studio in either c# or vb it can be done but would be substantial work, a much quicker suggestion would be to use CSV (if you want to integrate into excel directly use vba to save the content of the excel into a csv on a file share) then use either orchestrator or a scheduled task to ingest the csv into powershell using a foreach loop to turn each row into an SR using the process Roland linked

Brian_Wiest · April 2018

In my experience the blog that @Roland_Kind shares functionally works, it has a flaw that you will find other posts about.
The flaw is the SMLets when applying a template that contains the activities the activities will not go to in-progress, they all just sit at pending. Also the Activities are missing their assigned prefix to the WI ID.
So putting together a few posts I found I complied this.
It will process a CSV creating a service request for each row. (You just update the title and description as you need from the rows)
Then it pulls a helper file that then "fixes" the activities so the the first is set to in-progress, and adds the WI ID prefix.
I have used this script to create 100 SR's with each having 6 MA's attached. Took less then 1 minute in my production farm.
The below example assumes a CSV with three columns Title, Name, ID.

import-module smlets;
$path = "C:\Temp\Test.csv"
$csv = Import-csv -path $path
 
#Get the Service Request Class
$serviceRequestClass = Get-SCSMClass -name System.WorkItem.ServiceRequest$
 
#Get the enumeration values needed for the lists in the Service Request
$serviceRequestArea = get-SCSMEnumeration -Name ServiceRequestAreaEnum.Security.Physical
$serviceRequestPriority = Get-SCSMEnumeration -Name ServiceRequestPriorityEnum.Low
$serviceRequestUrgency = Get-SCSMEnumeration -Name ServiceRequestUrgencyEnum.Low
#Get The Service Request Type Projection
$serviceRequestTypeProjection = Get-SCSMTypeProjection -name System.WorkItem.ServiceRequestProjection$
#Get The Service Request Template
$serviceRequestTemplate = Get-SCSMObjectTemplate -DisplayName “MyTemplateName”


foreach ($line in $csv)
{ 
    #Specify the title
    $serviceRequestTitle = $line.Title
    $serviceRequestDescription = "$($line.title)`r`nFullname=$($line.Name)`r`nID=$($line.ID)"

    #Create a hash table of the Service Request Arguments
     $serviceRequestHashTable = @{
         Title = $serviceRequestTitle;
         Description = $serviceRequestDescription;
         Urgency = $serviceRequestUrgency;
         Priority = $serviceRequestPriority;
         ID = “SR{0}”;
        Area = $serviceRequestArea
        }

    #Create initial Service Request
    $newServiceRequest = New-SCSMOBject -Class $serviceRequestClass -PropertyHashtable $serviceRequestHashTable -PassThru
    $serviceRequestId = $newServiceRequest.ID
    write-host ("SR Created " + $serviceRequestId)

    #Get the Service Request created earlier in a form where we can apply the template
    $serviceRequestProjection = Get-SCSMObjectProjection -ProjectionName $serviceRequestTypeProjection.Name -filter “ID -eq $serviceRequestId”

    $SR = Get-SCSMObject -Class (Get-SCSMClass -Name system.workitem.servicerequest$) -Filter "Id -eq $serviceRequestId"
    
    C:\PowerShell\FixActivites.ps1 -Id $SR.get_id() -TemplateId $serviceRequestTemplate.get_id()

    #get the first Activity
    $firstActivity = Get-SCSMRelatedObject -SMObject $SR |?{$_.classname -like "System.WorkItem.Activity*" -and $_.sequenceID -eq "0"}
    write-host ("For SR " + $serviceRequestId + ", Found First Activity " + $firstActivity)

    # Set ActivityStatus to in progress
    Set-SCSMObject -SMObject $firstActivity -Property status -Value "in progress"
    write-host ("Set First Activity to in progress " + $firstActivity)
}

Script for the referenced C:\PowerShell\FixActivites.ps1

# //***************************************************************************
# // Author:  Morten Meisler, Coretech A/S. http://blog.coretech.dk/mme
# //   
# // Usage: Script to apply a template with activities on a workitem and correct
# //        id prefixes on each activity
# //        
# //
# // CORETECH A/S History:
# // 1.0    27/12/2013   beta version 1.
# // 1.1    02-01-2014   Fixed issue with activities containing other objects (like system.reviewer)                       
# //***************************************************************************

Param ([guid]$Id =$(throw "Please provide guid of the workitem"), 
       [string]$TemplateDisplayName,
       [guid]$TemplateId,
       [string]$ComputerName = "localhost")

#----------------------------------------------------
#Function to get activity id prefix from the activity settings
#----------------------------------------------------
function Get-SCSMObjectPrefix
{
    Param ([string]$ClassName =$(throw "Please provide a classname"))
    
    switch ($ClassName)
    {
        default 
        {
            #Get prefix from Activity Settings
            if ($ClassName.StartsWith("System.WorkItem.Activity") -or $ClassName.Equals("Microsoft.SystemCenter.Orchestrator.RunbookAutomationActivity"))
            {
                $ActivitySettingsObj = Get-SCSMObject -Class (Get-SCSMClass -Id "5e04a50d-01d1-6fce-7946-15580aa8681d")
                
                if ($ClassName.Equals("System.WorkItem.Activity.ReviewActivity")) {$prefix = $ActivitySettingsObj.SystemWorkItemActivityReviewActivityIdPrefix}
                if ($ClassName.Equals("System.WorkItem.Activity.ManualActivity")) {$prefix = $ActivitySettingsObj.SystemWorkItemActivityManualActivityIdPrefix}
                if ($ClassName.Equals("System.WorkItem.Activity.ParallelActivity")) {$prefix = $ActivitySettingsObj.SystemWorkItemActivityParallelActivityIdPrefix}
                if ($ClassName.Equals("System.WorkItem.Activity.SequentialActivity")) {$prefix = $ActivitySettingsObj.SystemWorkItemActivitySequentialActivityIdPrefix}
                if ($ClassName.Equals("System.WorkItem.Activity.DependentActivity")) {$prefix = $ActivitySettingsObj.SystemWorkItemActivityDependentActivityIdPrefix}
                if ($ClassName.Equals("Microsoft.SystemCenter.Orchestrator.RunbookAutomationActivity")) {$prefix = $ActivitySettingsObj.MicrosoftSystemCenterOrchestratorRunbookAutomationActivityBaseIdPrefix
                }
            } 
            else {throw "Class Name $ClassName is not supported"}
        }
    }
    return $prefix
}
#----------------------------------------------------
#Function to set id prefix to activities in template
#----------------------------------------------------
function Update-SCSMPropertyCollection
{
    Param ([Microsoft.EnterpriseManagement.Configuration.ManagementPackObjectTemplateObject]$Object =$(throw "Please provide a valid template object")) 
    
    #Regex - Find class from template object property between ! and ']
    $pattern = '(?<=!)[^!]+?(?=''\])'
    if (($Object.Path) -match $pattern -and ($Matches[0].StartsWith("System.WorkItem.Activity") -or $Matches[0].StartsWith("Microsoft.SystemCenter.Orchestrator")))
    {
        #Set prefix from activity class
        $prefix = Get-SCSMObjectPrefix -ClassName $Matches[0]
       
        #Create template property object
        $propClass = [Microsoft.EnterpriseManagement.Configuration.ManagementPackObjectTemplateProperty]
        $propObject = New-Object $propClass

        #Add new item to property object
        $propObject.Path = "`$Context/Property[Type='$alias!System.WorkItem']/Id$"
        $propObject.MixedValue = "$prefix{0}"
        
        #Add property to template
        $Object.PropertyCollection.Add($propObject)
        
        #recursively update activities in activities
        if ($Object.ObjectCollection.Count -ne 0)
        {
            foreach ($obj in $Object.ObjectCollection)
            {  
                Update-SCSMPropertyCollection -Object $obj
            }        
        }
    } 
}
#----------------------------------------------------
#Function to apply template after it has been updated
#----------------------------------------------------
function Apply-SCSMTemplate 
{
    Param ([Microsoft.EnterpriseManagement.Common.EnterpriseManagementObjectProjection]$Projection =$(throw "Please provide a valid projection object"), 
    [Microsoft.EnterpriseManagement.Configuration.ManagementPackObjectTemplate]$Template = $(throw 'Please provide an template object, ex. -template template'))

    #Get alias from system.workitem.library managementpack to set id property
    $templateMP = $Template.GetManagementPack() 
    $alias = $templateMP.References.GetAlias((Get-SCSMManagementPack system.workitem.library))
    
    #Update Activities in template
    foreach ($TemplateObject in $Template.ObjectCollection)
    {
        Update-SCSMPropertyCollection -Object $TemplateObject
    }
    #Apply update template
    Set-SCSMObjectTemplate -Projection $Projection -Template $Template -ErrorAction Stop
    Write-Host "Successfully applied template:`n"$template.DisplayName "`nTo:`n"$Projection.Object 
}

#--------------------------------
#INITIALIZE
#--------------------------------
$SMDefaultComputer = $ComputerName

#Load SMlets module
if (!(Get-Module smlets)) {Import-Module smlets -force -ErrorAction stop}

#Get object from guid
$emo = get-scsmobject -id $id

#determine projection according to workitem type
switch ($emo.GetLeastDerivedNonAbstractClass().Name)
{
    "System.workitem.Incident" {$projName = "System.WorkItem.Incident.ProjectionType" }
    "System.workitem.ServiceRequest" {$projName = "System.WorkItem.ServiceRequestProjection"}
    "System.workitem.ChangeRequest" {$projName = "System.WorkItem.ChangeRequestProjection"}
    "System.workitem.Problem" {$projName = "System.WorkItem.Problem.ProjectionType"}
    "System.workitem.ReleaseRecord" {$projName = "System.WorkItem.ReleaseRecordProjection"}
    
    default {throw "$emo is not a supported workitem type"}
}
#Get object projection
$emoID = $emo.id
$WIproj = Get-SCSMObjectProjection -ProjectionName $projName -Filter "Id -eq $emoID"

#Get template from displayname or id
if ($TemplateDisplayName)
{
    $template = Get-SCSMObjectTemplate -DisplayName $TemplateDisplayName
}
elseif ($templateId)
{
    $template = Get-SCSMObjectTemplate -id $TemplateId
}
else
{
    throw "Please provide either a template id or a template displayname to apply"
}

#Execute apply-template function if id and 1 template exists
if (@($template).count -eq 1)
{
    
    if ($WIProj)
    {
        Apply-SCSMTemplate -Projection $WIproj -Template $template
    }
    else
    {throw "Id $Id cannot be found";}
}
else{throw "Template cannot be found or there was more than one result"}

Roland_Kind · February 2018

Hi,

as an alternate possibility you can use powershell to create SR

maybe the following link might be helpful

http://www.systemcentercentral.com/day-78using-powershell-create-service-request-using-template/

regards

Paul_Dalkin · February 2018

Hi Roland - the idea of using a similar process to asset import would be utilising the excel interface directly rather than a single PS transaction at a time, but it's a good start if it could be scaled up.

David_Morris1 · April 2018

to do this you would have to create a custom excel addin in Visual studio in either c# or vb it can be done but would be substantial work, a much quicker suggestion would be to use CSV (if you want to integrate into excel directly use vba to save the content of the excel into a csv on a file share) then use either orchestrator or a scheduled task to ingest the csv into powershell using a foreach loop to turn each row into an SR using the process Roland linked

Brian_Wiest · April 2018

In my experience the blog that @Roland_Kind shares functionally works, it has a flaw that you will find other posts about.
The flaw is the SMLets when applying a template that contains the activities the activities will not go to in-progress, they all just sit at pending. Also the Activities are missing their assigned prefix to the WI ID.
So putting together a few posts I found I complied this.
It will process a CSV creating a service request for each row. (You just update the title and description as you need from the rows)
Then it pulls a helper file that then "fixes" the activities so the the first is set to in-progress, and adds the WI ID prefix.
I have used this script to create 100 SR's with each having 6 MA's attached. Took less then 1 minute in my production farm.
The below example assumes a CSV with three columns Title, Name, ID.

import-module smlets;
$path = "C:\Temp\Test.csv"
$csv = Import-csv -path $path
 
#Get the Service Request Class
$serviceRequestClass = Get-SCSMClass -name System.WorkItem.ServiceRequest$
 
#Get the enumeration values needed for the lists in the Service Request
$serviceRequestArea = get-SCSMEnumeration -Name ServiceRequestAreaEnum.Security.Physical
$serviceRequestPriority = Get-SCSMEnumeration -Name ServiceRequestPriorityEnum.Low
$serviceRequestUrgency = Get-SCSMEnumeration -Name ServiceRequestUrgencyEnum.Low
#Get The Service Request Type Projection
$serviceRequestTypeProjection = Get-SCSMTypeProjection -name System.WorkItem.ServiceRequestProjection$
#Get The Service Request Template
$serviceRequestTemplate = Get-SCSMObjectTemplate -DisplayName “MyTemplateName”


foreach ($line in $csv)
{ 
    #Specify the title
    $serviceRequestTitle = $line.Title
    $serviceRequestDescription = "$($line.title)`r`nFullname=$($line.Name)`r`nID=$($line.ID)"

    #Create a hash table of the Service Request Arguments
     $serviceRequestHashTable = @{
         Title = $serviceRequestTitle;
         Description = $serviceRequestDescription;
         Urgency = $serviceRequestUrgency;
         Priority = $serviceRequestPriority;
         ID = “SR{0}”;
        Area = $serviceRequestArea
        }

    #Create initial Service Request
    $newServiceRequest = New-SCSMOBject -Class $serviceRequestClass -PropertyHashtable $serviceRequestHashTable -PassThru
    $serviceRequestId = $newServiceRequest.ID
    write-host ("SR Created " + $serviceRequestId)

    #Get the Service Request created earlier in a form where we can apply the template
    $serviceRequestProjection = Get-SCSMObjectProjection -ProjectionName $serviceRequestTypeProjection.Name -filter “ID -eq $serviceRequestId”

    $SR = Get-SCSMObject -Class (Get-SCSMClass -Name system.workitem.servicerequest$) -Filter "Id -eq $serviceRequestId"
    
    C:\PowerShell\FixActivites.ps1 -Id $SR.get_id() -TemplateId $serviceRequestTemplate.get_id()

    #get the first Activity
    $firstActivity = Get-SCSMRelatedObject -SMObject $SR |?{$_.classname -like "System.WorkItem.Activity*" -and $_.sequenceID -eq "0"}
    write-host ("For SR " + $serviceRequestId + ", Found First Activity " + $firstActivity)

    # Set ActivityStatus to in progress
    Set-SCSMObject -SMObject $firstActivity -Property status -Value "in progress"
    write-host ("Set First Activity to in progress " + $firstActivity)
}

Script for the referenced C:\PowerShell\FixActivites.ps1

# //***************************************************************************
# // Author:  Morten Meisler, Coretech A/S. http://blog.coretech.dk/mme
# //   
# // Usage: Script to apply a template with activities on a workitem and correct
# //        id prefixes on each activity
# //        
# //
# // CORETECH A/S History:
# // 1.0    27/12/2013   beta version 1.
# // 1.1    02-01-2014   Fixed issue with activities containing other objects (like system.reviewer)                       
# //***************************************************************************

Param ([guid]$Id =$(throw "Please provide guid of the workitem"), 
       [string]$TemplateDisplayName,
       [guid]$TemplateId,
       [string]$ComputerName = "localhost")

#----------------------------------------------------
#Function to get activity id prefix from the activity settings
#----------------------------------------------------
function Get-SCSMObjectPrefix
{
    Param ([string]$ClassName =$(throw "Please provide a classname"))
    
    switch ($ClassName)
    {
        default 
        {
            #Get prefix from Activity Settings
            if ($ClassName.StartsWith("System.WorkItem.Activity") -or $ClassName.Equals("Microsoft.SystemCenter.Orchestrator.RunbookAutomationActivity"))
            {
                $ActivitySettingsObj = Get-SCSMObject -Class (Get-SCSMClass -Id "5e04a50d-01d1-6fce-7946-15580aa8681d")
                
                if ($ClassName.Equals("System.WorkItem.Activity.ReviewActivity")) {$prefix = $ActivitySettingsObj.SystemWorkItemActivityReviewActivityIdPrefix}
                if ($ClassName.Equals("System.WorkItem.Activity.ManualActivity")) {$prefix = $ActivitySettingsObj.SystemWorkItemActivityManualActivityIdPrefix}
                if ($ClassName.Equals("System.WorkItem.Activity.ParallelActivity")) {$prefix = $ActivitySettingsObj.SystemWorkItemActivityParallelActivityIdPrefix}
                if ($ClassName.Equals("System.WorkItem.Activity.SequentialActivity")) {$prefix = $ActivitySettingsObj.SystemWorkItemActivitySequentialActivityIdPrefix}
                if ($ClassName.Equals("System.WorkItem.Activity.DependentActivity")) {$prefix = $ActivitySettingsObj.SystemWorkItemActivityDependentActivityIdPrefix}
                if ($ClassName.Equals("Microsoft.SystemCenter.Orchestrator.RunbookAutomationActivity")) {$prefix = $ActivitySettingsObj.MicrosoftSystemCenterOrchestratorRunbookAutomationActivityBaseIdPrefix
                }
            } 
            else {throw "Class Name $ClassName is not supported"}
        }
    }
    return $prefix
}
#----------------------------------------------------
#Function to set id prefix to activities in template
#----------------------------------------------------
function Update-SCSMPropertyCollection
{
    Param ([Microsoft.EnterpriseManagement.Configuration.ManagementPackObjectTemplateObject]$Object =$(throw "Please provide a valid template object")) 
    
    #Regex - Find class from template object property between ! and ']
    $pattern = '(?<=!)[^!]+?(?=''\])'
    if (($Object.Path) -match $pattern -and ($Matches[0].StartsWith("System.WorkItem.Activity") -or $Matches[0].StartsWith("Microsoft.SystemCenter.Orchestrator")))
    {
        #Set prefix from activity class
        $prefix = Get-SCSMObjectPrefix -ClassName $Matches[0]
       
        #Create template property object
        $propClass = [Microsoft.EnterpriseManagement.Configuration.ManagementPackObjectTemplateProperty]
        $propObject = New-Object $propClass

        #Add new item to property object
        $propObject.Path = "`$Context/Property[Type='$alias!System.WorkItem']/Id$"
        $propObject.MixedValue = "$prefix{0}"
        
        #Add property to template
        $Object.PropertyCollection.Add($propObject)
        
        #recursively update activities in activities
        if ($Object.ObjectCollection.Count -ne 0)
        {
            foreach ($obj in $Object.ObjectCollection)
            {  
                Update-SCSMPropertyCollection -Object $obj
            }        
        }
    } 
}
#----------------------------------------------------
#Function to apply template after it has been updated
#----------------------------------------------------
function Apply-SCSMTemplate 
{
    Param ([Microsoft.EnterpriseManagement.Common.EnterpriseManagementObjectProjection]$Projection =$(throw "Please provide a valid projection object"), 
    [Microsoft.EnterpriseManagement.Configuration.ManagementPackObjectTemplate]$Template = $(throw 'Please provide an template object, ex. -template template'))

    #Get alias from system.workitem.library managementpack to set id property
    $templateMP = $Template.GetManagementPack() 
    $alias = $templateMP.References.GetAlias((Get-SCSMManagementPack system.workitem.library))
    
    #Update Activities in template
    foreach ($TemplateObject in $Template.ObjectCollection)
    {
        Update-SCSMPropertyCollection -Object $TemplateObject
    }
    #Apply update template
    Set-SCSMObjectTemplate -Projection $Projection -Template $Template -ErrorAction Stop
    Write-Host "Successfully applied template:`n"$template.DisplayName "`nTo:`n"$Projection.Object 
}

#--------------------------------
#INITIALIZE
#--------------------------------
$SMDefaultComputer = $ComputerName

#Load SMlets module
if (!(Get-Module smlets)) {Import-Module smlets -force -ErrorAction stop}

#Get object from guid
$emo = get-scsmobject -id $id

#determine projection according to workitem type
switch ($emo.GetLeastDerivedNonAbstractClass().Name)
{
    "System.workitem.Incident" {$projName = "System.WorkItem.Incident.ProjectionType" }
    "System.workitem.ServiceRequest" {$projName = "System.WorkItem.ServiceRequestProjection"}
    "System.workitem.ChangeRequest" {$projName = "System.WorkItem.ChangeRequestProjection"}
    "System.workitem.Problem" {$projName = "System.WorkItem.Problem.ProjectionType"}
    "System.workitem.ReleaseRecord" {$projName = "System.WorkItem.ReleaseRecordProjection"}
    
    default {throw "$emo is not a supported workitem type"}
}
#Get object projection
$emoID = $emo.id
$WIproj = Get-SCSMObjectProjection -ProjectionName $projName -Filter "Id -eq $emoID"

#Get template from displayname or id
if ($TemplateDisplayName)
{
    $template = Get-SCSMObjectTemplate -DisplayName $TemplateDisplayName
}
elseif ($templateId)
{
    $template = Get-SCSMObjectTemplate -id $TemplateId
}
else
{
    throw "Please provide either a template id or a template displayname to apply"
}

#Execute apply-template function if id and 1 template exists
if (@($template).count -eq 1)
{
    
    if ($WIProj)
    {
        Apply-SCSMTemplate -Projection $WIproj -Template $template
    }
    else
    {throw "Id $Id cannot be found";}
}
else{throw "Template cannot be found or there was more than one result"}

Tuan_Luu · March 2020

It is working like a champ!

Create SRs in bulk from an Excel file

Best Answers

Answers

Create SRs in bulk from an Excel file

Best Answers

Answers

CIRESON COMMUNITY WEB SITE