Author Posts

December 28, 2017 at 11:38 am

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"

December 28, 2017 at 3:45 pm

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.

January 8, 2018 at 7:41 am

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"