Home Analyst Portal

Create SRs in bulk from an Excel file

Paul_DalkinPaul_Dalkin Member IT Monkey ✭
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.

Best Answers

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    edited April 2018 Answer ✓
    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"}

Answers

  • Roland_KindRoland_Kind Partner Advanced IT Monkey ✭✭✭

    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_DalkinPaul_Dalkin Member IT Monkey ✭
    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.
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    edited April 2018 Answer ✓
    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_LuuTuan_Luu Customer IT Monkey ✭

    It is working like a champ!

Sign In or Register to comment.