Thycotic Secret Server Report Script Collection

Built-in Reports

Secret Server includes many pre-configured reports that you can run or use as templates for creating custom reports. Below are the reports shipped with current release of SS:

Activity

• Custom Report Activity
• Database Configuration Audit
• Distributed Engine Activity (Professional)
• Dual Control Audit
• Event Subscription Activity (Professional)
• Folder Activity
• Internal Communication Changes
• IP Address Range Audit
• License Audit
• Secret Activity
• Secret Activity Today
• Secret Activity Yesterday
• Secret Template Activity (Professional)
• Session Recording Errors
• Unlimited Administrator Behavior
• Users Activity

Discovery Scan

Note: These are available in Professional edition. In prior versions they are available only in Enterprise Plus.

• Discovery Scan Status
• What computers in Active Directory no longer exist?
• What computers have been successfully scanned?
• What computers that exist have not been successfully scanned?
• What Secrets failed to import by Discovery?
• What Secrets are pending import by Discovery?

Folders

• What folders can a user see?
• What folders can all users see?
• What folder permissions exist?
• What folder permissions exist for groups?

Groups

• Group Membership
• Group Membership By Group

Legacy Reports

• Secret Server Usage
• Secret Expiration Health
• Secret Template Distribution
• Top Ten Viewers (Professional)

Password Compliance

• What Secrets Do Not Meet Password Requirements?
• Secret Password Compliance Statuses

Report Schedules

Report Schedules (Professional)

Roles and Permissions

• What role permissions does a user have?
• What role assignments exist?
• What role permission assignments exist?

Secrets

• Secret Count per Site
• Secret Permissions Mismatch
• What file types have been uploaded to Secrets?
• What file types have been uploaded to Secrets? (Pie Chart)
• What Hooks and Dependencies use a script? (Enterprise Plus/Premium add-on)
• What Secret permissions exist for a group?
• What Secret permissions exist for a user?
• What Secret permissions exist?
• What Secrets are expiring this week?
• What Secrets can a user see?
• What Secrets can all users see?
• What Secrets changed passwords in the last 90 days?
• What Secrets Do Not Have Distributed Engines? (Professional)
• What Secrets don’t require approval? (Enterprise/Premium)
• What Secrets have been accessed by a user?
• What Secrets have been accessed by an impersonated user?
• What Secrets have been accessed?
• What Secrets have Distributed Engines?
• What Secrets have Expiration?
• What Secrets have failed Heartbeat? (Professional)
• What Secrets have not changed passwords for over 90 days?
• What Secrets require approval? (Enterprise/Premium)
• What Secrets require Comments?

Secret Policy

• What Folders have Policies Assigned?
• What Secrets have different Policies than their folders?
• What Secrets have policies assigned?

Users

• Failed login attempts
• Who hasn’t logged in within the last 90 days?
• What users have had an admin reset their password?
• Secret Template Permission by User

Note: You can find additional reports in the Custom Report Gallery.

Show All Secrets, Users, Folder Path and Last Access Date

Select distinct a.SecretId, SecretName, UserName, FolderPath, LastAccessedDate
from
(select SecretID, max(DateRecorded) as LastAccessedDate 
from tbAuditSecret
where Action like 'VIEW%'
group by SecretID) a
inner join tbSecret s on s.SecretID = a.SecretId
inner join tbFolder f on f.FolderID = s.FolderId
inner join tbAuditSecret au on au.SecretId = a.SecretId and LastAccessedDate = au.DateRecorded
inner join tbUser u on u.UserId = au.UserId
order by SecretId

Active Secret Sessions and Count

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 SELECT IsNull(f.FolderPath, 'No Folder') AS 'Folder Path', s.SecretId, SecretName, st.SecretTypeName AS [Secret Template], StartDate from tbSecretSession ss
INNER JOIN tbSecret s on s.SecretID = ss.SecretId
INNER JOIN tbSecretType st on s.SecretTypeID = st.SecretTypeID
LEFT JOIN tbFolder f on s.FolderID = f.FolderID
WHERE ss.Active = 1 and LaunchedSuccessfully = 1

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 SELECT count(1) as 'Session Count' from tbSecretSession ss
INNER JOIN tbSecret s on s.SecretID = ss.SecretId
WHERE ss.Active = 1 and LaunchedSuccessfully = 1

Service Accounts Discovery Report

a 

SELECT 
 CASE 
 WHEN ds.DomainId = '1' THEN 'EDITSQLTOPUTDOMAINHERE' -- Adjust for your domains 
 END AS 'Domain', 
 c.ComputerName AS 'Host Name', 
 c.ComputerVersion AS 'Operating System', 
 cd.AccountName AS 'Account Name', 
 cd.DependencyName AS 'Dependency Name', 
 sdt.SecretDependencyTypeName AS 'Dependency Type', 
 c.LastPolledDate AS 'Last Scanned', 
 s.SecretName AS 'Secret Name' 
FROM 
 tbComputer c 
 JOIN tbComputerDependency cd ON cd.ComputerID = c.ComputerId 
 JOIN tbSecretDependencyType sdt ON sdt.SecretDependencyTypeId = cd.SecretDependencyTypeID 
 JOIN tbSecretDependencyTemplate sdtm ON cd.ScanItemTemplateId = sdtm.ScanItemTemplateId 
 AND cd.SecretDependencyTypeID = sdtm.SecretDependencyTypeId 
 JOIN tbDiscoverySource ds ON c.DiscoverySourceId = ds.DiscoverySourceId 
 LEFT OUTER JOIN tbSecret s ON s.SecretID = cd.SecretId 
Where 
 cd.AccountName like '%' + #CUSTOMTEXT + '%' 
 AND -- Custom filters to remove non-important tasks 
 cd.DependencyName NOT like 'Optimize Start Menu Cache Files%' 
 AND 
 cd.DependencyName NOT like 'User_Feed_Synchronization%' 
 AND 
 cd.DependencyName NOT like 'CreateExplorerShellUnelevatedTask' 
ORDER BY c.ComputerName asc

References

• Built-in Reports