SQL query to only return PMP updates

[brandonm]

Hi. We have just purchased Patch My PC and I am in the process of getting it setup. I have custom SSRS reports for displaying data on "needed" and "failed" updates for our computers. I am building a report just for third-party updates and looking for a simple way to query for just Patch My PC updates. I am using the dbo.v_UpdateInfo view to pull in a list of update names. I thought to use the ArticleID column, WHERE ArticleID LIKE 'PMP%' but I have noticed PMP updates do not always being with PMP. There is no Vendor column, nor do I see any type of vendor uid in this view. SCCM console can filter on Vendor, so it is obviously possible. Any suggestions appreciated. Thanks.

[Cody Mathis (Patch My PC)]

brandonm,

You are welcome to install the SSRS reports that we provide and look at the existing queries to see how we do this filtering.

I believe there is a particular column that contains a URL. We use this column to filter out the Microsoft updates.

https://patchmypc.com/free-software-update-compliance-dashboard-reports-for-microsoft-sccm

[brandonm]

Thanks Cody. I got the SSRS reports installed and found what you are referring to in the "Sub -Third-Party Updates By Device" report. It is the column InfoURL in the dbo.v_UpdateInfo view. Microsoft updates look to always have that populated with a URL, where third-party updates have a blank value (not null). That should work fine, though I can see that non-Patch My PC third-party updates may come back with that query (HP and Dell updates), but that is a very limited scope in our environment as we don't typically deploy those.