Issue with PMPC Dashboard can't do initial load because the table is missing. Null output from AA runbook.

[kevinmccurdybrd]

Hey!

Thanks for all the work you've done here these are fantastic reports. I've got the Update and Assignments dashboards working, but I'm having issues with the PMPC one. I'm seeing errors in the AA runbook log:

Both Az and AzureRM modules were detected on this machine. Az and AzureRM modules cannot be imported in the same session or used in the same script or runbook. If you are running PowerShell in an environment you control you can use the 'Uninstall-AzureRm' cmdlet to remove all AzureRm modules from your machine. If you are running in Azure Automation, take care that none of your runbooks import both Az and AzureRM modules. More information can be found here: https://aka.ms/azps-migration-guide
Exporting PMP apps list
System.Management.Automation.MethodInvocationException: Exception calling "Select" with "1" argument(s): "Cannot find column [notes]." ---> System.Data.EvaluateException: Cannot find column [notes].
   at System.Data.NameNode.Bind(DataTable table, List`1 list)
   at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
   at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
   at System.Data.DataExpression.Bind(DataTable table)
   at System.Data.Select..ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
   at System.Data.DataTable.Select(String filterExpression)
   at CallSite.Target(Closure , CallSite , Object , String )
   --- End of inner exception stack trace ---
   at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)
   at CallSite.Target(Closure , CallSite , Object , String )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
System.Management.Automation.MethodInvocationException: Exception calling "Select" with "1" argument(s): "Cannot find column [notes]." ---> System.Data.EvaluateException: Cannot find column [notes].
   at System.Data.NameNode.Bind(DataTable table, List`1 list)
   at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
   at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
   at System.Data.DataExpression.Bind(DataTable table)
   at System.Data.Select..ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
   at System.Data.DataTable.Select(String filterExpression)
   at CallSite.Target(Closure , CallSite , Object , String )
   --- End of inner exception stack trace ---
   at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)
   at CallSite.Target(Closure , CallSite , Object , String )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
System.Management.Automation.MethodInvocationException: Exception calling "Select" with "1" argument(s): "Cannot find column [notes]." ---> System.Data.EvaluateException: Cannot find column [notes].
   at System.Data.NameNode.Bind(DataTable table, List`1 list)
   at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
   at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
   at System.Data.DataExpression.Bind(DataTable table)
   at System.Data.Select..ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
   at System.Data.DataTable.Select(String filterExpression)
   at CallSite.Target(Closure , CallSite , Object , String )
   --- End of inner exception stack trace ---
   at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)
   at CallSite.Target(Closure , CallSite , Object , String )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
System.Management.Automation.MethodInvocationException: Exception calling "Select" with "1" argument(s): "Cannot find column [notes]." ---> System.Data.EvaluateException: Cannot find column [notes].
   at System.Data.NameNode.Bind(DataTable table, List`1 list)
   at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
   at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
   at System.Data.DataExpression.Bind(DataTable table)
   at System.Data.Select..ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
   at System.Data.DataTable.Select(String filterExpression)
   at CallSite.Target(Closure , CallSite , Object , String )
   --- End of inner exception stack trace ---
   at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)
   at CallSite.Target(Closure , CallSite , Object , String )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
Exporting status overview report for apps
Exporting status overview report for updates
Retrieving device install status reports for apps
Retrieving device install status reports for updates
Uploading CSV files to Azure storage account

When I check the SA, the container has files, but they're all basically 0 output, particularily the apps list and .

I'm using a managed identity, perms are right based on the docs, I think:

I'm probably missing something super basic, but could you help me figure this out? Really appreciate your work here.

I did revert one version of the script, your sortable change was also throwing errors for me so I just wanted to eliminate one possibility.

Exception calling "Select" with "1" argument(s): "Cannot find column [notes]." At line:259 char:5 + [array]$script:PmpApps = $DataTable.Select("notes LIKE 'PmpAppId: ... + ~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : EvaluateException

[SMSAgentSoftware]

Hi, appreciate your feedback! I hope it's not a silly question, but how many PMPC apps and updates do you have in Intune, and do you have assignments on them?

[kevinmccurdybrd]

Hey Trevor,

Thanks for the quick response. I've got a dozens of apps assigned, however I probably only have one update assigned (it was zero, but I figured that might be causing this problem, so I assigned one to a test group to ensure there was data) Do I need to do a couple more?

Cheers,

[SMSAgentSoftware]

Ok, if you've got PMPC apps assigned that's good. We'll need to step through the process to see where it's breaking down for you.

Can you create a new runbook with the following code and execute it? It should output the number of PMPC apps it is returning from MS Graph. eg "PMPC apps count: 259"

`$ProgressPreference = 'SilentlyContinue'

$url = $env:IDENTITY_ENDPOINT
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" $headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) $headers.Add("Metadata", "True") $body = @{resource='https://graph.microsoft.com/' } $script:accessToken = (Invoke-RestMethod $url -Method 'POST' -Headers $headers -ContentType 'application/x-www-form-urlencoded' -Body $body ).access_token

$URL = "https://graph.microsoft.com/v1.0/deviceAppManagement/mobileApps?`$filter=startswith(notes, 'Pmp')&$expand=assignments&$select=id,displayName,description,publisher,createdDateTime,lastModifiedDateTime,notes" $headers = @{'Authorization'="Bearer " + $accessToken} $Result = [System.Collections.ArrayList]::new() $GraphRequest = Invoke-WebRequest -Uri $URL -Headers $headers -Method Get -UseBasicParsing If ($GraphRequest.StatusCode -ne 200) { Return $GraphRequest } $Content = $GraphRequest.Content | ConvertFrom-Json [void]$Result.Add($Content.value) Write-Output "PMPC apps count: $($Result.Count)"`

[kevinmccurdybrd]

I'm not sure if I copied something incorrectly or I misunderstood the assignment but the job fails and when I run it in test pane I get:

[SMSAgentSoftware]

There are backticks (`) at the beginning and end of the code - you should remove these. GitHub markup doesn't display code sections properly if your code includes backticks, which this does 🙄

[kevinmccurdybrd]

D'oh. Sorry about that.

PMPC apps count: 1

I have significantly more apps via PMPC than that.

[kevinmccurdybrd]

[SMSAgentSoftware]

Ok, interesting. Can you please change the second to last line with the following and re-run?

[void]$Result.AddRange($Content.value)

[kevinmccurdybrd]

PMPC apps count: 597

[SMSAgentSoftware]

That's more like it! I guess there are some differences between how different runtimes interpret the code. I've updated the runbook - can you try the new version?

https://github.com/SMSAgentSoftware/MEM/blob/main/Patch%20My%20PC%20Power%20BI%20Report/Export-MSGraphPatchMyPCUpdateData.ps1

[kevinmccurdybrd]

So far so good, still running

[SMSAgentSoftware]

With the number of apps you have, you can expect it to take some time. There's a lot of data it has to export and it will have to negotiate some 429s due to Graph service limits.

[kevinmccurdybrd]

NO worries, it's clearly doing more now, old runtime was like 10 seconds. Keep at this man, you're doing great work. Are you planning separate reporting for patchmypc custom apps?

[SMSAgentSoftware]

I hadn't even thought about custom apps actually... I'll make a note to get those added in. Thanks for the suggestion!

[kevinmccurdybrd]

No, thank you for the rapid help! I'll let you know if I find anything else!