01 - Software Update Dashboard report runs interminably

[Atreus21]

Hi fellas,

Just got my 30 day trial of Enterprise and also wanted to use the Patch My PC Software Updates Dashboard.  But when I run it as it's configured out of the box, it runs until the SQL Reporting times out.  If I extend the timeout to never expire, it eventually throws an error after many hours of running.

If I cancel the report an target a smaller collection of about 100 devices or fewer, it will eventually resolve after about 10 minutes.  But I wanted to give my security guy the whole thing if possible.

I also suspect there may be something more fundamental afoot, as the native SCCM Software Updates Dashboard in Monitoring freezes the console for a good 5 minutes when you run it, but eventually resolves.  I thought the two issues may be related.

Is there any kind of indexing I may be missing?  The reporting server is on the primary site server - would it beneficial to separate them?  The server's provisioned with 32GB of RAM and is on our highest performing disks - task manager doesn't generally show the server's resources being exhausted.

Would appreciate any advice.  Thanks.

[Justin Chalfant (Patch My PC)]

Hey There,

There are a lot of queries happening in the first dashboard. How is #3 or #4 dashboards? It shouldn't timeout like you are seeing or take that long though.

The biggest factor here is SQL indexing do you have any indexing plans running recurring on the database?

Hi fellas,

Just got my 30 day trial of Enterprise and also wanted to use the Patch My PC Software Updates Dashboard.  But when I run it as it's configured out of the box, it runs until the SQL Reporting times out.  If I extend the timeout to never expire, it eventually throws an error after many hours of running.

If I cancel the report an target a smaller collection of about 100 devices or fewer, it will eventually resolve after about 10 minutes.  But I wanted to give my security guy the whole thing if possible.

I also suspect there may be something more fundamental afoot, as the native SCCM Software Updates Dashboard in Monitoring freezes the console for a good 5 minutes when you run it, but eventually resolves.  I thought the two issues may be related.

Is there any kind of indexing I may be missing?  The reporting server is on the primary site server - would it beneficial to separate them?  The server's provisioned with 32GB of RAM and is on our highest performing disks - task manager doesn't generally show the server's resources being exhausted.

Would appreciate any advice.  Thanks.

[Atreus21]

I tried 03 - SUG Compliance - Dashboard and that one runs with about the same success as 01.

However, I don't think there's much in the way of indexing going on.  I've implemented Olga Hallengrin's solution here:  https://ola.hallengren.com/downloads.html, since you suggested looking into it.  I'm running the indexing job now and it's taking awhile, so I hope it's getting things straightened out.

I'll let you know how it goes sir.

Incidentally, we'll be activating our subscription soon.  I must confess the Application Publishing service, when used in conjunction with MDT's Application Mapping capability is....well it's going to spoil us rotten if we buy it.  Truly an astonishing product sir, thank you.

[Unikom]

Hi, we have the same issue, we know what is causing it, the problem is the Compatibility level of the database, when set to 110 mode the Reports work for us, we did this when we had SQL 2014 as base but do not want to do this now on SQL 2017.

Can anyone from Patch My PC do a overhaul of the reports so that it can run the latest compatibility level on the database.

More info from MS regarding CM and SQL compatibility levels.

https://support.microsoft.com/en-us/help/3196320/sql-query-times-out-or-console-slow-on-certain-configuration-manager-d

[Justin Chalfant (Patch My PC)]

Do you at least test one query on 2017 with 110 compatibility to see if it helped?

Hi, we have the same issue, we know what is causing it, the problem is the Compatibility level of the database, when set to 110 mode the Reports work for us, we did this when we had SQL 2014 as base but do not want to do this now on SQL 2017.

Can anyone from Patch My PC do a overhaul of the reports so that it can run the latest compatibility level on the database.

More info from MS regarding CM and SQL compatibility levels.

https://support.microsoft.com/en-us/help/3196320/sql-query-times-out-or-console-slow-on-certain-configuration-manager-d

[Unikom]

Yes i did and it worked, sorry, should have written that in my previous post.

[Unikom]

Hello!

Any updates regarding the issue with Compatibility level of the database?

Do you at least test one query on 2017 with 110 compatibility to see if it helped?

Hi, we have the same issue, we know what is causing it, the problem is the Compatibility level of the database, when set to 110 mode the Reports work for us, we did this when we had SQL 2014 as base but do not want to do this now on SQL 2017.

Can anyone from Patch My PC do a overhaul of the reports so that it can run the latest compatibility level on the database.

More info from MS regarding CM and SQL compatibility levels.

https://support.microsoft.com/en-us/help/3196320/sql-query-times-out-or-console-slow-on-certain-configuration-manager-d

[Justin Chalfant (Patch My PC)]

No update other than this KB from Microsoft https://support.microsoft.com/en-us/help/4542722/sql-database-compatibility-level-changes-after-applying-configuration