{"id":107,"date":"2012-07-30T21:56:23","date_gmt":"2012-07-30T20:56:23","guid":{"rendered":"http:\/\/iddles.co.uk\/blogs\/?p=107"},"modified":"2012-07-30T21:56:23","modified_gmt":"2012-07-30T20:56:23","slug":"rsa-authentication-managercustom-reports","status":"publish","type":"post","link":"https:\/\/iddles.co.uk\/index.php\/2012\/07\/30\/rsa-authentication-managercustom-reports\/","title":{"rendered":"RSA Authentication Manager\u2013Custom Reports"},"content":{"rendered":"

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.<\/p>\n

So time to learn the custom queries section of the product, me thinks.<\/p>\n

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.<\/p>\n

It is possible to import my SQL scripts by creating a new Custom Query then pasting in the SQL. So\u2026. scripts:<\/p>\n

<\/h2>\n

<\/h2>\n

Todays Unique User Count<\/h2>\n
\n

SELECT COUNT(DISTINCT chUserName) 
FROM SDLogEntry 
WHERE dtLocalDate = GMTDateNow   
AND iMessageNum = 1011   
AND (CHClientName = "host1" OR CHClientName = "host2")<\/font><\/p>\n<\/blockquote>\n

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<\/p>\n

Message Number 1011 is a \u2018Passcode Successful\u2019 \u2013 meaning a successful authentication<\/p>\n

Todays Successful Users<\/h2>\n
\n

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")<\/font><\/p>\n<\/blockquote>\n

Again 1011 is a successful passcode.<\/p>\n

This uses a select from multiple tables to get the actual description of the message number to make the report more readable.<\/p>\n

Todays Failures<\/h2>\n
\n

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")<\/font><\/p>\n<\/blockquote>\n

This uses the same format as success but with a \u2018not\u2019 1011.  The report can be useful to troubleshoot users having problems or who are dim \ud83d\ude42<\/p>\n

Using Arguments to Select Dates<\/h2>\n

All of the above are for a single day, which is which is specified by the \u2018GMTDateNow\u2019 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 \u2018Successful Users\u2019 would be:<\/p>\n

\n

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")<\/font> <\/p>\n<\/blockquote>\n

Where ARG01 = Start Date and ARG02 = End Date<\/p>\n

These can be applied to any of the reports.<\/p>\n

Other Information<\/h2>\n

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.<\/p>\n

Also the HTML format reports aren’t to bad so its worth a look at using this output method.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[7,11],"tags":[47],"class_list":["post-107","post","type-post","status-publish","format-standard","hentry","category-networking","category-security","tag-rsa-aceserver-rsa-authentication-securid","post-preview"],"_links":{"self":[{"href":"https:\/\/iddles.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/iddles.co.uk\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/iddles.co.uk\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/iddles.co.uk\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/iddles.co.uk\/index.php\/wp-json\/wp\/v2\/comments?post=107"}],"version-history":[{"count":0,"href":"https:\/\/iddles.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/107\/revisions"}],"wp:attachment":[{"href":"https:\/\/iddles.co.uk\/index.php\/wp-json\/wp\/v2\/media?parent=107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/iddles.co.uk\/index.php\/wp-json\/wp\/v2\/categories?post=107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/iddles.co.uk\/index.php\/wp-json\/wp\/v2\/tags?post=107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}