SELECT ID, DISPLAY_NAME, FILE_SIZE,
( SELECT COUNT(MI.ID) FROM MI WHERE MI.SOFTWARE_ID = SOFTWARE.ID) as MICOUNT
FROM SOFTWARE
WHERE SOFTWARE.FILE_SIZE > 0
HAVING MICOUNT = 0
ORDER BY DISPLAY_NAME
This report can be scheduled to run on a recurring basis to remind someone to clean up the software inventory.
You can also create a smart label to identify software titles that can have their attachment deleted. This can make the process much quicker.
- In the admin console browse to Inventory, Software
- Click Smart Label under the search box
- For the search criteria choose File Size > 0
- Create a name for the label, I called mine File Cleanup
- Click Save
- Once the label is created click on the link in the notification to view smart labels or browse to Home, Label Management, Smart Labels
- Find the label that you just created
- Replace the SQL with the following:
SELECT DISPLAY_NAME, PUBLISHER, SOFTWARE.ID as TOPIC_ID, (SELECT COUNT(MI.ID) FROM MI WHERE MI.SOFTWARE_ID = SOFTWARE.ID) as MICOUNT FROM SOFTWARE WHERE ((FILE_SIZE > '0'))
HAVING MICOUNT = 0 - Save the label
When you go back to the software inventory you will now be able to filter the results using the new label.
Comments