How to sort Query Database object per server?

SaiTech Sweden 81 Reputation points
2021-03-09T16:46:24.037+00:00

Hi,
How to sort Query Database object per server?

My DB query gets this string, now i sort the per server-name to a textfile and read them to bundel all data per server. I wouold like to bundel data from SRV001 to one stream and SRV002 to another. At the end they will be an SCSM SR per server with the data. Is there a way?

SRV001;2021-02 Cumulative Update for Windows Server 2016 for x64-based Systems (KB4601318);Required;Server - Updates - Current Month;4601318
SRV001;Microsoft Edge-Stable Channel Version 88 Update for x64 based Editions (Build 88.0.705.63);Required;Server - Updates - Current Month;

SRV002;2021-02 Cumulative Update for Windows Server 2016 for x64-based Systems (KB4601318);Required;Server - Updates - Current Month;4601318
SRV002;Microsoft Edge-Stable Channel Version 88 Update for x64 based Editions (Build 88.0.705.63);Required;Server - Updates - Current Month;

I user the Query Database IP,

System Center Orchestrator
System Center Orchestrator
A family of System Center products that provide an automation platform for orchestrating and integrating both Microsoft and non-Microsoft IT tools.
219 questions
0 comments No comments
{count} votes

Accepted answer
  1. Stefan Horz 3,466 Reputation points
    2021-03-09T20:57:49.677+00:00

    Hi

    one option to use your Runbook up to "Query Database" Activity

    Flatten your "Query Database" Activity using line breaks.

    Use the PowerShell Code below in a "Run .Net Script" Activity. Instead of the text from your example subscripe the result your "Query Database" Activity.

    $QueryResult = @'
    SRV001;2021-02 Cumulative Update for Windows Server 2016 for x64-based Systems (KB4601318);Required;Server - Updates - Current Month;4601318
    SRV001;Microsoft Edge-Stable Channel Version 88 Update for x64 based Editions (Build 88.0.705.63);Required;Server - Updates - Current Month;
    
    SRV002;2021-02 Cumulative Update for Windows Server 2016 for x64-based Systems (KB4601318);Required;Server - Updates - Current Month;4601318
    SRV002;Microsoft Edge-Stable Channel Version 88 Update for x64 based Editions (Build 88.0.705.63);Required;Server - Updates - Current Month;
    '@
    $ResultInArray = $QueryResult.Split([Environment]::NewLine) | where {$_ -ne ''}
    
    $ServerNames = @()
    foreach ($item in $ResultInArray) {
        $ServerNames += ($item.Split(';'))[0]
    }
    
    $ServerNames  = $ServerNames | select -Unique
    
    $Server4Ticket = @()
    $Text4Ticket = @()
    
    foreach ($ServerName in $ServerNames) {
        $Server4Ticket += $ServerName
        $Text4Server = ''
        foreach ($line in $ResultInArray) {
            if (($line.Split(';'))[0] -eq $ServerName) {
                $Text4Server = $Text4Server + ($line.Split(';'))[1] + [Environment]::NewLine
            }
        }
        $Text4Ticket += $Text4Server
    }
    

    Publish Server4Ticket and Text4Ticket in the "Run .Net Script" Activity.

    Regards,
    Stefan

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. SaiTech Sweden 81 Reputation points
    2021-03-11T17:05:05.547+00:00

    Thank you Stefan, I will test and come back.

    0 comments No comments

  2. SaiTech Sweden 81 Reputation points
    2021-03-11T18:18:18.433+00:00

    Fantastico, your POSH code worked right away. I had to look at it for 10 minutes reading some variables. Then I saw the beauty. You're a king Stefan.

    All I need is line breaks, and some formatting then I'm home. As a quick test i sent it as mail to me, I got two mails srv001 and srv002.

    76875-image.png

    0 comments No comments