Home› Analyst Portal
Options

Lookin for a SQL/Powershell command

Adam_StoppaAdam_Stoppa Customer IT Monkey ✭
in Analyst Portal

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 ?

Best Answer

  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited August 2021 Answer ✓

    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
}


Answers

  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited August 2021

    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
  • Options
    Adam_StoppaAdam_Stoppa Customer IT Monkey ✭

    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


  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited August 2021 Answer ✓

    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
}


  • Options
    Adam_StoppaAdam_Stoppa Customer IT Monkey ✭

    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 ?

  • Options
    Adam_StoppaAdam_Stoppa Customer IT Monkey ✭

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

  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

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

    Yes.

Sign In or Register to comment.