What SQL statements are used to get SiteMinder audit Reports?

Products

• CA Single Sign On Secure Proxy Server (SiteMinder)
• CA Single Sign On SOA Security Manager (SiteMinder)
• CA Single Sign-On

Issue/Introduction

Description

Customer is required to get audit data from the SQL database but does not want to use the reports server. Do you have some template or SQL that can help me to generate audit reports?

Solution

You can use the following queries to generate audit reports via SQL.
Please note that the following queries are example and they are not supported by SiteMinder Support.

Activity Report

SELECT
    SM_TIMESTAMP,
    SM_CATEGORYID,
    SM_EVENTID,
    SM_USERNAME,
    SM_AGENTNAME,
    SM_REALMNAME,
    SM_CLIENTIP,
    SM_DOMAINOID,
    SM_RESOURCE,
    SM_STATUS
FROM
    smaccesslog4
WHERE
    SM_TIMESTAMP >= {START DATE PARAMETER} AND
    SM_TIMESTAMP <= {END DATE PARAMETER} AND
    SM_USERNAME = {USER NAME PARAMETER} AND
    SM_AGENTNAME = {AGENT NAME PARAMTER} AND
    SM_DOMAINOID = {DOMAIN ID PARAMETER}
 

User Activity Report

SELECT
    SM_TIMESTAMP,
    SM_CATEGORYID,
    SM_EVENTID,
    SM_SESSIONID,
    SM_USERNAME,
    SM_AGENTNAME,
    SM_REALMNAME,
    SM_CLIENTIP,
    SM_DOMAINOID,
    SM_RESOURCE,
    SM_STATUS
FROM
    smaccesslog4      
WHERE
    SM_TIMESTAMP >= {START DATE PARAMETER} AND
    SM_TIMESTAMP <= {END DATE PARAMETER} AND
    SM_USERNAME = {USER NAME PARAMETER} AND
    SM_AGENTNAME = {AGENT NAME PARAMTER} AND
    SM_DOMAINOID = {DOMAIN ID PARAMETER}
 

Agent Activity Report

SELECT
    SM_TIMESTAMP,
    SM_CATEGORYID,
    SM_EVENTID,
    SM_USERNAME,
    SM_AGENTNAME,
    SM_REALMNAME,
    SM_CLIENTIP,
    SM_DOMAINOID,
    SM_RESOURCE,
    SM_STATUS
FROM
    smaccesslog4 
WHERE
    SM_TIMESTAMP >= {START DATE PARAMETER} AND
    SM_TIMESTAMP <= {END DATE PARAMETER} AND
    SM_USERNAME = {USER NAME PARAMETER} AND
    SM_AGENTNAME = {AGENT NAME PARAMTER} AND
    SM_DOMAINOID = {DOMAIN ID PARAMETER} AND
    SM_CATEGORYID" < 3
 

Resource Activity Report

SELECT
    SM_TIMESTAMP,
    SM_CATEGORYID,
    SM_EVENTID,
    SM_HOSTNAME,
    SM_USERNAME,
    SM_AGENTNAME,
    SM_REALMNAME,
    SM_CLIENTIP,
    SM_DOMAINOID,
    SM_RESOURCE,
    SM_STATUS
FROM
    smaccesslog4 
WHERE
    SM_TIMESTAMP >= {START DATE PARAMETER} AND
    SM_TIMESTAMP <= {END DATE PARAMETER} AND
    SM_USERNAME = {USER NAME PARAMETER} AND
    SM_AGENTNAME = {AGENT NAME PARAMTER} AND
    SM_DOMAINOID = {DOMAIN ID PARAMETER} AND
    SM_CATEGORYID < 3
 

Intrusion Report

SELECT
    SM_TIMESTAMP,
    SM_CATEGORYID,
    SM_EVENTID,
    SM_USERNAME,
    SM_AGENTNAME,
    SM_REALMNAME,
    SM_CLIENTIP,
    SM_DOMAINOID,
    SM_RESOURCE,
    SM_STATUS
FROM
    smaccesslog4 
WHERE
    SM_TIMESTAMP >= {START DATE PARAMETER} AND
    SM_TIMESTAMP <= {END DATE PARAMETER} AND
    SM_USERNAME = {USER NAME PARAMETER} AND
    SM_AGENTNAME = {AGENT NAME PARAMTER} AND
    SM_DOMAINOID = {DOMAIN ID PARAMETER} AND
    SM_EVENTID IN (2,3,6,9)
 

Intrusion by User Report

SELECT
    SM_TIMESTAMP,
    SM_CATEGORYID,
    SM_EVENTID,
    SM_USERNAME,
    SM_AGENTNAME,
    SM_REALMNAME,
    SM_CLIENTIP,
    SM_DOMAINOID,
    SM_RESOURCE,
    SM_STATUS
FROM
    smaccesslog4 
WHERE
    SM_TIMESTAMP >= {START DATE PARAMETER} AND
    SM_TIMESTAMP <= {END DATE PARAMETER} AND
    SM_USERNAME = {USER NAME PARAMETER} AND
    SM_AGENTNAME = {AGENT NAME PARAMTER} AND
    SM_DOMAINOID = {DOMAIN ID PARAMETER} AND
    SM_EVENTID IN (2,3,6,9)
 

Intrusion by Agent Report

SELECT
    SM_TIMESTAMP,
    SM_CATEGORYID,
    SM_EVENTID,
    SM_USERNAME,
    SM_AGENTNAME,
    SM_REALMNAME,
    SM_CLIENTIP,
    SM_DOMAINOID,
    SM_RESOURCE,
    SM_STATUS
FROM
    smaccesslog4 
WHERE
    SM_TIMESTAMP >= {START DATE PARAMETER} AND
    SM_TIMESTAMP <= {END DATE PARAMETER} AND
    SM_USERNAME = {USER NAME PARAMETER} AND
    SM_AGENTNAME = {AGENT NAME PARAMTER} AND
    SM_DOMAINOID = {DOMAIN ID PARAMETER} AND
    SM_EVENTID IN (2,3,6,9)
 

Administrative Activity Report (Only valid in version 6)

SELECT
    SM_TIMESTAMP,
    SM_CATEGORYID,
    SM_EVENTID,
    SM_SESSIONID,
    SM_USERNAME,
    SM_OBJNAME,
    SM_FIELDDESC
FROM
    smobjlog4 
WHERE
    SM_TIMESTAMP >= {START DATE PARAMETER} AND
    SM_TIMESTAMP <= {END DATE PARAMETER} AND
    SM_USERNAME = {USER NAME PARAMETER}
 

Object Activity Report (Only valid in version 6)

SELECT
    SM_TIMESTAMP,
    SM_CATEGORYID,
    SM_EVENTID,
    SM_SESSIONID,
    SM_USERNAME,
    SM_OBJNAME,
    SM_FIELDDESC
FROM
    smobjlog4 
WHERE
    SM_TIMESTAMP >= {START DATE PARAMETER} AND
    SM_TIMESTAMP <= {END DATE PARAMETER} AND
    SM_USERNAME = {USER NAME PARAMETER} AND
    SM_CATEGORYID > 1
 

Administrative Activity by Administrator Report (Only valid in version 6)

SELECT
    SM_TIMESTAMP,
    SM_CATEGORYID,
    SM_EVENTID,
    SM_SESSIONID,
    SM_USERNAME,
    SM_OBJNAME,
    SM_FIELDDESC
FROM
    smobjlog4 
WHERE
    SM_TIMESTAMP >= {START DATE PARAMETER} AND
    SM_TIMESTAMP <= {END DATE PARAMETER} AND
    SM_USERNAME = {USER NAME PARAMETER}

Environment

Release:
Component: SMPLC