Lookin for a SQL/Powershell command

Adam_Stoppa

Hi,

i am looking for a sql or powershell command to show me all comments from an Incident/Service Request from the SCSM database. Does anyone has an idea ?

Adam_Dzyacky

The background of my request is that our Management is asking me to build a kind of a long term storage/history of all SRs and IRs with all comments included. And than save them in a database or data warehouse or a SQL-View.

You'll be happy to know that per our roadmap, Cireson Analytics will be extended to automatically bring in the Action Log. Which means you'll have long term storage of all of it via SQL soon. 😁

But for the here and now of PowerShell - yes. You can absolute whittle the results down further if you'd like. Take the Incident example from above:

If you wanted just the comment it's as simple as:

$irClass = Get-SCSMClass -name "System.WorkItem.Incident$"
$incident = Get-SCSMObject -class $irClass -filter "Name -eq 'IR36'"
Get-SCSMRelatedObject -smobject $incident | Where-Object {$_.ClassName -like "*TroubleTicket*"} | Select Comment

If you wanted to ignore Action Log entries and only grab comments:

$irClass = Get-SCSMClass -name "System.WorkItem.Incident$"
$incident = Get-SCSMObject -class $irClass -filter "Name -eq 'IR36'"
Get-SCSMRelatedObject -smobject $incident | Where-Object {$_.ClassName -like "*TroubleTicket*Comment*"} | Select ClassName, Comment

In either case though, it's possible PowerShell doesn't show the whole comment and instead wraps the line with a "..." per your screenshot. However, the full comment is in fact there! So if you wanted to export to CSV or Format-List you would see the full comment as you expect.

$irClass = Get-SCSMClass -name "System.WorkItem.Incident$"
$incident = Get-SCSMObject -class $irClass -filter "Name -eq 'IR36'"
Get-SCSMRelatedObject -smobject $incident | Where-Object {$_.ClassName -like "*TroubleTicket*Comment*"} | Select ClassName, Comment | Format-List

What's true in all of these cases, is you're getting the entire record of comments. So you could foreach through them if you needed quite easily.

$irClass = Get-SCSMClass -name "System.WorkItem.Incident$"
$incident = Get-SCSMObject -class $irClass -filter "Name -eq 'IR36'"
$allComments = Get-SCSMRelatedObject -smobject $incident | Where-Object {$_.ClassName -like "*TroubleTicket*Comment*"} | Select ClassName, Comment
foreach ($comment in $allComments)
{
    write-host $comment.Comment
}

Adam_Dzyacky

This will pull the entire Action Log, but you can certainly filter down further.

Incident:

$irClass = Get-SCSMClass -name "System.WorkItem.Incident$"
$incident = Get-SCSMObject -class $irClass -filter "Name -eq 'IR36'"
Get-SCSMRelatedObject -smobject $incident | Where-Object {$_.ClassName -like "*TroubleTicket*"} | Select ClassName, ActionType, Comment

Service Request:

$srClass = Get-SCSMClass -name "System.WorkItem.ServiceRequest$"
$sr = Get-SCSMObject -class $srClass -filter "Name -eq 'SR77'"
Get-SCSMRelatedObject -smobject $sr | Where-object {$_.ClassName -like "*TroubleTicket*"} | Select ClassName, ActionType, Comment

Adam_Stoppa

Hi Adam,

we are getting closer to it. Both commands help me, but don't show exactly what I want. I need the entire content of the Analyst or End Users comments.

When i execute the commands i get the attached result. With a SR the result is the same. Can i save only the comment in a variable?

The background of my request is that our Management is asking me to build a kind of a long term storage/history of all SRs and IRs with all comments included. And than save them in a database or data warehouse or a SQL-View.

Maybe you have a better idea to solve this problem ?

irComment_original.PNG

irComment.PNG

Adam_Dzyacky

The background of my request is that our Management is asking me to build a kind of a long term storage/history of all SRs and IRs with all comments included. And than save them in a database or data warehouse or a SQL-View.

You'll be happy to know that per our roadmap, Cireson Analytics will be extended to automatically bring in the Action Log. Which means you'll have long term storage of all of it via SQL soon. 😁

But for the here and now of PowerShell - yes. You can absolute whittle the results down further if you'd like. Take the Incident example from above:

If you wanted just the comment it's as simple as:

$irClass = Get-SCSMClass -name "System.WorkItem.Incident$"
$incident = Get-SCSMObject -class $irClass -filter "Name -eq 'IR36'"
Get-SCSMRelatedObject -smobject $incident | Where-Object {$_.ClassName -like "*TroubleTicket*"} | Select Comment

If you wanted to ignore Action Log entries and only grab comments:

$irClass = Get-SCSMClass -name "System.WorkItem.Incident$"
$incident = Get-SCSMObject -class $irClass -filter "Name -eq 'IR36'"
Get-SCSMRelatedObject -smobject $incident | Where-Object {$_.ClassName -like "*TroubleTicket*Comment*"} | Select ClassName, Comment

In either case though, it's possible PowerShell doesn't show the whole comment and instead wraps the line with a "..." per your screenshot. However, the full comment is in fact there! So if you wanted to export to CSV or Format-List you would see the full comment as you expect.

$irClass = Get-SCSMClass -name "System.WorkItem.Incident$"
$incident = Get-SCSMObject -class $irClass -filter "Name -eq 'IR36'"
Get-SCSMRelatedObject -smobject $incident | Where-Object {$_.ClassName -like "*TroubleTicket*Comment*"} | Select ClassName, Comment | Format-List

What's true in all of these cases, is you're getting the entire record of comments. So you could foreach through them if you needed quite easily.

$irClass = Get-SCSMClass -name "System.WorkItem.Incident$"
$incident = Get-SCSMObject -class $irClass -filter "Name -eq 'IR36'"
$allComments = Get-SCSMRelatedObject -smobject $incident | Where-Object {$_.ClassName -like "*TroubleTicket*Comment*"} | Select ClassName, Comment
foreach ($comment in $allComments)
{
    write-host $comment.Comment
}

Adam_Stoppa

That's really awesome, you made my night! Exactly what i need, thanks.

Do you perhaps know a same SQL-Statement for this or a SQL-View ?

Adam_Stoppa

Is there also a possibility to save attachments on this way ?

Adam_Dzyacky

Is there also a possibility to save attachments on this way ?

Yes.