Audit SQL statements used to get Audit Reports in Policy Server

Products

• CA Single Sign-On
• SITEMINDER

Issue/Introduction

 

How to get Audit data from the SQL database without using the reports server?

Is there some template or SQL that can help to generate Audit reports?

 

Resolution

 

Use the following queries to generate audit reports via SQL.

Note that the following queries are examples and they are out of support scope.

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}