Software_Updates_Reporting_Summarizer.ps1 Issue

[ildrosos]

Hello and congratulations for your great work . I was trying to deploy your solution following the perfect documentation created at https://docs.smsagent.blog/microsoft-endpoint-manager-reporting/windows-update-for-business-custom-reporting/deploy-the-solution/deploy-the-azure-automation-runbooks.

My problem is that both runbooks (summarizer+trend) keep failing to run with error message : Invocation of the Log Analytics query failed: Operation returned an invalid status code 'BadRequest'

I read in your instructions that this might happen during the first runs because the appropriate tables are not yet created but this is running for more than 30 days and keeps failing. I checked my log analytics workspace and I have several SU tables created (check screenshot) . IN addition I rechecked the system managed automation account has Log Analytics agent contributor role assigned to the Log Analytics workspace . I am stuck can you help ?

[SMSAgentSoftware]

Thanks for the feedback! If you run the Kusto queries in the runbooks against the log analytics workspace directly, does it still error? What errors do you get?

[ildrosos]

Hello I apologise for the delay , thanks for answering. The solution is deployed to 1 of my customers and I dont visit him too often to check . I tried to run a kusto query I found in /Software_Updates_Reporting_Summarizer.ps1 The problem is when I try to run it I get a lot of errors (syntax) about the ` symbol .

[ildrosos]

I managed to remove all of them (all the symbols) but now I get this

[SMSAgentSoftware]

I managed to remove all of them (all the symbols) but now I get this

"FeatureUpdatesPaused_d" occurs twice in the query - lines 74 & 186. Can you check/screenshot those lines in your query?

[ildrosos]

It seems they are not recognized correctly I don't know if it's a syntax error cause I removed "`" symbol ..

[SMSAgentSoftware]

Ah ok, looks like these are missing in your table schema. Can you run this query and tell me if anything is returned?

SU_WUPolicyState_CL | getschema | where ColumnName has "FeatureUpdatesPaused"

[ildrosos]

no nothing is returned

[SMSAgentSoftware]

Ok, can you please replace line 74 with the following and tell me if that works?

| project QualityUpdatesDeferralInDays_d, FeatureUpdatesDeferralInDays_d, FeatureUpdatesPaused_d=column_ifexists("FeatureUpdatesPaused_d",real(null)), QualityUpdatesPaused_d=column_ifexists("QualityUpdatesPaused_d",real(null)), FeatureUpdatePausePeriodInDays_d=column_ifexists("FeatureUpdatePausePeriodInDays_d",real(null)), QualityUpdatePausePeriodInDays_d=column_ifexists("QualityUpdatePausePeriodInDays_d",real(null)), PauseFeatureUpdatesStartTime_t=column_ifexists("PauseFeatureUpdatesStartTime_t",datetime(null)), PauseQualityUpdatesStartTime_t=column_ifexists("PauseQualityUpdatesStartTime_t",datetime(null)), PauseFeatureUpdatesEndTime_t=column_ifexists("PauseFeatureUpdatesEndTime_t",datetime(null)), PauseQualityUpdatesEndTime_t=column_ifexists("PauseQualityUpdatesEndTime_t",datetime(null)), IntuneDeviceID_g

[Triipie19]

Hi there,

I have the same issue as above, however I'm not sure which line 74 you are referring too.

Line 74 in the original script is

$uri = "https://" + $customerId + ".ods.opinsights.azure.com" + $resource + "?api-version=2016-04-01"

I found a similar line to what you are referring to on line 1120:

| project QualityUpdatesDeferralInDays_d,FeatureUpdatesDeferralInDays_d,FeatureUpdatesPaused_d,QualityUpdatesPaused_d,FeatureUpdatePausePeriodInDays_d,QualityUpdatePausePeriodInDays_d,PauseFeatureUpdatesStartTime_t,PauseQualityUpdatesStartTime_t,PauseFeatureUpdatesEndTime_t,PauseQualityUpdatesEndTime_t,IntuneDeviceID_g )on IntuneDeviceID_g;

but changing that to your suggestion above doesn't make a difference.

I can see the tables SU_* have been created and I can query them using the query builder.

Any advice is appreciated.

[SMSAgentSoftware]

It's line 74 of the KQL query, not the original script. Are you able to run that query with the new line 74 directly in Log Analytics? I need to see if it gives any error still.

[Triipie19]

Sure, sorry about that.

I took the query from line 1046 to 1241, removed all the backticks as Azure was complaining about those (same as the comment above) and then replaced line 74 as you suggested and I get the following error:

top-nested operator: Failed to resolve table or column or scalar or graph expression named 'RebootRequired_s'

Entire query I ran is here https://pastebin.com/Wn1rSNw2

[SMSAgentSoftware]

Wow I didn't anticipate that all these columns could be completely missing any values! Can you replace line 57 of the KQL query with this: top-nested of RebootRequired=column_ifexists("RebootRequired_s","") by temp5=max(1),

[Triipie19]

Sorry for the delay on getting back to you.

Made that change and now have a new error:

'project' operator: Failed to resolve scalar expressed named 'FeatureUpdatesPaused_d'

If I look in the 'SU_WUPolicyState_CL' table, there is no heading for 'FeatureUpdatesPaused_d'.

I'm guessing that these issues are because I am doing a test of this report on a small subset of devices as part of our WUfB rollout, so some of the columns aren't being created by the Software Updates Inventory Script. For example, I haven't had to pause a Windows Update yet, so the script is returning no value and therefore not creating the column in the log analytics workspace?

[SMSAgentSoftware]

Yes you are correct about that - in my own environment all these columns are existing from the data I'm getting from my clients, but that may not be true for everyone, especially a smaller set of devices. Finding which columns could be missing data is the thing. For the 'FeatureUpdatesPaused_d' error, did you change line 74 with the updated one from a few comments ago?

[Triipie19]

Yeah that makes sense.

I didn't after I made the other change, my fault. I've now made that change. Updated KQL here https://pastebin.com/Se2iSS6J.

Error I am now getting is:

'project' operator: failed to resolve scalar expression named 'EngageReminderLastShownTime_t'

I don't see a way to transpose the fix for what you said to do for line 74 onto the project statement starting line 102.

[SMSAgentSoftware]

Ok, we're getting there! Can you replace lines 136 through 140:

EngageReminderLastShownTime=column_ifexists("EngageReminderLastShownTime_t",datetime(null)), ScheduledRebootTime=column_ifexists("ScheduledRebootTime_t",datetime(null)), PendingRebootStartTime=column_ifexists("PendingRebootStartTime_t",datetime(null)), AutoUpdateStatus=AutoUpdateStatus_s, WURebootRequired=column_ifexists("RebootRequired_s",""),

[Triipie19]

Yep so that now completes the query successfully.

So to resolve the issue with the runbook, I need to take the changes we've made and overwrite the contents of the Execute Devices Query region in the original runbook, publish then run it?

[SMSAgentSoftware]

Yes, correct. I've updated the runbook code with the new query. Do let me know if it works for you! https://github.com/SMSAgentSoftware/MEM/blob/main/Software%20Updates%20Reporting%20Solution/Azure%20automation%20runbooks/Software_Updates_Reporting_Summarizer.ps1

[Triipie19]

Yep absolutely perfect - thanks so much for your help with this. The trend analyzer workbook has worked correctly first time.

However, the PowerBI dashboard doesn't load all the queries; I suspect maybe because lack of data again? I get 14 query blocks which have been blocked by the attached errors, https://pastebin.com/LbNxdEjq.

[SMSAgentSoftware]

Excellent. Appreciate your help improving this solution :)

For the PowerBI report, I updated the queries in the template and posted a new version. Can you try it out? I'm unsure if the last error in your list will go away if there is no data in the source table though.

https://github.com/SMSAgentSoftware/MEM/tree/main/Software%20Updates%20Reporting%20Solution/Power%20BI%20template

[Triipie19]

Hi,

Sorry for the delay, the long weekend along with another project took my focus.

Thanks for updating the PowerBI dashboard, however the dashboard still isn't loading queries correctly/if at all. I have data in my tables in the Workspace, I have a few machines that have since picked up updates from WUfB plus machines in WSUS so there should be data to be used by the dashboard, however when I open it I still get an error for the "WindowsUpgradeSuccesses" query, but all other queries seem to load correctly. However, on the actual dashboard I get three warnings:

1) There are pending changes in your queries that haven't been applied - this on errors if I try to "Apply changes" 2) One or more calculated objects need to be manually refreshed - This one works fine when I press "Refresh Now" 3) Some of the tables have incomplete or no data - This errors if I try to "Refresh Now"

On the dashboard itself, the "Compliance - Latest CU's" tab for example, shows '(Blank)' on the sections, same with "Compliance - Previous CU's". I'm not sure whether the Trend analyser is supposed to create extra tables in the workspace, but I can't find any reference to tables such as "CUComplianceSummaryLatestOnly" which is in the PowerBI dashboard when I click on one of the objects.

[SMSAgentSoftware]

I guess the main problem here is you are working with a limited data set, and the report was intended to work on a larger data set of production devices. This is why we're having to make adjustments for missing columns etc.

The table names in the report are not the same as the table names in the LA workspace.

Below is the query that the WindowsUpgradeSuccesses is using - you could try running that manually and report the actual error.

SU_WindowsSetup_CL | where column_ifexists("SetupHostResult_d","nada") == 0 | where isnotnull(column_ifexists("DownlevelBuildNumber_d","nada")) | summarize arg_max(TimeGenerated,*) by IntuneDeviceID_g | join kind=leftouter (SU_ClientComplianceStatus_CL | summarize arg_max(InventoryDate_t,*) by IntuneDeviceID_g | project IntuneDeviceID_g,CurrentUser_s,DisplayVersion_s,EditionID_s,FriendlyOSName_s,FullBuildNmber_s,LastSyncTime_t,ProductName_s,Windows_Release_s ) on IntuneDeviceID_g | join kind=leftouter (SU_OSUpdateHistory_CL | distinct OSBaseBuild_d,OSVersion_s | project OSBaseBuild_d,OSVersion_s ) on $left.DownlevelBuildNumber_d == $right.OSBaseBuild_d | project ComputerName=ComputerName_s, CurrentUser=CurrentUser_s, SourceOS=strcat(DownlevelProductName_s," ",OSVersion_s), CurrentOS=FriendlyOSName_s, SourceOSBuildNumber=DownlevelBuildNumber_d, CurrentOSBuildNumber=FullBuildNmber_s, OSInstallDate=OSInstallDate_t, LastSyncTime=LastSyncTime_t, Windows_Release=Windows_Release_s | order by ComputerName asc

I've started working on v2 of this solution that uses Azure SQL database instead of Log Analytics. It will be more expensive to run and requires more infrastructure, but it should avoid this limitation of having potentially incomplete data. It's some time away though.

[Triipie19]

The error I get on that query is:

'join' operator: Failed to resolve column named "DownlevelBuildNumber_d

I appreciate you taking your time and helping me with this, I appreciate that it should be used on a dataset but as I want to use this as a POC it's difficult to get the go ahead from management roll it out estate wide.

[SMSAgentSoftware]

In this case we can't handle missing columns in the query. What you can do is edit the table schema directly and add any missing columns. To do this, in the LA workspace, go to Tables. Locate the SU_WindowsSetup_CL table. Open the context menu and select Edit schema. Check the list of custom columns against the reference schema attached, and add any missing columns.

SU_WindowsSetup_CL.csv

[Triipie19]

Thanks, so that query has now run correctly and the dashboard is now populating correctly :)

I suspect that the dashboard uses more than one table though, so is it worth pulling together the lists of all the columns for all the tables and then I can go through each table and add the missing columns, if there were.

For reference, that table only had 7 columns created, so I had to add an extra 33.

[SMSAgentSoftware]

If the report is now displaying correctly there shouldn't be anything more for you to do. However, I think your idea is good so I have published a table schema reference here if you want to check: https://docs.smsagent.blog/microsoft-endpoint-manager-reporting/windows-update-for-business-custom-reporting/table-schema-reference

This solution uses the Azure Monitor Http data collector API, which is now deprecated. This API doesn't require you to pre-create any tables with a fixed schema as the ingestion pipeline creates the tables for you with a schema based on the data you are sending it. For this reason, some columns can be missing if you aren't sending a full data set.

I have recently migrated my own implementation of this solution to the newer Logs Ingestion API. This API requires a fixed table schema and all the tables must be created beforehand, so it doesn't suffer from missing columns. Successfully migrating is quite a bit of work, however.

For v2 of this solution though I'm still in favour of using Azure SQL database rather than Log Analytics.

[Triipie19]

Glad to have been of some assistance to you with all the help you've given me (and the community)

That makes sense, will look forward to your write up on that either way, regardless of how difficult the migration may be.

The last question I have is more of a general PowerBI question, there are still some (Blank) entries in the dashboard, and I know these are because of no data (queries that are aimed at failed updates for example), how can I inspect the element on PowerBI to see the KQL running (to then go into the LA workspace and query it directly on the tables)?

[SMSAgentSoftware]

Thanks for your feedback and contribution!

For your question, in the Power BI desktop app, when you select a visual, on the right-hand side in the Data blade you'll see a green tick against any queries that visual is using. Right-click a query and choose Edit query. That will open the Power Query Editor. Select the query and then choose Advanced Editor from the Query menu. In there you'll see the original KQL query being used.

[LukassUK]

Hi! Thanks for this solution - it looks great from the outset and i look forward to using it. However, i haven't actually had it working yet; I am getting exactly the same errors as the OP.
I have an enormous environment that this will be going into, so it is currently in the Dev tenant for proof of concept and i only have 1 VM feeding the client data. Are you therefore saying that if we have a small data set, then the tables and columns may not get fully created, causing this error. If this happens and we can't enlarge the client machine pool feeding the LA workspace, we need to manually create the missing tables/columns? If so, that's fine because it gives a way forward. If not and i have misunderstood what has been said above, could you point me in the right direction please?

Many thanks indeed.

[SMSAgentSoftware]

Hello, yes you are correct, with a small dataset there are very likely some columns that will be missing as the http data collector API only creates columns based on the data you are sending it, which may be incomplete. Based on the previous feedback I have updated both the summarizer runbook and Power BI report to better handle potentially missing columns where possible. For the query that uses the SU_WindowsSetup_CL table specifically, that wasn't possible so do check with the table schema whether any columns are missing and create them.

[LukassUK]

Thanks very much indeed for confirming. I’ll check this table in the morning and add in what is missing. Are you aware of any other tables that may need gaps filling in in order for the run book to successfully run please?

[LukassUK]

I think i may have spotted a very minor issue with the Software_Updates_reportingSummarizer runbook: On Lines 836 and 881, there is this line: `$Row["$"] = Remove-HTMLFromString "$($cells[$t].ToString().Replace('
',[Environment]::NewLine).Replace('Â','').Replace('"','""').Replace('>','-'))"`

Powershell_ISE says that there is an error on this line that it is missing a terminator.
If you add another ) after NewLine, then the ISE then marks it as ok.

Also, in the client script, on line 713, there is the following line: ($FinalEventArray | where {$_.UpdateId -eq $UpdateIdForReboot}).RebootRequired = "True"

This should be -eq "True" at the end, not = "True"

[SMSAgentSoftware]

Hi, thanks for your comments.

On lines 836 & 881 the problem is that you have a comma after the "Ã" character. I don't know where this came from but the comma is not present in the script published to GitHub. So simply change .Replace('Â','') with .Replace('Ã','').

Line 713 is correct - nothing needs to change here. This line sets the RebootRequired value to True for updates in the array where a reboot is required.

[LukassUK]

Aah, I see. Thanks for correcting this.

Following the thread and running the Kusto queries, i appeared to have the same columns missing as Ildrosos and Triipie19, but the updated Summarizer has allowed it to finish successfully.

In terms of manually adding the missing columns to the tables, trying to add these manually, Azure greys out the Save button and won't let you add it. Going to the top of the page, it states "This is a classic custom log table. To have full control of the table schema, migrate to DCR-based custom logs."

If i migrate to Manual Schema Management, the table then says that it is a type Custom table, not Custom table (classic). Is doing this migration going to cause a problem, or will it work just the same?

Thanks very much for your help!

[SMSAgentSoftware]

I have also seen this, where the Save button is greyed out when trying to add a new custom column on classic tables. It could not be done with PowerShell either. I do not recommend migrating a table to DCR-based logs because, according to MS documentation, the legacy API will not be able to create any new columns in the table - it can only post to existing columns. This would be fine if you have a full set of columns, but if you're missing some, the API will never be able to create them.

Unfortunately if you can't add the missing custom columns I don't have a solution for you other than to try to post data that will create the missing columns if possible.