We all know that Data Warehouse server in Service Manager(SCSM) does not store any attachments.However, i recently worked on an interesting client request in which client wanted to Extract the attachments from all the closed incident work items and save it on a shared drive and then add a new column in the custom report which will have the link to the file path where the incident attachments are stored. This way the attachments can be viewed for the related incident if needed by just going to the shared path.
This was achieved by completing the steps below:
1. Extend the Incident Class and add a new String Field.
2. Create a shared folder path to store attachments
3. Create a PowerShell script which would do the following:
3.1. Filter out all the closed incidents in SCSM
3.2. Look for closed incidents with attachments and extract them to the folder
3.3. Write the folder path of the share drive where the incidents are stored in the new String Field created in Step 1 and if it cant find the attachments in the incident, write a message that no attachments are found.
3.4. Compare the objects in the Folder and the Incident Work Item so that they are same and no attachment is missing.
3.5. If there is a mismatch, send an email to HelpDesk group to take a look at the issue.
So, lets begin with the Steps in more detail:
1. I started this by extending the incident class using SCSM Authoring Tool and added a new string field called “AttachmentArchivePath”. This field will be used to store the links to the folder path for the attachments. After this, i sealed the MP and ran the Data warehouse jobs so that there is a new column in the Incidentdim on DWDataMart database with the same property name. This can be later used for the reporting purposes. I will not be explaining on how to extend a class but there is a GREAT video by Travis Wright which explains this. The link to the video is here.
2. Create a Shared folder path. For the purposes of this article and a demo , I created a folder called Archive on the local C: Drive of the SCSM server. You can create different folders for different work items(Change,SR etc.)
3. Below is the powershell script. A big thanks to Patrick from Litware for sharing a powershell script since i used some parts of his script which also extracts the Attachments. You can view that link here.
Import-Module 'C:\Program Files\Common Files\SMLets\SMLets.psd1'
$archiveDir = 'C:\Archive'
$archivePropertyName = 'AttachmentArchivePath'
$Date = (Get-Date).Adddays(-1)
$CId = (Get-SCSMEnumeration -ComputerName $Computername -Credential $cred IncidentStatusEnum.Closed$).id
$Class = Get-SCSMClass -Name “System.WorkItem.Incident$” -ComputerName $Computername -Credential $cred
$cType = "Microsoft.EnterpriseManagement.Common.EnterpriseManagementObjectCriteria"
$cString = "Status = '$CId'"
$work_item_has_attachment_rel_obj = Get-SCSMRelationshipClass -name 'System.WorkItemHasFileAttachment'
$FilesOnIncident = 0
$FilesInFolder = 0
param ( $attachmentObjects, $archiveLocation )
$seqnum = 0
$attachmentExportPaths = @()
$FilesOnIncident = 0
$FilesInFolder = 0
foreach ($attachment in $attachmentObjects)
if ($attachmentExportPaths -contains $archiveLocation + $attachment.DisplayName)
$fs = [IO.File]::OpenWrite(($archiveLocation + $attachment.DisplayName.Replace($attachment.Extension, '_' + $seqnum + $attachment.Extension)))
$fs = [IO.File]::OpenWrite(($archiveLocation + $attachment.DisplayName))
$memoryStream = New-Object IO.MemoryStream
$buffer = New-Object byte 8192
while (($bytesRead = $attachment.Content.Read($buffer, 0, $buffer.Length)) -gt 0)
$memoryStream.Write($buffer, 0, $bytesRead)
$attachmentExportPaths += $fs.name
$FilesOnIncident = $attachmentobjects.count
set-location -Path $archiveLocation
$FilesInFolder = Get-ChildItem -Recurse -force | Measure-Object
$errorMessage = "error"
#get all Closed IRs
$allclosedIRs = Get-SCSMObject $class -Filter $cString
foreach ($wi in $allclosedIRs)
$attachmentCheck = Get-SCSMRelatedObject -SMObject $wi -Relationship $work_item_has_attachment_rel_obj
if ( $attachmentCheck -ne $null )
#create a var to hold the archive folder name for this specific work item
$wiArchiveDir = $archiveDir + '\' + $wi.id + '\'
#create an individual folder for each work item
if (!(Test-Path -PathType Container -Path $wiArchiveDir))
New-Item -ItemType Directory -Force -Path $wiArchiveDir
#dump attachments to the new dir
$errorCatch = write-out-attachments -attachmentObjects $attachmentCheck -archiveLocation $wiArchiveDir
#Compare the Attachment count in Incident with Files in Folder
if ($FilesOnIncident -eq $FilesInFolder)
$errorIncidentId += $wi.id
if ($errorCatch -ne 'error')
Set-SCSMObject -SMObject $wi -Property $archivePropertyName -Value $wiArchiveDir
#write that there's no attachments for the work item
$WIid = $wi.Id
Set-SCSMObject -SMObject $wi -Property $archivePropertyName -Value "No attachments in $WIid"
4. The above PowerShell script will give you the attachments from all the closed Incident work items but you can easily change this to other Work item statuses as well(Active,Resolved and soon). You can also change the work item type as well and do the same for Change and Service Requests as well.
5. You can also add this code to a runbook so that it runs on a schedule and picks up the attachments based on your needs automatically using Orchestrator and send an email to HelpDesk if the count of the attachments don’t match. I have included the comparison of the count in the script already.
6. Once you run this code, you will see that the ‘AttachementAutoArchive’ property we created earlier should have the Folder path of the attachment if that incident workitem contains an attachment otherwise it will write “No Attachments in IRXX”. This will also populate the Incident Table in the DWDataMart database on the Datawarehouse SQL server. Screenshots are below.
7. Once the table has been populated, a custom report can be created from the SSRS using report builder and making changes in the List of Incidents SQL Store Procedure to create a new one with the custom property. I wont go into the details for creating the custom report since there is a great article already available from the team of Authoring Friday. The link is here