Skip to main content

When you don't follow best practices or just need to do some cleaning

Queries to help you find what to yeet

Layouts without page layout assignments (using Tooling API in Developer Console):

SELECT Id, Name, EntityDefinition.MasterLabel
FROM Layout
WHERE ID NOT IN (SELECT LayoutId
				 FROM ProfileLayout)

Permission Sets with less than 20 active assignments:

SELECT COUNT(id), PermissionSet.Label
FROM PermissionSetAssignment
WHERE Assignee.IsActive = TRUE AND PermissionSet.IsOwnedByProfile = FALSE
GROUP BY PermissionSet.Label
HAVING COUNT(Id) < 20
ORDER BY COUNT(Id) DESC

Profiles with less than 5 active assignments:

SELECT COUNT(id), PermissionSet.Profile.Name
FROM PermissionSetAssignment
WHERE Assignee.IsActive = TRUE AND PermissionSet.IsOwnedByProfile = TRUE
GROUP BY PermissionSet.Profile.Name
HAVING COUNT(Id) < 5
ORDER BY COUNT(Id) DESC

List Views that haven't been viewed in 90 days:

SELECT Id, DeveloperName,Name,SobjectType,LastReferencedDate 
FROM ListView
WHERE LastReferencedDate < LAST_N_DAYS:90

Email Templates that haven't been used in 90 days or haven't ever been used:

SELECT Id,Name, Folder.Name,LastUsedDate,TimesUsed 
FROM EmailTemplate
WHERE LastUsedDate < LAST_N_DAYS:90 OR TimesUsed = null

Reports that haven't been run in 90 days or haven't ever been used:

SELECT Id, Name, FolderName, LastViewedDate, LastReferencedDate
FROM Report 
WHERE (LastViewedDate < LAST_N_DAYS:90 AND LastReferencedDate < LAST_N_DAYS:90) OR
	  (LastViewedDate = null AND LastReferencedDate = null)

Dashboards that haven't been run in 90 days or haven't ever been viewed:

SELECT Id, Title, FolderName, LastViewedDate, LastReferencedDate
FROM Dashboard
WHERE (LastViewedDate < LAST_N_DAYS:90 AND LastReferencedDate < LAST_N_DAYS:90) OR
	  (LastViewedDate = null AND LastReferencedDate = null)

Roles / Queues without assignments:

SELECT Name, Type, DeveloperName, RelatedId
FROM GROUP 
WHERE Id NOT IN (SELECT GroupId FROM GroupMember) AND 
	  Type IN ('Role','Queue')