Does anyone have any examples of using powershell ( either via native scsm cmdlets, Smlets or scsmpx) to export a workitem or woriktems to csv but also in clude comments and related activities?

Hi Misha,

I guess no one but it's not hard to build. You have only to realize what data you actually need.

I would like to get Affected user, assigned to user, title description, Notes on SR, created date, and on the activities Title description notes, assigned to user,.  I think I should be able to figure out the fields I just don't know how to display connected Activities.  It would also be nice If MS had some mechanism of selecting a range of work-items and exporting them into an importable format.

As Marat said then it's probably hard to come by a full example, but this might get you started. I prefer using type projections in this case, but you could also just enumerate the related objects on the work item.

Example:

#Get type projection to use
#NB! there are many different projections, but there doesn't seem to be one out of the box for service requests with both action logs and activities you will need to make your own projection to get exactly the components you want.

#Type projection with actionlogs, related users and  related work items (no activities)
$projType = Get-SCSMTypeProjection -Name "System.WorkItem.ServiceRequestProjection"

#Type projection with above + Activities (requires cireson view builder or make the projection yourself) 
$projType = Get-SCSMTypeProjection -Name "Cireson.ServiceRequest.ViewModel"

#Get projection object(s)
$projObject = Get-SCSMObjectProjection -Projection $projType -Filter "Id -eq SR1185"

#Show Action logs:
$projObject.AppliesToWorkItem

#Show Activities
$projObject.Activity

#Optionally export to XML to work with later (e.g. transfer to other system etc.)
$projObject | Export-Clixml -Path "C:\temp\SR1185.xml" #-depth 3 (default = 2)

• Edited by Morten Meisler 22 hours 3 minutes ago
• Proposed as answer by Andreas BaumgartenMVP, Moderator 9 hours 40 minutes ago

As Marat said then it's probably hard to come by a full example, but this might get you started. I prefer using type projections in this case, but you could also just enumerate the related objects on the work item.

Example:

#Get type projection to use
#NB! there are many different projections, but there doesn't seem to be one out of the box for service requests with both action logs and activities you will need to make your own projection to get exactly the components you want.

#Type projection with actionlogs, related users and  related work items (no activities)
$projType = Get-SCSMTypeProjection -Name "System.WorkItem.ServiceRequestProjection"

#Type projection with above + Activities (requires cireson view builder or make the projection yourself) 
$projType = Get-SCSMTypeProjection -Name "Cireson.ServiceRequest.ViewModel"

#Get projection object(s)
$projObject = Get-SCSMObjectProjection -Projection $projType -Filter "Id -eq SR1185"

#Show Action logs:
$projObject.AppliesToWorkItem

#Show Activities
$projObject.Activity

#Optionally export to XML to work with later (e.g. transfer to other system etc.)
$projObject | Export-Clixml -Path "C:\temp\SR1185.xml" #-depth 3 (default = 2)

• Edited by Morten Meisler Sunday, September 13, 2015 9:08 AM
• Proposed as answer by Andreas BaumgartenMVP, Moderator Sunday, September 13, 2015 9:31 PM
• Marked as answer by Misha Rudiy 9 hours 54 minutes ago

I forgot to mention that one thing to be aware of with the above approach, is that if you have complex Service Requests - that is if you have activities inside activities (parallel, sequential), then you have to enumerate the related objects (unless you know the exact depth of the service requests, then you could probably construct a type project for it).

Example 2:

#Get relationship class
$relContainsActivity = Get-SCSMRelationshipClass -Name System.WorkItemContainsActivity

#Get work item from Id
$WI = Get-SCSMObject -Id $Id 
        
#Get all activities from Work Item
$Activities = Get-SCSMRelatedObject -SMObject $WI -Relationship $relContainsActivity -Depth Recursive

I forgot to mention that one thing to be aware of with the above approach, is that if you have complex Service Requests - that is if you have activities inside activities (parallel, sequential), then you have to enumerate the related objects (unless you know the exact depth of the service requests, then you could probably construct a type project for it).

Example 2:

#Get relationship class
$relContainsActivity = Get-SCSMRelationshipClass -Name System.WorkItemContainsActivity

#Get work item from Id
$WI = Get-SCSMObject -Id $Id 
        
#Get all activities from Work Item
$Activities = Get-SCSMRelatedObject -SMObject $WI -Relationship $relContainsActivity -Depth Recursive

• Marked as answer by Misha Rudiy 9 hours 54 minutes ago

Hello Morten,

Thanks for your post.  This will be a good thing to have in my back pocket.  When I asked this same question to the MS support agent who was working on our support incident, his response was "unfortunately some data loss will occur".