SQL Reports Transformation via SCCM Script into Array Formats
In SCCM, you can generate a report that displays all software updates that are required but not deployed via WSUS. This report can be automatically run and its results output to a .CSV file or an array for further processing. Here's a step-by-step guide on how to achieve this:
Running the SQL Query
- Run a SQL Query against the SCCM database that identifies software updates required by clients but not yet deployed. This query involves joining views like , , to find updates required but with no active deployments.
While a pre-written query for this specific use case was not found in the search results, the query can be crafted based on standard SCCM reporting scenarios. Here's an example outline of the SQL query:
- This query hypothetically checks for required updates with no deployment. Actual view and column names can vary, so they should be verified against your SCCM version schema.
Automating the Export with PowerShell
To automate the export of the query results, you can use PowerShell. Here's an example script that connects to the SCCM SQL database, executes the query, and exports the results to a CSV file:
```powershell
$sqlServer = "YourSCCMSQLServer" $sqlDB = "CM_ABC" # Your SCCM site database $query = @" SELECT sys.Name0 AS ComputerName, upd.ArticleID, upd.Title FROM v_UpdateComplianceStatus ucs INNER JOIN v_R_System sys ON ucs.ResourceID = sys.ResourceID INNER JOIN v_UpdateInfo upd ON ucs.CI_ID = upd.CI_ID LEFT JOIN v_DeploymentSummary dt ON upd.CI_ID = dt.CI_ID WHERE ucs.Status = 'Required' AND dt.DeploymentID IS NULL "@
$connectionString = "Server=$sqlServer;Database=$sqlDB;Integrated Security=True;" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $connection.Open()
$command = $connection.CreateCommand() $command.CommandText = $query $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command $dataset = New-Object System.Data.DataSet $adapter.Fill($dataset)
$connection.Close()
$dataset.Tables[0] | Export-Csv -Path "C:\Reports\UpdatesRequiredNotDeployed.csv" -NoTypeInformation ```
- Replace and with your SCCM SQL Server name and database name respectively.
Summary
- To run the Software Updates - Updates Required but Not Deployed report in SCCM, use SQL views such as , , and to build a query that identifies updates required but not deployed.
- Automate the SQL execution and export using PowerShell, either exporting to CSV or capturing results in a PowerShell array.
- This approach requires adaptation based on your SCCM environment version and exact schema, but the method is standard for SCCM reporting automation.
The log file generated by the function is fully compatible with CMTrace.exe, and the report does not deploy software updates; it only shows the updates that are required but not deployed. The results from a standard SQL report can be transformed into an array as , and the script enables the conversion of a SCCM SQL Report into a .CSV file or an array for pipeline use. However, the script does not provide details about the software updates themselves, only whether they are required but not deployed. The report does not specify the type or number of software updates it displays.
Technology and data-and-cloud-computing are utilized in this guide to automate the generation and export of a report in SCCM. The PowerShell script leverages technology by connecting to the SCCM SQL database, executing an SQL query (data-and-cloud-computing), and exporting the results to a CSV file (technology). The query is crafted based on standard SCCM reporting scenarios (data-and-cloud-computing) for identifying software updates required but not yet deployed (data-and-cloud-computing).
