How to sort Query Database object per server?

Question

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,

Answer 1

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

Answer 2

Thank you Stefan, I will test and come back.

Answer 3

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.