Need a SQL query that includes the list of all disabled (enabled=false) App Portal catalog items along with the associated install and uninstall collection names.
Thanks,
Mahesh
Aug 01, 2022 03:03 AM
Something similar to the following should give you what you need.
select packageVisible, packagetitle, sta.CollectionID,
CASE
WHEN sta.Type = 0 THEN 'install'
WHEN sta.Type =1 THEN 'uninstall'
END AS operation
from WD_WebPackages wp
inner join WD_SiteToAdvert sta on wp.PackageID = sta.PackageID
where PackageVisible = 0
Aug 01, 2022 09:10 AM
Something similar to the following should give you what you need.
select packageVisible, packagetitle, sta.CollectionID,
CASE
WHEN sta.Type = 0 THEN 'install'
WHEN sta.Type =1 THEN 'uninstall'
END AS operation
from WD_WebPackages wp
inner join WD_SiteToAdvert sta on wp.PackageID = sta.PackageID
where PackageVisible = 0
Aug 01, 2022 09:10 AM
I don't currently have a site with an SCCM connection, so I may not be able to provide an exact query, but I don't believe we store collection names in our database. The WD_WebPackages table has most of the catalog item information, so you can look at
SELECT PackageID, PackageTitle
FROM WD_WebPackages
WHERE PackageVisible = 0 AND Deleted = 0
Note: Deleted=0 only grabs non-archived catalog items.
If you want collection IDs (not names), you could get those from the WD_SiteToAdvert table using something like this
SELECT wp.PackageID, wp.PackageTitle, sta.CollectionID
FROM WD_WebPackages wp LEFT JOIN
WD_SiteToAdvert sta ON wp.PackageID = sta.PackageID
WHERE wp.PackageVisible = 0 AND wp.Deleted = 0
Edit: Corrected the column name from "Visible" to "PackageVisible".
Aug 01, 2022 09:16 AM - edited Aug 01, 2022 10:00 AM
I just saw Charlie's reply, and it looks like we had similar answers, so just a couple notes:
Aug 01, 2022 09:24 AM
Thank you for your reply.
Does "PackageVisible" field corresponds to the "Enabled" field within "View All Items" table in UI in App Portal which is highlighted in below screenshot?
Cause when I run the below query, I am getting more records than the number of records, if I select 'Enabled = False' in the UI.
Select * from [WD_WebPackages] where PackageVisible = 0
Aug 01, 2022 09:43 AM
Yes, PackageVisible = 0 corresponds with Enabled = False in the UI. However, the UI will only show you non-archived catalog items. That's why I suggested including the Deleted = 0 condition in your WHERE clause, which will filter out the archived catalog items.
Aug 01, 2022 09:58 AM