Need help coding powershell in relation to SCSM

Question

Good day! Please tell me how to use powershell to read information from the Log Entry at the Incident in SCSM (analyst comments, time / date)?

Answer 1

Hi @Konstantin ,

please take a look here if it helps with your requirement:
https://github.com/abaumgarten42/SCSM_Useful_PSscripts/blob/main/Get-IncidentComments.ps1

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 2

Hi, You don't mention your level of Powershell experience or if you've tried anything already but you could take a look into SMlets. Its a set of PowerShell cmdlets for SCSM which might have some options for reading back. I've used them to log calls, attached files etc so might have what you need. Couple year old now so I'm not sure if there are any newer options available or native PowerShell modules.

https://www.powershellgallery.com/packages/SMLets/1.0.2016.0

Answer 3

Hi @Konstantin ,

first: As your first question is marked as answer it's better to open a new question. I just found your additional question by "accident" ;-)

Here we go:

There are 4 tables involved:
MT_System$WorkItem$Incident -> Contains the Incident details
RelationshipType -> contains the Relationship Types (we need the System.WorkItem.TroubleTicketHasAnalystComment relationship )
Relationship -> contains the individual Incident ID (SourceID) and the individual Analyst Comment ID (TargetID)
System.WorkItem.TroubleTicketHasAnalystComment -> contains the individual Analyst Comment

Single queries:

/* Get Incident by ID  
*/  
SELECT Id_9A505725_E2F2_447F_271B_9B9F4F0D190C,BaseManagedEntityId FROM MT_System$WorkItem$Incident  
  WHERE Id_9A505725_E2F2_447F_271B_9B9F4F0D190C like 'IR2223'  
  
/* Get RelationshipType by name  
*/  
SELECT RelationshipTypeId, RelationshipTypeName FROM RelationshipType  
WHERE RelationshipTypeName like 'System.WorkItem.TroubleTicketHasAnalystComment'  
  
/* Get Related comments  by SourceEntityId (BaseManagedEntityId if Incident) and Relationship "System.WorkItem.TroubleTicketHasAnalystComment  
*/  
SELECT SourceEntityId, TargetEntityId FROM Relationship  
WHERE RelationshipTypeId like '835A64CD-7D41-10EB-E5E4-365EA2EFC2EA' AND  
SourceEntityId like '012FA8A9-E3C3-B0EF-3B56-879FA13A9B81'  
  
SELECT * FROM MT_System$WorkItem$TroubleTicket$AnalystCommentLog  
WHERE BaseManagedEntityId like '3E3E0615-4E43-6B34-A6D7-60D5693DB819'  

One query:

SELECT Comment_AECE7B96_331B_498C_2F77_AEA4376EFFF1 FROM MT_System$WorkItem$TroubleTicket$AnalystCommentLog  
WHERE BaseManagedEntityId in (SELECT TargetEntityId FROM Relationship  
WHERE RelationshipTypeId in (SELECT RelationshipTypeId FROM RelationshipType  
WHERE RelationshipTypeName like 'System.WorkItem.TroubleTicketHasAnalystComment') AND  
SourceEntityId in (SELECT BaseManagedEntityId  FROM MT_System$WorkItem$Incident  
WHERE Id_9A505725_E2F2_447F_271B_9B9F4F0D190C like 'IR2223'))  

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten