MA Comment and Clompleted by User

Sarah_Geihs

Hi everybody,

can anyone tell me how to get thes users who closed manual activities via SQL?

I need the following information:

• BaseManageEntityID from MA
• ClosedDate
• Closed by User
• Final Comment

Thanks for your help!

Sarah

Jeff_Lang

@Sarah_Geihs here is a query that will get the actual user that updated the MA to completed, no matter who it was actually assigned to. NOTE: it is slow, could probably be done a better way/Improved and if you look at the Where statement at the bottom is currently set to get just the last 8 days, and gets the history item where the status is changed from something else to completed. This is a modified query from another one we actually use for something else, and is set to not lock files to prevent holding up other queries

GetMAComplete.txt

Simon_Zeinhofer

@Sarah_Geihs with the cached tables, the actual query is pretty simple:

Select guid,
ActualEndDate,
System_WorkItemAssignedToUser_DisplayName,
Notes

from 
cachert.csnCached_MT_System_WorkItem_Activity_ManualActivity
where LanguageCode = 'ENU'

The thing is, when there is no activity implementer inside the MA, the System_WorkItemAssignedToUser_DisplayName will be NULL. The notes are a simple text field, so it will be difficult to say what's the last comment.

But maybe this still helps :)

Sarah_Geihs

Hey Simon,

thanks for your answer.

Our Problem is that some users dont assign the tickets to themselves so the AssignedUser is their team. We hope that we will find the actual processor via the user who closes the ticket.

I know that there is a relation to ClosedByUser but for our SRs it is always the technical user which closes the SR if all Activites are completed. Thats the reason why we need the person who closes the Activities.

Do you have another idea without AssignedUser? :-)

Simon_Zeinhofer

@Sarah_Geihs we have the same problem - we have a custom Enumeration for a support group, so we know at least which team is responsible for it - but the MAs itself are very rarely assigned.

It would help if we could e.g. set the Activity Implementer to a required field. Maybe this is something Cireson will provide in the future?

Jeff_Lang

@Sarah_Geihs here is a query that will get the actual user that updated the MA to completed, no matter who it was actually assigned to. NOTE: it is slow, could probably be done a better way/Improved and if you look at the Where statement at the bottom is currently set to get just the last 8 days, and gets the history item where the status is changed from something else to completed. This is a modified query from another one we actually use for something else, and is set to not lock files to prevent holding up other queries

GetMAComplete.txt

Sarah_Geihs

@Jeff_Lang Thanks. That works. :-)