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.
Recent Comments