none
Inserting array value into DB ( using "WriteToDB"activity in orchestrator)

    Question

  • TicketID                                                                                   AG                                                                                        
    --------                                                                                   --                                                                                        
    IM002491                                                                               TEST                                                                                      
    IM0024911                                                                              TEST 

    From a Runbook script , getting the above ouotput, need to add these entries into a DB(using functionalities within Orchestrator), can some please help on this?

    Thanks!

    Friday, March 17, 2017 2:16 PM

All replies

  • Hi,

    you can use the following PowerShel-Script in a "Run .Net Script" Activity:

    # subscribe your Data here
    $youritems = 
    @([pscustomobject]@{TicketID="IM0024911";AG="TEST"},
    [pscustomobject]@{TicketID="IM002491";AG="TEST"})
    
    #the script:
    $TicketID = @()
    $AG = @()
    
    FOREACH ($item in  $youritems)
    {
        $TicketID += $item.TicketID
        $AG += $item.AG
    }
    

    define the Published Data like this:

    and you'll get such a result:

    Regards,

    Stefan


    Visit go2azure.eu and my blog at www.sc-orchestrator.eu !

    Friday, March 17, 2017 9:48 PM
    Answerer
  • Thanks Stefan!


    Use case:

    Step1  Orchrestator would check the ticketing system's Rest URL for a tickets, TS (Ticketing system) would reply with Ticket ID and AG. Now the Orchestrator filters the respective AG's And  & (Step2)have to invoke the respective subworkflow to execute.

    This event status has to be captured in the DB along with the Ticket ID.And the filtered ticket(against AG has to updated in the DB, how this can be done when there is no definite output?

    Challenges: The step TILL 2 is performed by a powershell script, further would need to invoke respective sub workflows, how to invoke the subworkflow from a powershell script block?


    Appreciate your help!

    Monday, March 20, 2017 11:59 AM
  • I checked this out, it works when there is one entry to the DB in the case of multiple entries it fails not sure what am missing, please help!
    Monday, March 20, 2017 1:36 PM
  • You're welcome!

    Can you share the script and the result in or from a script block for further help?

    Regards,

    Stefan


    Visit go2azure.eu and my blog at www.sc-orchestrator.eu !

    Monday, March 20, 2017 9:00 PM
    Answerer
  • $URI = 'http://Server/Adapter/rest/tickets'
    $request_Rest=(Invoke-RestMethod $URI) |ConvertFrom-Json

    $TicketDetails= $request_Rest |select -expand ticketdtls | select TicketID, AG 
    $t = $TicketDetails | Where-Object{$_.AG -eq 'Test'} 
    $ID=$t.TicketID

    $TicketID=@()  
    $Description=@()

    foreach ($ID in $t)
    {
    $TicketURI= 'http://Server/Adapter/rest/tickets/'+($ID.TicketID)
    $request_TicketURI=(Invoke-RestMethod $TicketURI) |ConvertFrom-Json
    $TicketID += $ID.TicketID
    $Description+=($TicketIDDetails.BriefDescription)

    }

    From here, will have to make a pattern search fro the each entries in the $Description and invoke the respective Runbook. Secondly update the DB with the respective TicketID and status of the respective invoked runbook.

    Thanks,

    Tuesday, March 21, 2017 8:04 AM
  • Hi,

    not sure where you get "$TicketIDDetails" for $Description. Or where you use $request_TicketURI further in you script ....

    Can you provide sample output for $t ($t = $TicketDetails | Where-Object{$_.AG -eq 'Test'} ) and $request_TicketURI ($request_TicketURI=(Invoke-RestMethod $TicketURI) |ConvertFrom-Json)? You are interested in  $TicketID and $Description, right? Any other?

    Without knowing this exactly, I guess you need something like:

    $URI = 'http://Server/Adapter/rest/tickets'
    $request_Rest=(Invoke-RestMethod $URI) |ConvertFrom-Json
    
    $TicketDetails= $request_Rest | select -expand ticketdtls | select TicketID, AG 
    
    $t = $TicketDetails | Where-Object{$_.AG -eq 'Test'} 
    
    
    $TicketID=@()  
    $Description=@()
    
    foreach ($ID in $t)
    {
        $TicketURI= 'http://Server/Adapter/rest/tickets/'+($ID.TicketID)
        $request_TicketURI = (Invoke-RestMethod $TicketURI) |ConvertFrom-Json
        $TicketID += $ID.TicketID
        $Description+= $request_TicketURI.BriefDescription
    
    }

    Regards,

    Stefan


    Visit go2azure.eu and my blog at www.sc-orchestrator.eu !

    Tuesday, March 21, 2017 7:36 PM
    Answerer
  • PS C:\TEMP> $t

    TicketID                                                                                   AG                                                                                        
    --------                                                                                   --                                                                                        
    IM0024267491                                                                               TEST                                                                                      
    IM00242674911                                                                              TEST                                                                                      
    =============

    PS C:\TEMP> $request_TicketURI


    Impact                    : 3
    Urgency                   : 3
    PrimaryAssignmentGroup    : TEST
    AssigneeName              : TEst_assignee
    BriefDescription          : EUMI_FI PO number missing for FI supplier Team
    IncidentDescription       : UserId: DXXXXXSO
    Service                   : MESSAGING/ELECTRONIC DATA INTERCHANGE (EDI) MANAGEMENT
    ClosureCode               : 
    NextBreach                : 2016-12-14T09:52:03
    SLABreachCode             : 
    UserEnvironment           : UAT
    Category                  : incident
    Area                      : data issue
    Subarea                   : incorrect data/content
    AffectedBusOrgsIDList     : {Domain/90001002/90002002/---/91030176/90034826/61027860}
    KeyContactsList           : {}
    KeyContactDetails         : 
    ExternalReference         : null
    Attachments               : 
    NextUpdateTime            : 1753-01-01T00:00:00
    EstResolutionTime         : 1753-01-01T00:00:00
    CollaborationAGList       : {}
    CollaborationAssigneeList : {}
    ActivityType              : Analysis/Update
    ActivityDescription       : owning for support
    ActivityOperator          : XXX.XXXX@domain.com
    ActivityDateStamp         : 
    DateStamp                 : 2016-12-12T09:54:05
    SourceSystemName          : XXX
    TargetSystemName          : XXXX
    TransactionType           : update
    TransactionID             : 0000000077651958
    TicketID                  : IM00242674911
    MessageType               : Ticket
    Resolution                : What was the (root) cause of this issue?

    Also the value $TicketID=@()  seems considered to be single string not as an line by line entity hence unable to insert to the DB.


    • Edited by ksrkut Monday, March 27, 2017 11:43 AM
    Wednesday, March 22, 2017 9:11 AM
  • Hi,

    you should get a multi-trigger for each  TicketID  with  $TicketID=@()  and $TicketID += $ID.TicketID within the FOREACH if you TicketID is published in the "Run .Net Script" Activity.

    Have you tried the script from my last response? Looking at the results you provided I guess it should work ...

    Regards,

    Stefan


    Visit go2azure.eu and my blog at www.sc-orchestrator.eu !

    Wednesday, March 22, 2017 8:37 PM
    Answerer
  • Modified the scripts into three Run activity

    Below codes in 1st Run Activity

    $URI = 'http://Server/Adapter/rest/tickets'
    $request_Rest=(Invoke-RestMethod $URI) |ConvertFrom-Json

    $TicketDetails= $request_Rest |select -expand ticketdtls | select TicketID, AG 
    $t = $TicketDetails | Where-Object{$_.AG -eq 'Test'} 
    $ID=$t.TicketID


    $out=@();
    foreach($TicketIDs in $ID)
    {
    $out+=$TicketIDs
    }

    2nd Run Acitivity

    $ID="<Called Variable $out>"

    $TicketURI= 'http://Server/Adapter/rest/tickets/'+($ID)
    $request_TicketURI=(Invoke-RestMethod $TicketURI) |ConvertFrom-Json
    $TicketIDDetails= $request_TicketURI |select TicketID,BriefDescription 
    $Desc=$TicketIDDetails.BriefDescription
    $TicketID = $ID.TicketID

    ======

    From here on made invoke to each child runbooks, upon successful execution another lead 3rd RunActivity to POST  Rest Method

    <# The below codes are not validated yet>

    Invoke-WebRequest -URI 'http://Server/Adapter/rest/tickets/(ticket_ID from 1stRunactivity $ID)
    $params

    $params = @{"Ticket_ID"="ticket_ID from 1stRunactivity $ID";
     "Status"="Processed";
    }

    Thanks,



    • Edited by ksrkut Monday, March 27, 2017 11:41 AM
    Thursday, March 23, 2017 8:01 AM