Write a powershell script to create a new record and attach 1 image

This topic contains 2 replies, has 2 voices, and was last updated by  Deepansh 3 months, 2 weeks ago.

  • Author
    Posts
  • #90149

    Deepansh
    Participant

    Updated code requirement : to insert a new record and add 1 image as an attachment.
    I have a requirement where i need to write a powershell script .
    there are some images stored in a server .
    now on this script execution i want a record to be created and 1 photo to be attached to that record
    I am supposed to bring those images and records in a tool(Servicenow)
    right now my script is updating the existing records but i want to insert a record and then attach 1 image to it.
    I came up with attached script but getting below error .

    Below is the error when running the updated script
    Error : Invoke-WebRequest : The remote server returned an error: (500) Internal Server Error.
    At D:\SOAP\Import_SNOW_Attachments_copy.ps1:189 char:18
    + ... ]$Request = Invoke-WebRequest $snowLookup -Headers $Headers -Method P ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], W
    eption
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

    Line 189 – [xml]$Request = Invoke-WebRequest $snowLookup -Headers $Headers -Method Post -ContentType "text/xml" -InFile $LookXMLpath

    Can someone please guide me here.
    Below is the code :

    Updated code requirement : to insert a new record and add 1 image as an attachment.
    
    
    
    #Update working directory to the name of the folder holding your script
    #EXAMPLE: "C:\soap\"
    
    #Update Export Directory to the name of the folder holding the export folders / files
    #EXAMPLE: "C:\soap\export_files"
    
    #Update to reflect your SNOW instance
    
    # Enter SNOW authentication here - EXAMPLES LISTED
    
    
                                                                                    
                                                                    ####################################################################################################################################################
                                                                    ####################################################################################################################################################
                                                                                    ####################################################
                                                                                    #                                                  # 
                                                                                    #      Taken from http://poshcode.org/4845         #
                                                                                    #       Function creates ZIP file                  #
                                                                                    #                                                  #
                                                                                    ####################################################
                                                                                    #Add-Type -As System.IO.Compression.FileSystem
                                                                                    [Reflection.Assembly]::LoadWithPartialName("System.IO.Compression.FileSystem"); 
                                                                                    
                                                                                                    function NewZipFile {
                                                                                                      #.Synopsis
                                                                                                      #  Create a new zip file, optionally appending to an existing zip...
                                                                                                      [CmdletBinding()]
                                                                                                      param(
                                                                                                                    # The path of the zip to create
                                                                                                                    [Parameter(Position=0, Mandatory=$true)]
                                                                                                                    $ZipFilePath,
                                                                                                    
                                                                                                                    # Items that we want to add to the ZipFile
                                                                                                                    [Parameter(Position=1, Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
                                                                                                                    [Alias("PSPath","Item")]
                                                                                                                    [string[]]$InputObject = $Pwd,
                                                                                                    
                                                                                                                    # Append to an existing zip file, instead of overwriting it
                                                                                                                    [Switch]$Append,
                                                                                                    
                                                                                                                    # The compression level (defaults to Optimal):
                                                                                                                    #   Optimal - The compression operation should be optimally compressed, even if the operation takes a longer time to complete.
                                                                                                                    #   Fastest - The compression operation should complete as quickly as possible, even if the resulting file is not optimally compressed.
                                                                                                                    #   NoCompression - No compression should be performed on the file.
                                                                                                                    [System.IO.Compression.CompressionLevel]$Compression = "Optimal"
                                                                                                      )
                                                                                                      begin {
                                                                                                                    # Make sure the folder already exists
                                                                                                                    [string]$File = Split-Path $ZipFilePath -Leaf
                                                                                                                    [string]$Folder = $(if($Folder = Split-Path $ZipFilePath) { Resolve-Path $Folder } else { $Pwd })
                                                                                                                    $ZipFilePath = Join-Path $Folder $File
                                                                                                                    # If they don't want to append, make sure the zip file doesn't already exist.
                                                                                                                    if(!$Append) {
                                                                                                                      if(Test-Path $ZipFilePath) { Remove-Item $ZipFilePath }
                                                                                                                    }
                                                                                                                    $Archive = [System.IO.Compression.ZipFile]::Open( $ZipFilePath, "Update" )
                                                                                                      }
                                                                                                      process {
                                                                                                                    foreach($path in $InputObject) {
                                                                                                                      foreach($item in Resolve-Path $path) {
                                                                                                                                    # Push-Location so we can use Resolve-Path -Relative
                                                                                                                                    Push-Location (Split-Path $item)
                                                                                                                                    # This will get the file, or all the files in the folder (recursively)
                                                                                                                                    foreach($file in Get-ChildItem $item -Recurse -File -Force | % FullName) {
                                                                                                                                      # Calculate the relative file path
                                                                                                                                      $relative = (Resolve-Path $file -Relative).TrimStart(".\")
                                                                                                                                      # Add the file to the zip
                                                                                                                                      $null = [System.IO.Compression.ZipFileExtensions]::CreateEntryFromFile($Archive, $file, $relative, $Compression)
                                                                                                                                    }
                                                                                                                                    Pop-Location
                                                                                                                      }
                                                                                                                    }
                                                                                                      }
                                                                                                      end {
                                                                                                                    $Archive.Dispose()
                                                                                                                    Get-Item $ZipFilePath
                                                                                                      }
                                                                                                    }
                                                                    ####################################################################################################################################################
                                                                    ####################################################################################################################################################
    
    #Set Log file for writing
    $Logfile = $workingdir+"\logfile.txt"
    if (Test-Path $Logfile) { remove-item $Logfile }
    if (Test-Path "$workingdir\logfile.csv") { remove-item "$workingdir\logfile.csv"}
    
    Add-content $Logfile -value "File_Name,Uploaded_As,To_Table,Where_Field,Has_Value,Attached_To_Sys_ID,Confirmation_ECC_Insert_Sys_ID"
    
    
    get-childitem $ExportDir -Recurse  | Where-Object{!($_.PSIsContainer)} | % {
         
                    $filePath = $_.FullName
                    
                    #Get file name
                    $fileName = Split-Path -Path $filePath -Leaf
                    $inFile = Split-Path -Path $filePath -Leaf
    
                    #Get name of folder containing the file 
                    $parentFolderFull = Split-Path -Path $filePath -Parent
                    
                    #this is the known value of the record that will be used to query to get the sys_ID)
                    $qValue = Split-Path -Path $parentFolderFull -Leaf
                    $parentFolderFull  = $parentFolderFull + '\'
                    
                    #Pulls the next level up folder name (this is the  name of the field being queried to get the sys_ID)
                    $qFieldFull = Split-Path -Path $parentFolderFull -Parent 
                    $qField = Split-Path $qFieldFull -Leaf
                    
                    $qTableFull = $qFieldFull = Split-Path -Path $qFieldFull -Parent 
                    $qTable = Split-Path $qTableFull -Leaf
    
                    Write-Host $qTable\$qField\$qValue\$fileName -ForegroundColor Green
                    
    
    
                    #Set parent folder as the Import folder
                    #Will be used to place ZIP file if extension is unknown
                    $importFileDir = $parentFolder
    
                    #Get file name without extension
                    #Will be used to name ZIP file if extension is unknown
                    $shortFileName = [System.IO.Path]::GetFileNameWithoutExtension($fileName)
    
    
                    #Set output file for BASE64 conversion
                    $Base64File = $workingdir+"PS_certutil.txt"
    
                    #Get extension from file name for registry MIME lookup
                    $extension = [System.IO.Path]::GetExtension($fileName)
    
    
    
                    ################################################
                    #          START XML CREATION FOR LOOKUP       #
                    ################################################
        
                    # Path for XML output
                    $LookXMLpath = "$workingdir\lookupXML.xml"             
                    
                    # Set up encoding, and create new file for XML Lookup
                    [Reflection.Assembly]::LoadWithPartialName("System.Text"); 
                    $encoding = [System.Text.Encoding]::UTF8
                    [Reflection.Assembly]::LoadWithPartialName("System.Xml.XmlTextWriter"); 
                    $LOOKwriter = New-Object System.Xml.XmlTextWriter( $LookXMLpath, $encoding )
                    $LOOKwriter.Formatting = [system.xml.formatting]::indented
                     
                    Write-Host "Generating XML Sys_ID Lookup file..."
                     
                    # Write start of XML Lookup document
                    [Reflection.Assembly]::LoadWithPartialName("System.IO.Compression.FileSystem"); 
                    $LOOKwriter.WriteStartDocument()
                     
                    $LOOKwriter.WriteStartElement( "soapenv:Envelope" )
                                    $LOOKwriter.WriteAttributeString( "xmlns:soapenv", "http://schemas.xmlsoap.org/soap/envelope/")
                                    $LOOKwriter.WriteAttributeString( "xmlns:rec", "http://www.service-now.com/$qTable" )
                                        $LOOKwriter.WriteStartElement("soapenv:Header")
                                                    $LOOKwriter.WriteEndElement()
                                        $LOOKwriter.WriteStartElement("soapenv:Body")
                                                                    $LOOKwriter.WriteStartElement("rec:getRecords")
                                                                                    #$LOOKwriter.WriteStartElement("$qField")
                                                                   #$LOOKwriter.WriteString("$qValue")
                                                                                    #$LOOKwriter.WriteEndElement()
                                                                    #$LOOKwriter.WriteEndElement()
                                                    $LOOKwriter.WriteEndElement()
                    $LOOKwriter.WriteEndElement()
                                    
    
                    # Make sure we close the file
                    $LOOKwriter.close()
                     
    
                    # Create SNOW authentication and execute SOAP Web request
                    $pair = "$($user):$($pass)"
                    $encodedCreds = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes($pair))
                    $basicAuthValue = "Basic $encodedCreds"
    
                    $Headers = @{
                                    Authorization = $basicAuthValue
                    }
    
                    $snowLookup = "$snowInstance/$qTable.do?SOAP"
                    [xml]$Request = Invoke-WebRequest $snowLookup -Headers $Headers -Method Post -ContentType "text/xml" -InFile $LookXMLpath 
    
                    $Sys_ID = $Request.Envelope.Body.getRecordsResponse.getRecordsResult.sys_id
    
                    Write-Host $Sys_ID
    
                    try
                                    {
                                    #Get MIME value for extension
                                                    #Check for mapped registry key existence before creating it
                                                    if (!(Test-Path HKCR:\)) {
                                                    
                                                                    ##New-PSDrive usage info can be found at (https://technet.microsoft.com/en-us/library/Hh849829.aspx)
                                                                    New-PSDrive -Name HKCR -PSProvider Registry -Root HKEY_CLASSES_ROOT | Out-Null
                                                    }
                                    $Regkey = Get-Item "HKCR:\$extension" -ErrorAction Stop
                                    $values = Get-ItemProperty $Regkey.PSPath -ErrorAction Stop
                                    $mime = $values.'Content Type'
                                    }
    
                    #Catch errors for extensions not in registry
                    #If this happens, zip the file so it can be uploaded with recognizable mime type
                    catch [System.Management.Automation.ItemNotFoundException]
                                    {   
                                    write-host "Extension not found: Zipping File..." -ForegroundColor Yellow
                                    NewZipfile $parentFolderFull$shortFileName'_zipped.zip' $parentFolderFull$fileName | Out-Null
                                    $filename = $shortFileName+"_zipped.zip"
                                    $Regkey = Get-Item "HKCR:\.zip" 
                                    $values = Get-ItemProperty $Regkey.PSPath 
                                    $mime = $values.'Content Type'
                                    }
    
                    #Create BASE64 encoding
                    certutil -encode "$parentFolderFull$fileName" $Base64File
    
                    #Remove BEGIN and END lines from encoding
                    $fileContentEncoded  = [System.IO.File]::ReadAllText($Base64File).Replace("-----END CERTIFICATE-----","")
                    $fileContentEncoded  = $fileContentEncoded.Replace("-----BEGIN CERTIFICATE-----","")
    
    
                    ################################################
                    #          START XML CREATION FOR IMPORT       #
                    ################################################
                    [Reflection.Assembly]::LoadWithPartialName("System.IO.Compression.FileSystem"); 
                    # Path for XML output
                    $XMLpath = "$workingdir\SOAP.xml"             
                    
                    # Set up encoding, and create new instance of XMLTextWriter
                    $encoding = [System.Text.Encoding]::UTF8
                    $writer = New-Object System.Xml.XmlTextWriter( $XMLpath, $encoding )
                    $writer.Formatting = [system.xml.formatting]::indented
                     
                    Write-Host "Generating XML upload file... "$fileName":"$mime
                     
                    # Write start of XML document
                    [Reflection.Assembly]::LoadWithPartialName("System.IO.Compression.FileSystem"); 
                    $writer.WriteStartDocument()
                     
                    $writer.WriteStartElement( "soapenv:Envelope" )
                                    $writer.WriteAttributeString( "xmlns:soapenv", "http://schemas.xmlsoap.org/soap/envelope/")
                                    $writer.WriteAttributeString( "xmlns:ecc", "http://www.service-now.com/ecc_queue" )
                                    $writer.WriteAttributeString( "xmlns:rec", "http://www.service-now.com/u_u_fax_test" )                   // added table to insert
                                        $writer.WriteStartElement("soapenv:Header")
                                                    $writer.WriteEndElement()
                                        $writer.WriteStartElement("soapenv:Body")
                                        $writer.WriteStartElement("rec:insert")                                                                                                        // specified operation to insert
                                                                    $writer.WriteStartElement("ecc:insert")
                                                                                    $writer.WriteStartElement("agent")
                                                                   $writer.WriteString("AttachmentCreator")
                                                                                    $writer.WriteEndElement()
                                                                                    $writer.WriteStartElement("topic")
                                                                   $writer.WriteString("AttachmentCreator")
                                                                                    $writer.WriteEndElement()
                                                                                    $writer.WriteStartElement("name")
                                                                   $writer.WriteString($fileName+":"+$mime)
                                                                                    $writer.WriteEndElement()
                                                                                    $writer.WriteStartElement("source")
                                                                   $writer.WriteString($qTable+":"+$Sys_ID)                                                               // attach image to the specific records
                                                                                    $writer.WriteEndElement()
                                                                                    $writer.WriteStartElement("payload")
                                                                   $writer.WriteString($fileContentEncoded)
                                                                                    $writer.WriteEndElement()
                                                                    $writer.WriteEndElement()
                                                    $writer.WriteEndElement()
                    $writer.WriteEndElement()
                                    
                    # Make sure we close the file
                    $writer.close()
                     
                    # Create SNOW authentication and execute SOAP Web request
                    $pair = "$($user):$($pass)"
                    $encodedCreds = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes($pair))
                    $basicAuthValue = "Basic $encodedCreds"
                    $Headers = @{
                                    Authorization = $basicAuthValue
                    }
    
                    
                    $snowECC = "$snowInstance/ecc_queue.do?SOAP"
                    
                    [xml]$eccInsert = Invoke-WebRequest $snowECC -Headers $Headers -Method Post -ContentType "text/xml" -InFile $XMLpath 
                    
                    $Insert_Sys_ID = ''
                    $Insert_Sys_ID = $eccInsert.Envelope.Body.InsertResponse.sys_id
                    $log = "$inFile,$fileName,$qTable,$qField,$qValue,$Sys_ID,$Insert_Sys_ID"
                    Add-content $Logfile -value $log
                    
    
                    
                    # Cleanup temp files
                    remove-item $Base64File
                    remove-item $XMLpath
                    remove-item $LookXMLpath
    }
    
                    Rename-Item -path "$Logfile" -newName "$workingdir\logfile.csv"
    
  • #90155

    Don Jones
    Keymaster

    That's an exceedingly long script for me to parse through :). Also, just to let you know, I'm not sure you have a "PowerShell problem" per se. I think you have more of a "ServiceNow API problem," so it might be worth your while to (a) trim your question's code down to just the relevant bit, and (b) also post in a broader audience place like ServerFault.com or StackOverflow.com. That way, you might have. better chance of someone familiar with ServiceNow seeing your question.

  • #91028

    Deepansh
    Participant

    Hi Don,
    i tried to create a new script for this.
    now i am able to insert a new record but not able to figure out how to attach the file to the record that has been created.
    Any ideas ?
    this script is comparatively smaller. 🙂

    #Update working directory to the name of the folder holding your script
    $workingdir = "D:\SOAP" 
    #EXAMPLE: "C:\soap\"
    
    #Update Export Directory to the name of the folder holding the export folders / files
    $ExportDir = "D:\export_files" 
    #EXAMPLE: "C:\soap\export_files"
    
    $username='*'
    $Pwd='*'
    
    $base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f 'linc.user', '123')))
    $password = ConvertTo-SecureString $Pwd -AsPlainText -Force
    [System.Net.ServicePointManager]::DefaultConnectionLimit = 8
    $credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username,$password 
    
    $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
    $headers.Add('Authorization',('Basic {0}' -f $base64AuthInfo))
    $headers.Add('Accept','application/json')
    $headers.Add('Content-Type','application/json')
    
    # Specify endpoint uri
    $uri = "https://**.service-now.com/api/now/table/u_u_fax_test"
    
    # Specify HTTP method
    $method = "post"
    $user="**"
    $snow_web="True"
    $Work_log = "Requested Support Channel: `r`n"
    if($snow_web -eq "True"){$Work_log += "Servinow Portal"}
    
    $body = @{   #Create body of the POST request
    u_string_1 = "attach"
    }
    $bodyJson = $body | ConvertTo-Json
    
    # Send HTTP request
    $response = Invoke-RestMethod -Headers $headers -Method $method -Uri $uri -Body $bodyJson -ContentType  "application/json" -Credential $credentials  
    #$response.result.sys_id#get sysid
    
    $response = Invoke-RestMethod -Headers $headers -Method get -Uri $uri -Credential $credentials -ContentType "application/json"

You must be logged in to reply to this topic.