SQL query to list all disabled App Portal Catalog items with the associated install and uninstall collection names

[maheshpatil]

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

[CharlesW · Accepted solution]

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

[jdempsey]

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".

[jdempsey]

I just saw Charlie's reply, and it looks like we had similar answers, so just a couple notes:

1. I like Charlie's CASE statement for showing 'install' vs. 'uninstall' (or you could just show 0/1 if you know what those mean
2. He used INNER JOIN while I used LEFT JOIN.  An INNER JOIN will only show you disabled catalog items that have a deployment attached.  LEFT JOIN will show you all disabled catalog items, including ones without a deployment (e.g. General Catalog Items or Software Catalog Items where the deployments have been deleted).  For catalog items without a deployment, the CollectionID column would simply be displayed as NULL.  So, this is a matter of preference as to what you want to see in the results.
3. The Deleted column in WD_WebPackages tells you if a catalog item has been archived.  If you want to see all catalog items (archived or not), then exclude that condition.  If you only want to see disabled catalog items that are "active", then include the Deleted = 0.

[maheshpatil]

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

[jdempsey]

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.