CMS Query Builder - user by Client session type

[sharon_smith3]

Is there a way to query CMS to find Users by Client Session type?

For example, in the CMC>Sessions, it shows how a user is logged-in; via BI launch pad or Crystal Reports Services.(see below)

I'd like to know which of our users has logged-in with Crystal Reports Services and when.

Thank You.

[Joe_Peters · Accepted solution]

If you want a historical record of logons by client type, then you're much better off using Audit. That field is recorded in ads_event.client_type_id. The descriptive values are in ads_application_type_str. Here's a simple query that displays this value for all events in the past 24 hours (this is Oracle syntax):

SELECT
  ADS_CLIENT_TYPE_STR.APPLICATION_TYPE_NAME,
  ADS_EVENT.USER_NAME,
  ADS_EVENT.START_TIME
FROM
  ADS_EVENT INNER JOIN ADS_APPLICATION_TYPE_STR  ADS_CLIENT_TYPE_STR 
    ON (ADS_CLIENT_TYPE_STR.APPLICATION_TYPE_ID=ADS_EVENT.CLIENT_TYPE_ID  
    AND  ADS_CLIENT_TYPE_STR.LANGUAGE='EN')
WHERE
  ADS_EVENT.START_TIME >= sysdate - 1

If you want live connections, then you can get it with a CMS query, but it's more work. Start with this:

select top 10000 si_name,si_id,si_cuid,SI_LOGON_CLIENT_TYPE from ci_systemobjects where si_kind = 'connection'
and SI_FAILOVER_AVAILABLE_UNTIL is null
and si_name != 'system account'
and SI_AUTHEN_METHOD != 'server-token'

The query result will match what you see in the Sessions tab in CMC.

The si_logon_client_type field is a CUID that kind of maps to the Client Session. Look up that CUID in ci_appobjects and its si_name will indicate the client type.

[sharon_smith3 · Accepted solution]

Thank You Joe Peters. The second query works. I will try the first query.

Thanks again.