question

PerSai-6948 avatar image
0 Votes"
PerSai-6948 asked ·

How to sort Query Database object per server?

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,

msc-orchestrator-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

StefanHorz avatar image
0 Votes"
StefanHorz answered ·

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


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PerSai-6948 avatar image
0 Votes"
PerSai-6948 answered ·

Thank you Stefan, I will test and come back.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PerSai-6948 avatar image
0 Votes"
PerSai-6948 answered ·

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



image.png (21.5 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.