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.