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}