Secret Server is a brilliant Privileged Access Management (PAM) product. I have being doing some work recently on its custom reports – it’s great that you have the flexibility to create your own custom reports without needing to resort to Professional Services, so I thought would be worth sharing some of my observations on the subject:
- As the documentation says, a good starting point for creating custom reports is to look at the SQL scripts behind the ‘out of the box’ reports and then modify them to meet your own specific requirements.
2. To investigate the data available for reporting, if you are running the ‘on premise’ Secret Server and have access to the database the best thing to do is to use SQL Server Management Studio to view the tables and the data stored within them:
Otherwise you can go into the report editor and see what tables exist and see what columns exist within them:
3. It is also useful to know that if you have access to the database you can test running queries within the SQL Server Management Studio:
4. If there is a problem with the SQL query you sometimes get a meaningful error message from the SQL engine (such as
The following SQL Exception occurred… Incorrect syntax near the keyword 'SELECT'). But Secret Server seems to first run a pre-processor to perform its own validation of the query, and if that fails it just returns a bland response
The SQL that was entered is not valid for reporting:
I have even had the unhelpful
not valid for reporting error message when I copied the SQL from the standard ‘Secret Activity’ report into a new report. Why? Surely this SQL should be valid! The problem appears to be the
AS [User] statement (on line 3). If you change it to
AS [Username] then it works fine. It seems that the column header name needs to be anything other than
One other gotcha: make sure that you don’t have a
; anywhere in the query.
5. You can’t create temporary tables and the reports need to be constructed from a single SQL query (which means that you may end up using multiple subqueries, etc). I’m sure that this is heaven for the DBAs but it can be challenging for us lesser mortals..!