Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Datasource to monitor user logon

Highlighted
b460
Regular Collector

Datasource to monitor user logon

Hi,

We have created some SQL used within a Data Source to create a portlet to tell us how many times a person has logged into PPM.

I have tried to add some filter fields in but cannot get it to work. I cant seem to get the SQL right.

I would like to add filter fields that will allow me to filter between 2 date ranges (when they logged in) and also by the department the user is in within the Org Model (Resource Management).

If you think you know how to add this SQL in please let me know, what we have at the moment is below:

SELECT del.last_logon_time LAST_LOGON_TIME, del.logon_times LOGON_TIMES, ku.full_name FULLNAME
FROM (SELECT max(creation_date) LAST_LOGON_TIME,
count(username) LOGON_TIMES,
username USERNAME
FROM knta_logon_attempts
WHERE success_flag = 'Y'
GROUP BY USERNAME ) del,
knta_users ku
WHERE del.username = ku.username
ORDER BY del.logon_times desc
8 REPLIES
Erik Cole
Honored Contributor

Re: Datasource to monitor user logon

How about something like

SELECT del.last_logon_time LAST_LOGON_TIME, del.logon_times LOGON_TIMES, ku.full_name FULLNAME,oum.ORG_UNIT_NAME
FROM (SELECT max(creation_date) LAST_LOGON_TIME,
count(username) LOGON_TIMES,
username USERNAME
FROM knta_logon_attempts
WHERE success_flag = 'Y'
GROUP BY USERNAME ) del,
knta_users ku,
KRSC_ORG_UNIT_MEMBERS_V oum
WHERE del.username = ku.username
and oum.USERNAME = del.USERNAME and oum.ENABLED_FLAG = 'Y'
/*
-- Filters from Data Source
and del.last_logon_time >= to_date([P.START_DATE.TO_STRING],'YYYY-MM-DD HH24:MI:SS')
and del.last_logon_time <= to_date([P.END_DATE.TO_STRING],'YYYY-MM-DD HH24:MI:SS')
and oum.ORG_UNIT_ID = [P.ORG_UNIT]
*/
ORDER BY del.logon_times desc
b460
Regular Collector

Re: Datasource to monitor user logon

Thnaks for that!

Could I ask though, you use the table KRSC_ORG_UNIT_MEMBERS_V. I had a look in the Data Model Guide and it is not listed there. Is this table some sort of special table as it has "_V" fixed to the end?
b460
Regular Collector

Re: Datasource to monitor user logon

my bad, its a view!
b460
Regular Collector

Re: Datasource to monitor user logon

Just implemented it as a data source and bar chart portlet definition and it does not seem to work. When I click preview the portlet times out.

However if I run the query in SQL runner it works fine?
Mahen M
Honored Contributor

Re: Datasource to monitor user logon

Increase the Time-out in Data Source to something like 300 Seconds or higher. It will work.

Regards,
Mahendran M
b460
Regular Collector

Re: Datasource to monitor user logon

Ok got it to work, Erik, the filter for the dates do not work.

In your code eg.

del.last_logon_time >= to_date([P.START_DATE.TO_STRING],'YYYY-MM-DD HH24:MI:SS')

"del.last_login_time" retrieves only the max date.

Any ideas around this?
Mahen M
Honored Contributor

Re: Datasource to monitor user logon

See if this helps.

and (WT1.SCHED_START_DATE between
to_date('[P.START_DATE]', 'yyyy-mm-dd hh24:mi:ss') and
to_date('[P.END_DATE]', 'yyyy-mm-dd hh24:mi:ss')
OR
WT1.SCHED_FINISH_DATE between
to_date('[P.START_DATE]', 'yyyy-mm-dd hh24:mi:ss') and
to_date('[P.END_DATE]', 'yyyy-mm-dd hh24:mi:ss'))

Regards,
Mahendran M
Erik Cole
Honored Contributor

Re: Datasource to monitor user logon

Well it filters only on the max date because that's what your inline view is sending to your main query.

I suppose if you are intending to filter out the results of your inline view (so you get a count(username) for the selected date range) instead of the main query, and you are always going to require the user to select a date range, you could move the filters into the view. Or you could just re-write the query to get rid of the inline view entirely - it's not really needed.
//Add this to "OnDomLoad" event