# 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')
```

Scontrols (if you have these, you really need to look at org health):

```
SELECT Id, DeveloperName 
FROM Scontrol
```

### Queries for things that you need to update

Apex Classes not in the most recent 3 versions (update API version each release):

```
SELECT Name, APIVersion
FROM ApexClass
WHERE APIVersion <= 50.0 AND NamespacePrefix = null
```

Visualforce pages not in the most recent 3 versions (you may also want to look at making these LWC's):

```
SELECT Name, APIVersion
FROM ApexPage
WHERE APIVersion <= 50.0 AND NamespacePrefix = null
```

Aura Components not in the most recent 3 versions (you may also want to look at making these LWC's):

```
SELECT DeveloperName, ApiVersion 
FROM AuraDefinitionBundle
WHERE APIVersion <= 50.0 AND NamespacePrefix = null
```

<p class="callout warning">Locker was introduced with API version 39.0. Regression test if upgrading API version.</p>

Lightning Web Components not in the most recent 3 versions (use tooling API):

```
SELECT DeveloperName, ApiVersion 
FROM LightningComponentBundle
WHERE APIVersion <= 50.0 AND NamespacePrefix = null
```

Flows not in the most recent 3 versions (use tooling API):

```
SELECT MasterLabel, ApiVersion 
FROM Flow
WHERE APIVersion <= 50.0 AND Definition.NamespacePrefix = null
```

### Permissions Best Practices

#### Review users who have Customize Application, Modify All Data and View All Data

##### Unique Counts of Users

Customize Application

```
SELECT COUNT_DISTINCT(AssigneeId)
FROM PermissionSetAssignment
WHERE Assignee.IsActive = true AND PermissionSet.PermissionsCustomizeApplication = true
```

Modify All Data

```
SELECT COUNT_DISTINCT(AssigneeId)
FROM PermissionSetAssignment
WHERE Assignee.IsActive = true AND PermissionSet.PermissionsModifyAllData = true
```

View All Data

```
SELECT COUNT_DISTINCT(AssigneeId)
FROM PermissionSetAssignment
WHERE Assignee.IsActive = true AND PermissionSet.PermissionsViewAllData = true
```

##### Review Users and the permission sets or profiles that grant them access

Customize Application

```
SELECT PermissionSet.Label, PermissionSet.Profile.Name, Assignee.Name
FROM PermissionSetAssignment
WHERE Assignee.IsActive = true AND PermissionSet.PermissionsCustomizeApplication = true
```

Modify All Data

```
SELECT PermissionSet.Label, PermissionSet.Profile.Name, Assignee.Name
FROM PermissionSetAssignment
WHERE Assignee.IsActive = true AND PermissionSet.PermissionsModifyAllData = true
```

View All Data

```
SELECT PermissionSet.Label, PermissionSet.Profile.Name, Assignee.Name
FROM PermissionSetAssignment
WHERE Assignee.IsActive = true AND PermissionSet.PermissionsViewAllData = true
```

<p class="callout info">Also, in case you haven't run it recently, run [the optimizer report](https://help.salesforce.com/articleView?id=optimizer_kick_off.htm&type=0)! It'll help you identify areas for improvement beyond the above.</p>

<p class="callout info">If you have premier or signature support, you may also want to look into the [org health assessment accelerator](https://help.salesforce.com/articleView?id=000319484&type=1&mode=1).</p>