# Useful Queries

# Who has what permission?

#### A few housekeeping items

The permission set object is an amalgamation of Permission Sets and Profiles. You can determine if a record is a profile by using the "IsOwnedByProfile" field. You can also query on the permission set permissions to limit results.

[SOAP API Developer Documentation](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_permissionset.htm)

#### Field Level Security (FLS)

[SOAP API Developer Documentation](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_fieldpermissions.htm?search_text=Field%20Permissions)

##### Who has access to a field by a specific Profile?

```
SELECT Id, Field, PermissionsRead, PermissionsEdit, SobjectType, Parent.Profile.Name
FROM FieldPermissions
WHERE Parent.Profile.Name = 'System Administrator' AND Field = 'Account.Type'
```

##### Who has access to a field by specific Permission Set?

```
SELECT Id, Field, PermissionsRead, PermissionsEdit, SobjectType, Parent.Profile.Name, Parent.Label
FROM FieldPermissions
WHERE Field = 'Account.Type'
```

##### Who has access to a specific field?

<p class="callout info">The absence of records indicates that they have *no* access to that field.</p>

```
SELECT Id, Field, PermissionsRead, PermissionsEdit, SobjectType, Parent.Profile.Name, Parent.Label
FROM FieldPermissions
WHERE Field = 'Account.Type'
```

##### Who does not have access to a field?

```
SELECT Id, Label, Profile.Name
FROM PermissionSet
WHERE ID NOT IN (SELECT ParentID
                 FROM FieldPermissions
                 WHERE Field = 'Account.Type')
```

### Object Level Security

[SOAP API Developer Documentation](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_objectpermissions.htm)

##### Who has access to an object by a specific Profile?

```
SELECT Id, Field, PermissionsRead, PermissionsCreate, PermissionsEdit, PermissionsDelete, PermissionsViewAllRecords, PermissionsModifyAllRecords, SobjectType, Parent.Profile.Name
FROM ObjectPermissions
WHERE Parent.Profile.Name = 'System Administrator' AND SobjectType = 'Account'
```

##### Who has access to an object by a specific Permission Set?

```
SELECT Id, Field, PermissionsRead, PermissionsCreate, PermissionsEdit, PermissionsDelete, PermissionsViewAllRecords, PermissionsModifyAllRecords, SobjectType, Parent.Label
FROM ObjectPermissions
WHERE Parent.Label = 'Account Permission Set' AND SobjectType = 'Account'
```

##### Who has access to a specific object?

<p class="callout info">The absence of records indicates that they have *no* access to that object.</p>

```
SELECT Id, Field, PermissionsRead, PermissionsCreate, PermissionsEdit, PermissionsDelete, PermissionsViewAllRecords, PermissionsModifyAllRecords, SobjectType, Parent.Label, Parent.Profile.Name
FROM ObjectPermissions
WHERE SobjectType = 'Account'
```

##### Who does not have access to an object?

```
SELECT Id, Label, Profile.Name
FROM PermissionSet
WHERE ID NOT IN (SELECT ParentID
                 FROM ObjectPermissions
                 WHERE SobjectType = 'Account')
```

### Setup Entity Access

This object is for querying many object permissions in Salesforce. Those are:

<table border="1" id="bkmrk-type-object-api-name" style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 50%;">**Type**</td><td style="width: 25%;">**Object API Name**</td><td style="width: 25%;">**Name Field**</td></tr><tr><td style="width: 50%;">Apex Class</td><td style="width: 25%;">ApexClass</td><td style="width: 25%;">Name</td></tr><tr><td style="width: 50%;">Visualforce Page</td><td style="width: 25%;">ApexPage</td><td style="width: 25%;">Name</td></tr><tr><td style="width: 50%;">Custom Metadata Type</td><td style="width: 25%;">EntityDefinition</td><td style="width: 25%;">QualifiedAPIName</td></tr><tr><td style="width: 50%;">Custom Setting</td><td style="width: 25%;">EntityDefinition</td><td style="width: 25%;">QualifiedAPIName</td></tr><tr><td style="width: 50%;">Applications (Apps in app launcher)</td><td style="width: 25%;">AppMenuItem</td><td style="width: 25%;">Name</td></tr><tr><td style="width: 50%;">Connected Applications</td><td style="width: 25%;">ConnectedApplication</td><td style="width: 25%;">Name</td></tr><tr><td style="width: 50%;">Custom Permission</td><td style="width: 25%;">CustomPermission</td><td style="width: 25%;">MasterLabel</td></tr></tbody></table>

[SOAP API Developer Documentation](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_setupentityaccess.htm)

The process is the same for all of the above in the below examples. Just replace the object API name with the one for which you are looking to find permissions and the "Name" value in the where clause with the appropriate name field in the table above. For Custom Settings, use "IsCustomSetting" to filter. For Custom Metadata types, add QualifiedApiName LIKE '%\_\_mdt' to the filter.

```
SELECT Parent.Label, Parent.Profile.Name
FROM SetupEntityAccess
WHERE SetupEntityID IN (SELECT Id 
						FROM ApexClass 
                        WHERE Name = 'MyGreatApexClass')
```

##### Custom Tab Settings

<p class="callout info">Pro-tip: You can update the settings from here with "Default On" or "Default Off". Deleting the row will make it "Hidden".</p>

[Tooling API Documentation](https://developer.salesforce.com/docs/atlas.en-us.api_tooling.meta/api_tooling/tooling_api_objects_permissionsettabsetting.htm)

Name is prepended with "standard-" for standard objects. Name is the API name of a custom object.

```
SELECT Parent.Name, Parent.Profile.Name, Visibility, Name
FROM PermissionSetTabSetting
WHERE Name = 'standard-Account'
```

##### Profile Page Layout Settings

<p class="callout info">The below are using the tooling API.</p>

[Tooling API Documentation](https://developer.salesforce.com/docs/atlas.en-us.api_tooling.meta/api_tooling/tooling_api_objects_profilelayout.htm)

##### Standard Objects

```
SELECT Layout.Name, TableEnumOrId, Profile.Name, RecordType.Name
FROM ProfileLayout
WHERE TableEnumOrId = 'Account'
```

##### Custom Objects

First, retrieve the "Durable ID" of the object:

```
SELECT DurableId 
FROM EntityDefinition 
WHERE QualifiedAPIName = 'Account_Retention_Rate__c'
```

Then, query the Page Layout Settings:

```
SELECT Layout.Name, TableEnumOrId, Profile.Name, RecordType.Name
FROM ProfileLayout
WHERE TableEnumOrId = '01Io0000001KyaB'
```

<div data-row="0" id="bkmrk-"></div><div data-row="1" id="bkmrk--0"></div><div data-row="2" id="bkmrk--1"></div><div data-row="3" id="bkmrk--2"></div><div data-row="4" id="bkmrk--3"></div><div data-row="5" id="bkmrk--4"></div><div data-row="6" id="bkmrk--5"></div><div data-row="7" id="bkmrk--6"></div><div data-column="0" id="bkmrk--7"></div><div data-column="1" id="bkmrk--8"></div><div data-column="2" id="bkmrk--9"></div>

# License Management

#### Who has what Salesforce License?

```
SELECT Id, Name, UserName, Profile.Name, Profile.UserLicense.MasterLabel
FROM User
WHERE IsActive = true
```

#### How Many Licenses do I have?

```
SELECT MasterLabel,MonthlyLoginsEntitlement,MonthlyLoginsUsed,Name,Status,TotalLicenses,UsedLicenses,UsedLicensesLastUpdated 
FROM UserLicense
```

#### How Many Managed Package Licenses do I have?

```
SELECT NamespacePrefix,Status,UsedLicenses,AllowedLicenses,ExpirationDate 
FROM PackageLicense
```

##### Who has what Managed Package License?

```
SELECT UserId, PackageLicense.NamespacePrefix, CreatedBy.Name,CreatedDate 
FROM UserPackageLicense
```

<p class="callout info">You must download this information and then do excel VLOOKUP or INDEX(MATCHES()) formula to join the data in excel.</p>

#### How many Permission Set Licenses do I have?

```
SELECT MasterLabel, DeveloperName, ExpirationDate,Status,TotalLicenses,UsedLicenses 
FROM PermissionSetLicense
```

#### Who has what Permission Set License?

```
SELECT Id, Assignee.Name, Assignee.UserName, PermissionSetLicense.MasterLabel
FROM PermissionSetLicenseAssign
WHERE Assignee.IsActive = true
```

# Approval Processes

#### Housekeeping

Here is the [ERD](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_erd_process.htm) for the Approval Process Objects. Typically reporting will be done from the Process Instance Step =&gt; Process Instance =&gt; Process Definition or from the Process Instance =&gt; Process Definition.

#### Process Instance Steps

Process Steps show any pending, completed, or recalled approval process steps.

[SOAP API Developer Documentation](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_processinstancestep.htm?search_text=processstep)

##### Retrieve all Process Steps that are Pending Approval

```
SELECT Id, ProcessInstance.TargetObjectId, CreatedBy.Name, Actor.Name, OriginalActor.Name, ProcessInstance.Status, ProcessInstance.TargetObject.Type, ElapsedTimeInDays, ElapsedTimeInHours, ElapsedTimeInMinutes
FROM ProcessInstanceWorkitem
WHERE ProcessInstance.Status IN ('Pending', 'Started')
```

##### Retrieve all Process Steps that are Pending Approval and apply to a specific object

```
SELECT Id, ProcessInstance.TargetObjectId, CreatedBy.Name, Actor.Name, OriginalActor.Name, ProcessInstance.Status, ProcessInstance.TargetObject.Type, ElapsedTimeInDays, ElapsedTimeInHours, ElapsedTimeInMinutes
FROM ProcessInstanceWorkitem
WHERE ProcessInstance.Status IN ('Pending', 'Started') AND ProcessInstance.TargetObject.Type = 'Opportunity'
```

#### Getting Metrics on Approval Process Completion

[Process Instance SOAP API Developer Documentation](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_processinstance.htm)

[Process Definition SOAP API Developer Documentation](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_processdefinition.htm)

```
SELECT ProcessDefinition.Name,SUM(ElapsedTimeInDays),SUM(ElapsedTimeInHours),SUM(ElapsedTimeInMinutes),COUNT(Id),Status,TargetObject.Type
FROM ProcessInstance
WHERE ProcessDefinition.State = 'Active' AND Status IN ('Approved','Rejected')
GROUP BY ProcessDefinitionId, ProcessDefinition.Name,Status,TargetObject.Type
```

# Content Documents

#### Housekeeping

[ERD for Content Documents](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_erd_content.htm)

When a Content Document is created, a content version record is created. Each update gets stored as a version and the content document LatestPublishedVersionId is updated. When linking a content document with a record, a ContentDocumentLink is created. A Content Document can be associated with multiple records in the system.

ContentNotes are *only* notes which are stored as well in the ContentDocument object. You can filter ContentNotes using FileType = 'SNOTE' in the ContentDocument object.

ContentDocumentLinks *must* be filtered by either a single ContentDocumentId or LinkedEntityId(s). If you do not, you will receive this error:

"Implementation restriction: ContentDocumentLink requires a filter by a single Id on ContentDocumentId or LinkedEntityId using the equals operator or multiple Id's using the IN operator."

#### Querying for ContentDocuments Linked to Certain Kinds of Records

```
SELECT Id, ContentDocumentId, ContentDocument.Title, ContentDocument.Description, ContentDocument.FileExtension, ContentDocument.FileType, ContentDocument.LastViewedDate, ContentDocument.LastReferencedDate, ContentDocument.PublishStatus, ContentDocument.SharingOption, ContentDocument.SharingPrivacy
FROM ContentDocumentLink 
WHERE LinkedEntityId IN (SELECT Id 
						 FROM Opportunity 
                         WHERE Name = 'My Amazing Opportunity')
```

#### Query for Count of Linked Entity Object Types

```
SELECT COUNT(Id), LinkedEntity.Type
FROM ContentDocumentLink
WHERE ContentDocumentId = '0695c000009ycrxAAA'
GROUP BY LinkedEntity.Type
```

#### Query for All Linked Entities for a Content Document

```
SELECT LinkedEntityId, LinkedEntity.Type
FROM ContentDocumentLink
WHERE ContentDocumentId = '0695c000009ycrxAAA'
```

# Querying Flows and Mass Deleting Old Versions

#### A Warning in Advance

Via the tooling api, we're going to delete some historic flow versions, this is super useful when you have reached the short limit of 50. However, you can lose useful history that past you might have wanted to save.

#### Accessing the Tooling API

You can access the tooling API via the [Salesforce Inspector chome extension](https://chrome.google.com/webstore/detail/salesforce-inspector/aodjmnfhjibkcdimpodiifdjnnncaafh), there is a checkbox at the top which points you at the Tooling API:

[![Screenshot 2022-04-13 at 16.24.31.png](https://wiki.sfxd.org/uploads/images/gallery/2022-04/scaled-1680-/screenshot-2022-04-13-at-16-24-31.png)](https://wiki.sfxd.org/uploads/images/gallery/2022-04/screenshot-2022-04-13-at-16-24-31.png)

You can also access the Tooling API via the developer console:

[![Screenshot 2022-04-13 at 16.34.35.png](https://wiki.sfxd.org/uploads/images/gallery/2022-04/scaled-1680-/screenshot-2022-04-13-at-16-34-35.png)](https://wiki.sfxd.org/uploads/images/gallery/2022-04/screenshot-2022-04-13-at-16-34-35.png)

[Tooling API Developer Documentation](https://developer.salesforce.com/docs/atlas.en-us.api_tooling.meta/api_tooling/intro_api_tooling.htm "Tooling API Developer Documentation")

##### What versions could I remove? 

```
SELECT Id, VersionNumber,  Status, Description, MasterLabel, CreatedDate
FROM Flow 
WHERE MasterLabel = 'Case Updates' AND Status != 'Active' ORDER BY CreatedDate ASC limit 19 
```

Generally limit yourself to one flow at a time, for ease this example limits via the Masterlabel, as per the above image, you can see that it returns both drafts and obselete versions. Beware, you do not want to remove your current draft with changes in it!

##### Deleting versions

In Salesforce Inspector, you can simply copy the excel output from the query and paste that into the upload box of a data import window:

[![Screenshot 2022-04-13 at 16.35.52.png](https://wiki.sfxd.org/uploads/images/gallery/2022-04/scaled-1680-/screenshot-2022-04-13-at-16-35-52.png)](https://wiki.sfxd.org/uploads/images/gallery/2022-04/screenshot-2022-04-13-at-16-35-52.png)

Note the Tooling API checkbox at the top, if using the above query, you will need to skip all the other column headers, as the delete operation expects a list of Id's.

It doesn't appear that dev console can delete flows unfortunately.

<div data-row="0" id="bkmrk--2"></div><div data-row="1" id="bkmrk--3"></div><div data-row="2" id="bkmrk--4"></div><div data-row="3" id="bkmrk--5"></div><div data-row="4" id="bkmrk--6"></div><div data-row="5" id="bkmrk--7"></div><div data-row="6" id="bkmrk--8"></div><div data-row="7" id="bkmrk--9"></div><div data-column="0" id="bkmrk--10"></div><div data-column="1" id="bkmrk--11"></div><div data-column="2" id="bkmrk--12"></div>

# 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>