Working with this product, its evident that the inbuilt reports are not that useful for certain things.   I wanted to find a way of logging users who have used SecurID to authenticate against a number of hosts.

So time to learn the custom queries section of the product, me thinks.

The Server is (obviously) SQL based.  The schema is fairly easy to understand, especially if you look at the examples within the help text or provided on the server.

It is possible to import my SQL scripts by creating a new Custom Query then pasting in the SQL. So…. scripts:

Todays Unique User Count

SELECT COUNT(DISTINCT chUserName) 
FROM SDLogEntry 
WHERE dtLocalDate = GMTDateNow   
AND iMessageNum = 1011   
AND (CHClientName = "host1" OR CHClientName = "host2")

Remember to change the host1 and host2 entries to your device host names obviously.  If you have more then add more to the last AND

Message Number 1011 is a ‘Passcode Successful’ – meaning a successful authentication

Todays Successful Users

SELECT SDLogEntry.dtLocalDate, SDLogEntry.TLocalTOD, SDLogEntry.chUserName, SDLogEntry.chLogin, SDLogEntry.chClientName, SDLogMessage.chShortMessage
FROM SDLogEntry, SDLogMessage 
WHERE SDLogEntry.dtLocalDate = GMTDateNow   
AND SDLogEntry.iMessageNum = 1011   
AND SDLogMessage.iMessageNum = SDLogEntry.iMessageNum  
AND (SDLogEntry.CHClientName = "host1" OR SDLogEntry.CHClientName = "host2")

Again 1011 is a successful passcode.

This uses a select from multiple tables to get the actual description of the message number to make the report more readable.

Todays Failures

SELECT SDLogEntry.dtLocalDate, SDLogEntry.TLocalTOD, SDLogEntry.chUserName, SDLogEntry.chLogin, SDLogEntry.chClientName, SDLogMessage.chShortMessage
FROM SDLogEntry, SDLogMessage 
WHERE SDLogEntry.dtLocalDate = GMTDateNow   
AND SDLogEntry.iMessageNum <> 1011   
AND SDLogMessage.iMessageNum = SDLogEntry.iMessageNum  
AND (SDLogEntry.CHClientName = "host1" OR SDLogEntry.CHClientName = "host2")

This uses the same format as success but with a ‘not’ 1011.  The report can be useful to troubleshoot users having problems or who are dim 🙂

Using Arguments to Select Dates

All of the above are for a single day, which is which is specified by the ‘GMTDateNow’ value.  This can be changed to use Argument Selectors which you can build in the same screen as the SQL entry.  Thus an example of ‘Successful Users’ would be:

SELECT SDLogEntry.dtLocalDate, SDLogEntry.TLocalTOD, SDLogEntry.chUserName, SDLogEntry.chLogin, SDLogEntry.chClientName, SDLogMessage.chShortMessage
FROM SDLogEntry, SDLogMessage 
WHERE SDLogEntry.dtLocalDate > ARG01 AND SDLogEntry.dtLocalDate < ARG02
AND SDLogEntry.iMessageNum = 1011   
AND SDLogMessage.iMessageNum = SDLogEntry.iMessageNum  
AND (SDLogEntry.CHClientName = "host1" OR SDLogEntry.CHClientName = "host2")

Where ARG01 = Start Date and ARG02 = End Date

These can be applied to any of the reports.

Other Information

One thing I found is that the custom queries are stored locally on the machine that create the reports so to share them to others you have to use a fileshare, USB disk or similar method.

Also the HTML format reports aren’t to bad so its worth a look at using this output method.