This post is to summarize the reports from TSS.
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:
- 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
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?
- What folders can a user see?
- What folders can all users see?
- What folder permissions exist?
- What folder permissions exist for groups?
- Group Membership
- Group Membership By Group
- Secret Server Usage
- Secret Expiration Health
- Secret Template Distribution
- Top Ten Viewers (Professional)
- What Secrets Do Not Meet Password Requirements?
- Secret Password Compliance Statuses
Report Schedules (Professional)
- What role permissions does a user have?
- What role assignments exist?
- What role permission assignments exist?
- 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?
- What Folders have Policies Assigned?
- What Secrets have different Policies than their folders?
- What Secrets have policies assigned?
- 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
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
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