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
- CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], W
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"
