ReportServer Forum

quintg

Member

Registered: 2016-10-19

Internal database data model

I want to create some auditing reports. I know the RS_AUDIT_* tables in the internal database and want to join them with other tables. Is the full data model of this internal database published somewhere? I can't find it.

karolina

Member

Registered: 2014-08-09

Re: Internal database data model

Hi,

To explore the model, just use any relational database management software that fits the database and your needs.

To help you get started:
1. see this post about the
10 recently used reports displayed on dashboard

2. the query for all log-ins and log-outs:

SELECT 
  rs_user.username AS username,
  rs_audit_log_entry.action AS action,
  rs_audit_log_entry.date_field AS datetime
FROM
  rs_audit_log_entry
  INNER JOIN rs_user ON (rs_audit_log_entry.user_id = rs_user.id)
WHERE
  rs_audit_log_entry.action LIKE 'LOGIN' OR 
  rs_audit_log_entry.action LIKE 'LOGOUT'
ORDER BY
  datetime DESC

Note: if a user just closes a browser window without explicitly logging out, there may be no entry in the database for this event.

Karolina