(Delinea) 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

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

References

• Built-in Reports