Home› Advanced Request Offering

Creating service requests via powershell

Ryan_KelleyRyan_Kelley Customer IT Monkey ✭
edited April 2023 in Advanced Request Offering

Use case: auto creating vulnerability tickets for techs from a csv file. If anyone can help :)

I have a script that works well enough BUT

The last items I am struggling with is:


How do I set the usertext1 am hoping for it to be the hostname(in extensions tab) and how to set the affected user.

image.png
image.png

For affected user I would to include a domain user but if an easier solution is found like creating a user scsm - I made a Firstname: Security Lastname: Vulnerability user inside config items/ users

image.png

The last item was to combine the hostname's if they are the same and make the description different:

IE if only one is found:

CVSS sore:

Title:

Proof URL:

If more than one is found:

Title 1:

Proof url 1:

Title 2:

Proof url 2:

ECT


My Script below only omitting confidential info:

# Set default computer for SMlets
$GLOBAL:smdefaultcomputer = "test server"

# Import SMlets module
Import-Module SMlets

# Get SCSM class and enumerations
$srClass = Get-SCSMClass -name System.WorkItem.ServiceRequest$
$srprior = Get-SCSMEnumeration -Name ServiceRequestPriorityEnum.Medium
$srurg = Get-SCSMEnumeration -Name ServiceRequestUrgencyEnum.Medium
$ararea = get-SCSMEnumeration -Name Enum.add3768303064ec18890170ba33cffda
$source = Get-SCSMEnumeration -name Enum.c933f53299cd460c867871e0662be07d
#$affectedUser = Get-SCSMObject -Class (Get-SCSMClass -Name System.Domain.User$) -Filter "UserName -eq '*Vulnerability*"

# Read in CSV file
$csvPath = "C:\test\data.csv"
$requests = Import-Csv -Path $csvPath

# Initialize the result list
$resultList = @()

# Initialize previous host name and description list
$previousHostName = ""
$descriptionList = @()

# Loop through each row in CSV file and create service request
foreach ($request in $requests) {
  # Check for blank row or host_name with "Total"
  if ([string]::IsNullOrWhiteSpace($request.Title) -or [string]::IsNullOrWhiteSpace($request.host_name) -or $request.host_name -eq "Total") {
    continue
  }

  # Extract data from CSV row
  $title = $request.Title
  $descrip = $request.Description
  $hostName = $request.host_name

  # Check for host_name conditions
  $supportGroup = "Enum.7d3ab9057e1040248c4fc3b9998351ff"
  if ($hostName -match "SHR") {
    $supportGroup = "Enum.a8a0f089027f4b74ad15e8cfe39f8c86"
  } elseif ($hostName -match "SQL") {
    $supportGroup = "Enum.5cc39a482dab48e186615223f4d7e4ff"
  }

  # Set the due date
  $today = Get-Date
  $sloCountdown = [int]$request.'SLO Countdown'
  $dueDate = $today.AddDays($(if ($sloCountdown -lt 0) { 7 } else { $sloCountdown }))

  # Set the title
  $title = "vuln - $title on $hostName custodian: $($request.'SCSM Custodian') - Due date: $($dueDate.ToString('yyyy-MM-dd'))"

  # Set the description
  if ($hostName -eq $previousHostName) {
    $descriptionList += "Title: $($request.Title) Proof URL: $($request.'Proof URL')"
    $description = "Proof 1 CVSS score: $($request.RCS_CVSS) $($descriptionList -join '; ')"
  } else {
    $descriptionList = @("Title: $($request.Title) Proof URL: $($request.'Proof URL')")
    $description = "Proof 1 CVSS score: $($request.RCS_CVSS) $($descriptionList -join '; ')"
  } # This is the missing closing brace

  # Set service request arguments
 $srargs = @{
  Title = $title;
  Urgency = $srurg;
  Priority = $srprior;
  ID = "SR{0}";
  Area = $ararea;
  SupportGroup = $supportGroup;
  Description = $description;
  #AffectedUser = $affectedUser;
  Status = "New";
}

try {
  # Create service request
  $newServiceRequest = New-SCSMObject -Class $srClass -PropertyHashtable $srargs -PassThru
  $SRId = $newServiceRequest.id
  $srTypeProjection = Get-SCSMTypeProjection -name System.WorkItem.ServiceRequestProjection$
  $SRProj = Get-scsmobjectprojection -ProjectionName $srTypeProjection.Name -filter “Id -eq $SRId”

  # Output confirmation message
  Write-Host "Created service request $($newServiceRequest.Id) for $title"
  $resultList += "Created service request $($newServiceRequest.Id) for $title"
} catch {
  $errorMessage = "Error creating service request for $title`n$($_.Exception.Message)"
  Write-Host $errorMessage -ForegroundColor Red
  $resultList += $errorMessage
}

# Update previous host name
$previousHostName = $hostName
}

# Generate a timestamp for the filename
$timestamp = Get-Date -Format "yyyyMMdd-HHmmss"

# Generate a text file with the results
$outputPath = "C:\Users\rkelley\Documents\results-$timestamp.txt"
$resultList | Out-File -FilePath $outputPath

#Delete the CSV file
#Remove-Item $csvPath

# Output final result summary
Write-Host "The script has completed. The results have been saved to $outputPath."
Write-Host "The CSV file has been deleted."


Answers

  • Simon_ZeinhoferSimon_Zeinhofer Customer Ninja IT Monkey ✭✭✭✭
    edited April 2023

    for the affected user, this line has to be changed from

    $affectedUser = Get-SCSMObject -Class (Get-SCSMClass -Name System.Domain.User$) -Filter "UserName -eq '*Vulnerability*"

    to

    $affectedUser = Get-SCSMObject -Class (Get-SCSMClass -Name System.Domain.User$) -Filter "UserName -like '*Vulnerability*"

    Also the affected user is not a property, it is a relationship, so you cannot set it inside the hashtable, you have to create a new relationship via

    new-scsmrelationshipobject...

    And I don't know if it is on purpose that the affected user is commented out

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭
    edited April 2023

    Hello!


    I commented the affected user part out as it was not working. I wanted to give a powershell script that worked or was stable at the moment. Kinda like how I commented out the csv file - I do not want to delete it now during testing :)

    I fixed the code and now affected user is now showing!! Now just for the other stuff! :) -- If any help comes I feel this stuff might help future powershell amateurs as I started with really nothing haha


    # Set default computer for SMlets
$GLOBAL:smdefaultcomputer = "CINSHRSSMT4"

# Import SMlets module
Import-Module SMlets

# Get SCSM class and enumerations
$srClass = Get-SCSMClass -name System.WorkItem.ServiceRequest$
$srprior = Get-SCSMEnumeration -Name ServiceRequestPriorityEnum.Medium
$srurg = Get-SCSMEnumeration -Name ServiceRequestUrgencyEnum.Medium
$ararea = get-SCSMEnumeration -Name Enum.add3768303064ec18890170ba33cffda
$source = Get-SCSMEnumeration -name Enum.c933f53299cd460c867871e0662be07d
$affectedUser = Get-SCSMObject -Class (Get-SCSMClass -Name System.Domain.User$) -Filter "UserName -like '*Vulnerability*"
$affectedUserRelClass = Get-SCSMRelationshipClass -Name System.WorkItemAffectedUser


# Read in CSV file
$csvPath = "C:\test\data.csv"
$requests = Import-Csv -Path $csvPath

# Initialize the result list
$resultList = @()

# Initialize previous host name and description list
$previousHostName = ""
$descriptionList = @()

# Loop through each row in CSV file and create service request
foreach ($request in $requests) {
  # Check for blank row or host_name with "Total"
  if ([string]::IsNullOrWhiteSpace($request.Title) -or [string]::IsNullOrWhiteSpace($request.host_name) -or $request.host_name -eq "Total") {
    continue
  }

  # Extract data from CSV row
  $title = $request.Title
  $descrip = $request.Description
  $hostName = $request.host_name

  # Check for host_name conditions
  $supportGroup = "Enum.7d3ab9057e1040248c4fc3b9998351ff"
  if ($hostName -match "SHR") {
    $supportGroup = "Enum.a8a0f089027f4b74ad15e8cfe39f8c86"
  } elseif ($hostName -match "SQL") {
    $supportGroup = "Enum.5cc39a482dab48e186615223f4d7e4ff"
  }

  # Set the due date
  $today = Get-Date
  $sloCountdown = [int]$request.'SLO Countdown'
  $dueDate = $today.AddDays($(if ($sloCountdown -lt 0) { 7 } else { $sloCountdown }))

  # Set the title
  $title = "vuln - $title on $hostName custodian: $($request.'SCSM Custodian') - Due date: $($dueDate.ToString('yyyy-MM-dd'))"

  # Set the description
  if ($hostName -eq $previousHostName) {
    $descriptionList += "Title: $($request.Title) Proof URL: $($request.'Proof URL')"
    $description = "Proof 1 CVSS score: $($request.RCS_CVSS) $($descriptionList -join '; ')"
  } else {
    $descriptionList = @("Title: $($request.Title) Proof URL: $($request.'Proof URL')")
    $description = "Proof 1 CVSS score: $($request.RCS_CVSS) $($descriptionList -join '; ')"
  } # This is the missing closing brace

  # Set service request arguments
 $srargs = @{
  Title = $title;
  Urgency = $srurg;
  Priority = $srprior;
  ID = "SR{0}";
  Area = $ararea;
  SupportGroup = $supportGroup;
  Description = $description;
  #AffectedUser = $affectedUser;
  Status = "New";
}

try {
# Create service request
$newServiceRequest = New-SCSMObject -Class $srClass -PropertyHashtable $srargs -PassThru
$SRId = $newServiceRequest.id
$srTypeProjection = Get-SCSMTypeProjection -name System.WorkItem.ServiceRequestProjection$
$SRProj = Get-scsmobjectprojection -ProjectionName $srTypeProjection.Name -filter “Id -eq $SRId”

# Create Affected User relationship
New-SCSMRelationshipObject -RelationShip $affectedUserRelClass -Source $newServiceRequest -Target $affectedUser -Bulk


  # Output confirmation message
  Write-Host "Created service request $($newServiceRequest.Id) for $title"
  $resultList += "Created service request $($newServiceRequest.Id) for $title"
} catch {
  $errorMessage = "Error creating service request for $title`n$($_.Exception.Message)"
  Write-Host $errorMessage -ForegroundColor Red
  $resultList += $errorMessage
}

# Update previous host name
$previousHostName = $hostName
}

# Generate a timestamp for the filename
$timestamp = Get-Date -Format "yyyyMMdd-HHmmss"

# Generate a text file with the results
$outputPath = "C:\Users\rkelley\Documents\results-$timestamp.txt"
$resultList | Out-File -FilePath $outputPath

#Delete the CSV file
#Remove-Item $csvPath

# Output final result summary
Write-Host "The script has completed. The results have been saved to $outputPath."
Write-Host "The CSV file has been deleted."


  • Simon_ZeinhoferSimon_Zeinhofer Customer Ninja IT Monkey ✭✭✭✭

    @Ryan_Kelley as you described it here, I don't know what you want to accomplish sry.

    I understand it that way: if you have one and the same hostname in your csv, you only want ONE Request to be created (not one for every line) and the description to be updated with the next url from the same host.

    If that's correct I would do it this way. At first you always want to check, if there is already an SR with that hostname. And if one exists, you just update the SR description. If not, you create a new one AND set the usertext1 property to the hostname.

    $hostName = $request.host_name
#check if there is already an existing SR for that. The ? {...} filters the requests for the ones which are Active Only
$existingSR = Get-scsmobject -class $srClass -filter "UserText1 -eq $hostName" | ? {$_.Status.DisplayName -in 'In Progress','Submitted'}
if($existingSR)
{
Set-scsmobject -smobject $existingSR -property Description -value "$($existingSR.Description)`nProof 1 CVSS score: $($request.RCS_CVSS) $($descriptionList -join '; ')" -passthru
}
else
{
   try {
   # Create service request
   ... YOUR CODE
   #add this code to write the hostname inside the usertext1 property
   Set-scsmobject -smobject $newServiceRequest -property UserText1 -value $hostName
   }
   catch
   {
   ...
   }
}

    Again, as I don't know what you want to accomplish I can only guess what you want and how I would do it.

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    Happy to explain!


    My goals for the question are:

    Found out how to put affected user into a ticket created by powershell – done! Thank you!


    2) how do I set all tickets row by row to be the hostname- there is a column in my csv file called hostname but I do not know how to put it into usertext1


    3) How do I detect – as the foreachrow is implemented – catch that the hostname is used more than once and then store the vulnerabilities.


    Mind you - any help given is highly appreciated - the knowledge of how to get usertext1 into a ticket would be the biggest help! :)

    IE

    If there is a row with no duplicate hostnames – do nothing and generate ticket

    CVSS sore:

    Title:

    Proof URL:


    If more than one row has the same hostname then:

    Title 1:

    Proof url 1:

    Title 2:

    Proof url 2:

    ECT



    I do like the idea of a catch though! If a hostname and the same vulnerability is already within the system – it might be a good idea to generate a ticket with something like: This could be good for reporting purposes:

    Title: "vuln -REPEAT VULN- $title on $hostName custodian: $($request.'SCSM Custodian') - Due date: $($dueDate.ToString('yyyy-MM-dd'))"

  • Simon_ZeinhoferSimon_Zeinhofer Customer Ninja IT Monkey ✭✭✭✭
    https://community.cireson.com/discussion/comment/24639#Comment_24639

    So point 2 is exactly what I implemented in the comment above ;)

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭

    This might also help

    https://community.cireson.com/discussion/comment/14027#Comment_14027

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    Hopefully this helps someone as this was a bit of a journey:

    # Set default computer for SMlets

    $GLOBAL:smdefaultcomputer = "CINSHRSSMT4"


    # Import SMlets module

    Import-Module SMlets


    # Get SCSM class and enumerations

    $srClass = Get-SCSMClass -name System.WorkItem.ServiceRequest$

    $srprior = Get-SCSMEnumeration -Name ServiceRequestPriorityEnum.Medium

    $srurg = Get-SCSMEnumeration -Name ServiceRequestUrgencyEnum.Medium

    $ararea = Get-SCSMEnumeration -name Enum.add3768303064ec18890170ba33cffda

    $source = Get-SCSMEnumeration -name Enum.c933f53299cd460c867871e0662be07d

    $affectedUser = Get-SCSMObject -Class (Get-SCSMClass -Name System.Domain.User$) -Filter "UserName -like '*vulnerability*'"

    $affectedUserRelClass = Get-SCSMRelationshipClass -Name System.WorkItemAffectedUser


    # Read in CSV file

    $csvPath = "C:\test\data.csv"

    $requests = Import-Csv -Path $csvPath


    # Initialize the result list

    $resultList = @()


    # Initialize previous host name and description list

    $previousHostName = ""

    $descriptionList = @()


    # Loop through each row in CSV file and create service request

    foreach ($request in $requests) {

      # Check for blank row or host_name with "Total"

      if ([string]::IsNullOrWhiteSpace($request.Title) -or [string]::IsNullOrWhiteSpace($request.host_name) -or $request.host_name -eq "Total") {

        continue

      }


      # Extract data from CSV row

      $title = $request.Title

      $descrip = $request.Description

      $hostName = $request.host_name


      # Check for host_name conditions

      $supportGroup = "Enum.7d3ab9057e1040248c4fc3b9998351ff"

      if ($hostName -match "") {

        $supportGroup = "Enum.a8a0f089027f4b74ad15e8cfe39f8c86"

      } elseif ($hostName -match "") {

        $supportGroup = "Enum.5cc39a482dab48e186615223f4d7e4ff"

      }


      # Set the due date

      $today = Get-Date

      $sloCountdown = [int]$request.'SLO Countdown'

      $dueDate = $today.AddDays($(if ($sloCountdown -lt 0) { 7 } else { $sloCountdown }))


      # Set the title and description

      if ($hostName -eq $previousHostName) {

        $descriptionList += "`r`nTitle: $($request.Title)`r`nProof URL: $($request.'Proof URL')"

        $finalTitle = "Multiple Vulnerabilities on $hostName custodian: $($request.'SCSM Custodian') - Due date: $($dueDate.ToString('yyyy-MM-dd'))"

      } else {

        if ($descriptionList.Count -gt 0) {

          # Generate ticket for the previous hostname

          $description = "CVSS score: $($previousRequest.RCS_CVSS)`r`n$($descriptionList -join '`r`n')"

          # Set service request arguments

          $srargs = @{

            Title = $finalTitle;

            Urgency = $srurg;

            Priority = $srprior;

            ID = "SR{0}";

            Area = $ararea;

            SupportGroup = $supportGroup;

            Description = $description;

            Source = $source;

             UserText1 = $hostName;

            #AffectedUser = $affectedUser;

            Status = "New";

          }


          try {

            # Create service request

            $newServiceRequest = New-SCSMObject -Class $srClass -PropertyHashtable $srargs -PassThru

            $SRId = $newServiceRequest.id

            $srTypeProjection = Get-SCSMTypeProjection -name System.WorkItem.ServiceRequestProjection$

            $SRProj = Get-scsmobjectprojection -ProjectionName $srTypeProjection.Name -filter “Id -eq $SRId”


            # Create Affected User relationship

            New-SCSMRelationshipObject -RelationShip $affectedUserRelClass -Source $newServiceRequest -Target $affectedUser -Bulk


            # Output confirmation message

            Write-Host "Created service request $($newServiceRequest.Id) for $title"

            $resultList += "Created service request $($newServiceRequest.Id) for $title"

          } catch {

            $errorMessage = "Error creating service request for $title`n$($_.Exception.Message)"

            Write-Host $errorMessage -ForegroundColor Red

            $resultList += $errorMessage

          }


          # Clear the list for the next hostname

          $descriptionList = @()

        }


        $descriptionList = @("`r`nTitle: $($request.Title)`r`nProof URL: $($request.'Proof URL')")

        $finalTitle = "vuln - $title on $hostName custodian: $($request.'SCSM Custodian') - Due date: $($dueDate.ToString('yyyy-MM-dd'))"

      }


      # Update previous host name

      $previousHostName = $hostName

      $previousRequest = $request

    }


    # Generate ticket for the last hostname

    if ($descriptionList.Count -gt 0) {

      $description = "CVSS score: $($previousRequest.RCS_CVSS)`r`n$($descriptionList -join '`r`n')"

      # Set service request arguments

      $srargs = @{

            Title = $finalTitle;

        Urgency = $srurg;

        Priority = $srprior;

        ID = "SR{0}";

        Area = $ararea;

        SupportGroup = $supportGroup;

        Description = $description;

        Source = $source;

         UserText1 = $hostName;

        #AffectedUser = $affectedUser;

        Status = "New";

      }


      try {

        # Create service request

        $newServiceRequest = New-SCSMObject -Class $srClass -PropertyHashtable $srargs -PassThru

        $SRId = $newServiceRequest.id

        $srTypeProjection = Get-SCSMTypeProjection -name System.WorkItem.ServiceRequestProjection$

        $SRProj = Get-scsmobjectprojection -ProjectionName $srTypeProjection.Name -filter “Id -eq $SRId”


        # Create Affected User relationship

        New-SCSMRelationshipObject -RelationShip $affectedUserRelClass -Source $newServiceRequest -Target $affectedUser -Bulk


        # Output confirmation message

        Write-Host "Created service request $($newServiceRequest.Id) for $title"

        $resultList += "Created service request $($newServiceRequest.Id) for $title"

      } catch {

        $errorMessage = "Error creating service request for $title`n$($_.Exception.Message)"

        Write-Host $errorMessage -ForegroundColor Red

        $resultList += $errorMessage

          }


      # Update previous host name

      $previousHostName = $hostName

      $previousRequest = $request

    }


    # Generate a timestamp for the filename

    $timestamp = Get-Date -Format "yyyyMMdd-HHmmss"


    # Generate a text file with the results

    $outputPath = "C:\Users\username\Documents\results-$timestamp.txt"

    $resultList | Out-File -FilePath $outputPath


    #Delete the CSV file

    #Remove-Item $csvPath


    # Output final result summary

    Write-Host "The script has completed. The results have been saved to $outputPath."

    Write-Host "The CSV file has been deleted."

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    Last question if anyone can help:

     If a hostname and the same vulnerability is already within the system – it might be a good idea to generate a ticket with something like: This could be good for reporting purposes:

    Like check all tickets if a ticket has already been generated and then add - repeat vuln- Maybe this would be something toward then end?

    Title: "vuln -REPEAT VULN- $title on $hostName custodian: $($request.'SCSM Custodian') - Due date: $($dueDate.ToString('yyyy-MM-dd'))"

Sign In or Register to comment.