This post will find the PowerShell script to Import CSV to Pivot Table SCCM Patch Report. As SCCM Patch management administrators, we must deliver good patch compliance.
This activity is also important for creating reports to track our overall patch compliance. As part of this activity, I manually created a weekly SCCM patch report to ensure patch compliance in our environment.
We use a security tool to get the raw data for a total number of scanned clients and noncompliant clients. I must import this data from multiple regions into different Excel files, create a pivot table, calculate the compliance percentage, and send the report.
After a couple of months, I became bored with doing the same thing and started researching whether I could automate this regular activity on the Internet.
| Index |
|---|
| PowerShell Module to Import CSV to Pivot Table |
| Import CSV to Pivot Table |
| Import CSV to Pivot Table – Results & Download Script |
What are the Advantages of Import CSV to Pivot Table in SCCM?
Importing CSV to Pivot Table SCCM Patch Report in SCCM can provide various benefits for patch compliance reporting and management.
They are:
1. Automation and Time Savings: Creating pivot tables from multiple CSV files can save time and reduce errors.
2. Consolidation of Data: SCCM admins can consolidate raw data by importing CSV files into a single pivot table for easier patch compliance analysis and reporting.
3. Centralized Reporting: Pivot tables are a flexible and automated tool for summarizing and analyzing data.
4. Customizable Views: Pivot tables customize views and filter data. SCCM admins tailor reports to focus on specific requirements. Pivot tables analyze data efficiently, offering the information needed to make informed decisions.
5. Efficient Data Analysis: Pivot tables organize data and reveal trends. Charts, graphs, and formatting enhance reports.
6. Scalability: CSVs to pivot tables scale with your organization. The same script handles new data without changes.
SCCM advantages vary based on your organization’s needs and environmental complexity.
PowerShell Module to Import CSV to Pivot Table
In search of how to write a simple script for it, I came across a PowerShell module developed by Doug Finke @dfinke. I found it really simple and easy to use.
I have done some homework and clarified a few doubts with the GitHub community: https://github.com/dfinke/ImportExcel/issues/559. A big shout out to #pkarunkar and Doug for their help.
Import CSV to Pivot Table
Finally, I can write a simple script to automate my regular activity. This script hardcodes all the file names, as these weekly reports will not be changed dynamically.
Copy the raw data into a folder and modify the script to match your input\output file names.
- Run the script. It will take around a minute to import CSV files into an Excel file in different worksheets, create a pivot table, and calculate the compliance percentage.
- Files are placed under a folder as below before running the script
- After executing the script, a new Excel file will be created as below with Pivot tale on the 3rd worksheet of the Excel file.
Import CSV to Pivot Table – Results & Download Script
I have two CSV files to import to Excel, so my pivot table is in the third worksheet. Right-click on the Compliance Percentage client’s number to convert it into a percentage representation.
Please find the actual code of the script from GitHub https://raw.githubusercontent.com/jampaniharish/OnlineScripts/master/WeeklyPatchComplianceReport.ps1.
Other scripts on the Internet may best suit this requirement. I hope someone will find this helpful.
Resources
- SCCM Patch Management is Enough for Vulnerability Management?
- SCCM Third-Party Updates Step-by-Step Background Process Guide Post
We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.
Author
Hareesh Jampani has 11 years of experience (calculated as of 2019) in the IT domain. During his career, he’s worked with Desktop support, Active Directory, VMware, SCCM, and PowerShell.
Wouldn’t this solve the issue? https://patchmypc.com/free-software-update-compliance-dashboard-reports-for-microsoft-sccm
Could you please share the format, column details for the RAW data files for “All Clients.csv” and “Non-Compliant Clients.csv” file, Also if you use any query to fetch that dats then kindly share, So i can use this solution to implement the patching automation in my envirnoment.