- Flexera Community
- :
- App Broker
- :
- App Broker Forum
- :
- SQL query to list all disabled App Portal Catalog items with the associated install and uninstall co...
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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".
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I just saw Charlie's reply, and it looks like we had similar answers, so just a couple notes:
- I like Charlie's CASE statement for showing 'install' vs. 'uninstall' (or you could just show 0/1 if you know what those mean
- 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.
- 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.
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
