Problemstellung: Daten sollen mittels “Invoke-SqlCmd” aus einer SQL-Server Datenbanke geholt werden. Sofern das “SqlServer”-Modul für die Powershell installiert ist, kann das auf bequeme Weise mit dem Commandlet “Invoke-SqlCmd” erfolgen.
Beispielsweise so:
$result = Invoke-SqlCmd -ServerInstance MeinDbServer -Database msdb -Query @"
SELECT
H.*
FROM
msdb.dbo.sysjobs AS J
CROSS APPLY (
SELECT TOP 1
JobName = J.name,
StepNumber = T.step_id,
StepName = T.step_name,
StepStatus = CASE T.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'Running' END,
ExecutedAt = msdb.dbo.agent_datetime(T.run_date, T.run_time),
ExecutingHours = ((T.run_duration/10000 * 3600 + (T.run_duration/100) % 100 * 60 + T.run_duration % 100 + 31 ) / 60) / 60,
ExecutingMinutes = ((T.run_duration/10000 * 3600 + (T.run_duration/100) % 100 * 60 + T.run_duration % 100 + 31 ) / 60) % 60,
Message = T.message
FROM
msdb.dbo.sysjobhistory AS T
WHERE
T.job_id = J.job_id
ORDER BY
T.instance_id DESC) AS H
ORDER BY
J.name
"@Wie aus dem Code-Block ersichtlich, werden die Ergebnisdaten in der Variable “$result” gespeichert. Wenn man sich die Variable nun ausgeben lässt, dann könnte man davon ausgehen, dass tatsächlich nur die abgefragten Inhaltsdaten in die Variable geschrieben wurden. Denn das Ergebnis sieht dann etwas so aus:
Powershell> $result JobName : DatabaseBackup - DBName_DATABASE - FULL - NUL StepNumber : 0 StepName : (Auftragsergebnis) StepStatus : Succeeded ExecutedAt : 28.07.2022 20:30:00 ExecutingHours : 0 ExecutingMinutes : 21 Message : Der Auftrag war erfolgreich. JobName : DatabaseBackup - DBName_DATABASE - LOG - NUL StepNumber : 0 StepName : (Auftragsergebnis) StepStatus : Succeeded ExecutedAt : 29.07.2022 14:39:00 ExecutingHours : 0 ExecutingMinutes : 0 Message : Der Auftrag war erfolgreich.
Tatsächlich handelt es sich jedoch bei der Variable “$result” in diesem Fall um eine ItemArray, welches mit DataRow-Objekten befüllt ist.
Das kann unter Umständen problematisch sein, wenn man die ermittelten Daten zur Weiterverarbeitung an eine Schnittstelle geben möchte. Denn eine DataRow enthält weitere MetaInformationen, die meines Erachtens beim Serialisieren in ein anderes Format (bspw. JSON) folgende Nachteile mitbringt:
- unschön anzuschauen
- bläht die Datei mit unnötigen Informationen
- beim Deserialisieren auf der anderen Seite (Schnittstelle) werden unnötige Felder benötigt.
Damit ihr versteht was ich meine, hier ein Auszug nach dem Serialisieren in das .json-Format:
$result | ConvertTo-Json
[
{
"RowError": "",
"RowState": 1,
"Table": {
"Rows": "",
"CaseSensitive": false,
"IsInitialized": true,
"RemotingFormat": 0,
"ChildRelations": "",
"Columns": "JobName StepNumber StepName StepStatus ExecutedAt ExecutingHours ExecutingMinutes Message",
"Constraints": "",
"DataSet": null,
"DefaultView": "",
"DisplayExpression": "",
"ExtendedProperties": "System.Data.PropertyCollection",
"HasErrors": false,
"Locale": "de-DE",
"MinimumCapacity": 50,
"ParentRelations": "",
"PrimaryKey": "",
"TableName": "",
"Namespace": "",
"Prefix": "",
"Site": null,
"Container": null,
"DesignMode": false
},
"ItemArray": [
"syspolicy_purge_history",
0,
"(Auftragsergebnis)",
"Succeeded",
"\/Date(1659052800000)\/",
0,
0,
"Der Auftrag war erfolgreich. Der Auftrag wurde von Zeitplan 8 (syspolicy_purge_history_schedule) aufgerufen. Zuletzt wurde Schritt 3 (Erase Phantom System Health Records.) ausgeführt."
],
"HasErrors": false,
"JobName": "syspolicy_purge_history",
"StepNumber": 0,
"StepName": "(Auftragsergebnis)",
"StepStatus": "Succeeded",
"ExecutedAt": "\/Date(1659052800000)\/",
"ExecutingHours": 0,
"ExecutingMinutes": 0,
"Message": "Der Auftrag war erfolgreich. Der Auftrag wurde von Zeitplan 8 (syspolicy_purge_history_schedule) aufgerufen. Zuletzt wurde Schritt 3 (Erase Phantom System Health Records.) ausgeführt."
}
]Um beim Serialisieren, die ganzen unnötigen Information weglassen zu können, bietet es sich an, ein neues Array zu erstellen, welches dann die bereinigten Objekte enthält. Mein Ansatz hierfür sieht wie folgt aus:
function DataRows2PsCustomObjects
{
[CmdletBinding()]
param(
[Parameter(ValueFromPipeline,ValueFromPipelineByPropertyName)]
$Items
)
begin {
$objects = @();
}
process {
$Items | % {
$obj = $_;
$obj
$customObj = [PsCustomObject]@{};
$_.Table.Columns | % {
$customObj | Add-Member -MemberType NoteProperty -Name $_.ColumnName -Value $obj.($_.ColumnName);
}
$objects += $customObj;
}
}
end {
return $objects;
}
}
# Beispiel-Code für die Verwendung der Funtkion:
$result | DataRows2PsCustomObjectsDie Ausgabe kommt und bekannt vor:
JobName : syspolicy_purge_history StepNumber : 0 StepName : (Auftragsergebnis) StepStatus : Succeeded ExecutedAt : 29.07.2022 02:00:00 ExecutingHours : 0 ExecutingMinutes : 0 Message : Der Auftrag war erfolgreich. JobName : syspolicy_purge_history StepNumber : 0 StepName : (Auftragsergebnis) StepStatus : Succeeded ExecutedAt : 29.07.2022 02:00:00 ExecutingHours : 0 ExecutingMinutes : 0 Message : Der Auftrag war erfolgreich.
Wenn wir das, durch Funktion erzeugte Array nun serialisieren, dann erhalten wir ein wirklich schöne Datei, die ausschließlich die tatsächlich abgefragten Informationen erhält.
$result | DataRows2PsCustomObjects | ConvertTo-Json
[
{
"JobName": "SQLWATCH-REPORT-AZMONITOR",
"StepName": "(Auftragsergebnis)",
"StepStatus": "Failed",
"ExecutedAt": "\/Date(1659098421000)\/",
"ExecutingHours": 0,
"ExecutingMinutes": 0,
"Message": "Auftragsfehler Der Auftrag wurde von Zeitplan 1053 (SQLWATCH-REPORT-AZMONITOR) aufgerufen. Zuletzt wurde Schritt 1 (dbo.usp_sqlwatch_internal_process_reports) ausgeführt."
},
{
"JobName": "syspolicy_purge_history",
"StepNumber": 0,
"StepName": "(Auftragsergebnis)",
"StepStatus": "Succeeded",
"ExecutedAt": "\/Date(1659052800000)\/",
"ExecutingHours": 0,
"ExecutingMinutes": 0,
"Message": "Der Auftrag war erfolgreich. Der Auftrag wurde von Zeitplan 8 (syspolicy_purge_history_schedule) aufgerufen. Zuletzt wurde Schritt 3 (Erase Phantom System Health Records.) ausgeführt."
}
]Viel Spaß damit!