Server Patch Compliance report.

from datetime import datetime import json cmd="az graph query -q \"" cmd+="patchinstallationresources " cmd+="| where type =~ \\\"microsoft.compute/virtualmachines/patchinstallationresults\\\" or type =~ \\\"microsoft.hybridcompute/machines/patchinstallationresults\\\" " cmd+="| where properties.lastModifiedDateTime > ago(30d) " cmd+="| where properties.status in~ (\\\"Succeeded\\\",\\\"Failed\\\",\\\"CompletedWithWarnings\\\",\\\"InProgress\\\") " cmd+="| parse id with * 'achines/' resourceName '/patchInstallationResults/' * " cmd+="| project id, resourceName, type, properties.status, properties.startDateTime, properties.lastModifiedDateTime, properties.startedBy, properties, tags " cmd+="| union (" cmd+="patchassessmentresources " cmd+="| where type =~ \\\"microsoft.compute/virtualmachines/patchassessmentresults\\\" or type =~ \\\"microsoft.hybridcompute/machines/patchassessmentresults\\\" " cmd+="| where properties.lastModifiedDateTime > ago(30d) " cmd+="| where properties.status in~ (\\\"Succeeded\\\",\\\"Failed\\\",\\\"CompletedWithWarnings\\\",\\\"InProgress\\\") " cmd+="| parse id with * 'achines/' resourceName '/patchAssessmentResults/' * " cmd+="| project id, resourceName, type, properties.status, properties.startDateTime, properties.lastModifiedDateTime, properties.startedBy , properties, tags" cmd+=")\"" def parse_datetime(date_str): """Attempt to parse a datetime string first with milliseconds, then without.""" for fmt in ('%Y-%m-%dT%H:%M:%S.%fZ', '%Y-%m-%dT%H:%M:%SZ'): try: return datetime.strptime(date_str, fmt) except ValueError: pass raise ValueError(f"Time data {date_str} does not match any expected format.") def generate_patch_report(data): # Initialize table with the desired structure and headers table = context.newtable() table.title = "Patch Installation Runs History for the past 30 days" table.num_cols = 8 # Number of columns according to headers table.num_rows = 1 # Starts with one row for headers table.has_header_row = True # Define header names based on the new structure headers = ["Resource Name", "OS Type", "Status", "Last Modified", "Installed Patches", "Failed Patches", "Pending Patches", "Reboot Status"] # Set headers in the first row for col_num, header in enumerate(headers): table.setval(0, col_num, header) # Collect server data into a list for sorting server_data = [] for server in data.get("data", []): properties = server.get("properties", {}) server_info = { "resourceName": server.get("resourceName", "Unknown Resource"), "osType": properties.get("osType", "N/A"), "status": properties.get("status", "N/A"), "lastModifiedDateTime": properties.get("lastModifiedDateTime", "N/A"), "installedPatchCount": properties.get("installedPatchCount", 0), "failedPatchCount": properties.get("failedPatchCount", 0), "pendingPatchCount": properties.get("pendingPatchCount", 0), "rebootStatus": properties.get("rebootStatus", "N/A") } server_data.append(server_info) # Sort the collected data by "Last Modified Date" in descending order server_data.sort(key=lambda x: parse_datetime(x["lastModifiedDateTime"]), reverse=True) # Populate the table with sorted data for row_num, server in enumerate(server_data, start=1): # Starting from the second row table.num_rows += 1 # Add a row for each server values = [server["resourceName"], server["osType"], server["status"], server["lastModifiedDateTime"], str(server["installedPatchCount"]), str(server["failedPatchCount"]), str(server["pendingPatchCount"]), server["rebootStatus"]] for col_num, value in enumerate(values): table.setval(row_num, col_num, value) op = _exe(None,cmd) #print(op) # for raw results of the above query patch_compliance_table = generate_patch_report(json.loads(op))