Thycotic Secret Server Report Script Collection

 This post is to summarize the reports from TSS. 

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:

Note: https://docs.thycotic.com/ss/11.1.0/reports/built-in-reports/index.md

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.

Built-in Security Hardening Reports

The Security Hardening Report checks aspects of SS to ensure security best practices are being implemented. While SS runs with all the items failing, administrators should be aware of possible security issues within an installation. For details on this, see Reports Security Hardening Tab.

Local Account Discovery Report

The User Audit Report shows all secrets accessed by a user during a specified period.

Custom Report Download

31 Custom report available for downloading: https://my.thycotic.com/products_secretserver_customreportgallery.html

Name	Description	Download	Published	Uploaded By
Which users have accessed Secrets that they did not create?	Shows a log of all actions done to Secrets by users who did not create the secret. This report is useful if you expect most users to be primarily using their own Secrets.	237	7/7/2010	David A
Which Secrets are expired?	Shows all Secrets that have expired. This is useful for showing which passwords should be changed.	255	7/7/2010	David A
Which Secrets will expire by a certain date?	Shows which Secrets will expire by a given date. This is useful for finding out which Secrets will soon expire.	104	7/7/2010	David A
Which Secrets expire in a certain date range?	This is useful for finding out which Secrets will expire in a certain date range in the future.	65	7/7/2010	David A
Which Secrets are out of sync?	Shows which Secrets have the incorrect password stored in Secret Server.	170	7/7/2010	David A
Which users have two factor authentication enabled?	Shows which users have two factor enabled and what form of two factor they are using. This is useful for security hardening purposes.	348	7/7/2010	David A
Which remote password changing secrets will never expire?	Shows all remote password changing secrets that will never expire. This is useful for assessing vulnerability on network passwords that may never change.	73	7/7/2010	David A
Which remote password changing secrets are set to expire but do not have autochange enabled?	Shows the secrets that can have their passwords remotely changed, but are not set to change on expiration.	145	7/7/2010	David A
Which Secrets have been accessed by users who do not currently have view permissions on the Secret?	Shows all Secrets that have been accessed in the past where the users currently do not have view permissions. This is useful for assessing vulnerabilities for passwords that should be changed.	140	7/7/2010	David A
Which secrets are DoubleLocked?	Shows a list of all secrets that are DoubleLocked. This allows admins to see if they have forgotten to DoubleLock any sensitive secrets.	53	7/7/2010	David A
Which secrets have Check Out enabled?	This report shows which Secrets have Check Out enabled. It also shows which Secrets are checked out.	124	7/7/2010	David A
Which Secrets Require Approval to Access?	This report will indicate which Secrets require users to request access to view.	66	8/14/2014	Tucker
What Folders Does User Have Owner?	Displays all folders the selected user has owner on.	112	7/29/2011	Tucker
What Folder Permissions Exists For Folders the User Has Owner?		135	7/29/2011	Tucker
Secret Activity by Owner	Shows the Secret Activity for all Secrets where the selected user is an Owner.	177	9/20/2011	Jacob
Password Compliance Distribution	Shows the distribution of passwords that are compliant and non-compliant with their Password Requirements.	127	12/5/2011	Jonathan Cogley
Show Secrets with Duplicate Secret Names	Shows active Secrets that have the same Secret Name (possible duplicates?).	321	12/6/2011	Jonathan Cogley
Event Subscription Log	Shows all the events that each user has received	33	1/24/2012	Jonathan Cogley
Which folders are not inheriting permissions?		59	1/25/2012	Jonathan Cogley
Ran out of user licenses	Shows a message when we have run out of user licenses in the last 7 days.	85	1/26/2012	Jonathan Cogley
What folders can groups see?	Shows all the folders visible by group	100	1/27/2012	Jonathan Cogley
Which folders are not inheriting permissions? (showing permissions)	Shows folders that are not inheriting permissions and who has access to them	71	1/30/2012	Jonathan Cogley
What Secrets can a user not see?	Shows all the Secrets that a user cannot access.	77	1/30/2012	WebUploadUser
Show Owners of all Secrets	Shows all the owners of Secrets in Secret Server.	262	2/7/2012	Jonathan Cogley
Secrets that do not inherit permissions	Shows Secrets that have explicit permissions set on them	292	4/20/2012	Jonathan Cogley
What Secrets Have Dependencies	Shows Secrets with Dependencies and the current status of those Dependencies.	102	7/31/2012	Ben Yoder
Secret Activity by Group	Shows Secret activity by Group	138	8/14/2014	WebUploadUser
Secrets Exported	Shows Secrets Exported through the administrator clear text export by date range.	653	10/4/2013	WebUploadUser
What errors occured for Computers in Active Directory that no longer exist?	Shows computers that have not connected to Active Directory 3 months prior to the selected date and the most recent error message from the scan.	72	7/1/2014	Ben Yoder
Which Secrets does no one have access to?	This can be useful in the case that you need to retrieve Secrets that currently have no permissions assigned because they were only accessed by a user who is now disabled	121	8/14/2014	WebUploadUser
Secrets in middle of password change	This could tell you which secrets are possibly stuck in the middle of a password change	96	3/28/2017	WebUploadUser

Local Account Discovery Report

SELECT 
	c.ComputerName AS 'Host', 
	ca.AccountName AS 'Account_Name',
	ST.ScanItemTemplateName AS 'Account_Type',
	c.ComputerVersion AS 'Operating_System',
	CASE 
		WHEN ca.PasswordLastSet IS NULL then 'Never'
		ELSE CONVERT(nvarchar,ca.PasswordLastSet)
	END AS 'Password Last Set',
	CASE
		WHEN ca.ScanItemTemplateId =13 and ca.IsLocalAdministrator = 1 THEN 'Built-in Administrator'
		WHEN ca.ScanItemTemplateId =13 and ca.IsLocalAdministrator = 0 THEN 'Standard_User'
	END AS 'Account Privilege',
	CASE 
		WHEN ca.ScanItemTemplateId =13 and ca.HasLocalAdminRights = 1 THEN 'Yes'
		WHEN ca.ScanItemTemplateId =13 and ca.HasLocalAdminRights = 0 THEN 'No'
	END AS 'Has Local Admin Rights',
	ou.Path 'Organizational Unit'
FROM 
		tbComputer c
	JOIN 	tbComputerAccount ca 

	ON 
		ca.ComputerID = c.ComputerId

	JOIN tbOrganizationUnit OU

	ON c.OrganizationUnitId = ou.OrganizationUnitId
	JOIN tbScanItemTemplate ST
	on ca.ScanItemTemplateId = ST.ScanItemTemplateId

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

Filter Discover Report

Show a report to see all unmanaged accounts:

/*  Domain accounts discovered in Secret Server that are not managed in Secret Server  */

/*  To filter the results to only a specific OU, uncomment out the
AND ou.Path = 'SpecificOU\SpecificOU'
line and change SpecificOU\SpecificOU to the folder path for the OU to filter  */

/*  To include a specific OU and its sub-OUs, uncomment out the AND ou.Path line
and edit it to
AND ou.Path CONTAINS 'SpecificOU\SpecificOU'
and change SpecificOU\SpecificOU to the folder path for the OU to filter  */

SELECT
    isnull(Domain,ds.Name) AS 'Discovery Source / Domain'
    ,ou.Path
    ,ca.AccountName AS 'Account Name'
FROM tbComputerAccount ca
    INNER JOIN tbDiscoverySource ds on ca.DiscoverySourceId = ds.DiscoverySourceId
    LEFT JOIN tbDomain d ON d.DomainId = ds.DomainId
    LEFT JOIN tbOrganizationUnit ou ON ou.OrganizationUnitId = ca.OrganizationUnitId
    LEFT JOIN tbSecret s ON s.ComputerAccountId = ca.ComputerAccountId
WHERE ds.Active = 1
    AND ((d.EnableDiscovery is null) OR (d.EnableDiscovery = 1))
    AND s.ComputerAccountId IS NULL
    AND ca.OrganizationUnitId IS NOT NULL
/*    AND ou.Path = 'SpecificOU\SpecificOU'  */
GROUP BY isnull(Domain,ds.Name), ou.Path, ca.AccountName
    HAVING COUNT(ca.AccountName) > 0
ORDER BY
    1,2,3 ASC

SELECT
    tc.DiscoverySourceId AS 'DiscoverySourceId',
    tds.Name AS 'DiscoverySourceName',
    tca.AccountName AS 'Account',
    tc.ComputerName AS 'Host Name',
    CONVERT(VARCHAR(20),tc.LastPolledDate,107) AS 'Last Scanned'
FROM
        tbComputer tc
        JOIN tbComputerAccount tca
        ON tc.ComputerId=tca.ComputerId
    JOIN tbDiscoverySource tds
    on tc.DiscoverySourceId=tds.DiscoverySourceId
    LEFT JOIN tbSecret ts
    ON ts.ComputerAccountId = tca.ComputerAccountId
    WHERE ts.ComputerAccountId IS NULL
    ORDER BY tca.AccountName asc

Find out secrets enabled Password Auto Change

SELECT         
	 Main.[Folder Path]
	,Main.SecretName AS [Secret Name]        
	,Main.[Secret Template]
	,Main.SecretId
	,LEFT(Main.Approvers,LEN(Main.Approvers)-1) As "Approvers"
	FROM(
		SELECT DISTINCT 
			s2.SecretId
			,s2.SecretName
			,ISNULL(f.FolderPath, N'No folder assigned') as [Folder Path]
			,st.SecretTypeName AS [Secret Template]
			,(SELECT DISTINCT
				vg.DisplayName + ', ' AS [text()]            
			FROM dbo.tbSecret s1
			JOIN tbSecretGroupApproval sga
				ON s1.SecretId = sga.SecretId
			JOIN vGroupDisplayName vg
				ON vg.GroupId = sga.GroupId                
			WHERE s1.SecretId = s2.SecretId
			FOR XML PATH ('')
		) [Approvers]
		FROM dbo.tbSecret s2
		LEFT JOIN tbFolder f WITH (NOLOCK)
			ON s2.FolderId = f.FolderId
		INNER JOIN tbSecretType st WITH (NOLOCK)
			ON s2.SecretTypeId = st.SecretTypeId
		WHERE s2.AutoChangeOnExpiration = 1 
		AND 	
			st.OrganizationId = #ORGANIZATION
		AND
			s2.Active = 1
	) [Main]
	ORDER BY [Folder Path], [Secret Name]

Listing all columns from table dbo.tbSecret

You can just choose certain columns to show or you can choose all columns from one table:

• Select SecretID,SecretName,FolderId from dbo.tbSecret

Select *
From dbo.tbSecret
where AutoChangeOnExpiration = 1

References

• Built-in Reports