Livel Report Needed

[markorjj]

I'm trying to put together some sort of a LiveReport I can use to trim our user base down to users that are actually using the system.

I'd like to put together a LiveReport that will spit out a list of all of the active users, with their login creation date, (if possible) and the last activity date for the account.

Is this possible? Is so, what would the report query look like (We are running 9.7.0 and Oracle right now).

 

[appnair]

list of all of the active users, with their login creation date,

unless daudinew is keeping track of user creation and date which I think it is ,the old way is to look at the personal workspace (subtype 142) creation.There are plenty of similar reports in the KB that may work staright for your case.BTW it all depends on what the definition of an active user(a user who has logged in at least once in past month,6 months)

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[brettsanders]

We have created a similar report. Try this (assuming you are using mssql). You will have one user input field (date)...which will be the starting point of your report.


SELECT MAX(dbo.DAuditNew.AuditDate) AS LastLoginDate, CAST(dbo.DAuditNew.Value2 AS char(50)) AS UserName, dbo.KUAF.FirstName, dbo.KUAF.LastName
FROM dbo.KUAF INNER JOIN
dbo.DAuditNew ON CAST(dbo.KUAF.Name AS char(50)) = CAST(dbo.DAuditNew.Value2 AS char(50))
WHERE (dbo.KUAF.Deleted = '0')
AND (dbo.KUAF.Type = '0')
AND (dbo.DAuditNew.AuditStr = N'login')
AND (dbo.DAuditNew.AuditDate > %1)
GROUP BY CAST(dbo.DAuditNew.Value2 AS char(50)), dbo.KUAF.FirstName, dbo.KUAF.LastName
ORDER BY LastLoginDate DESC

 

[markorjj]

I tried changing the previous script as follows to run under ORACLE:

SELECT MAX(DAuditNew.AuditDate) AS LastLoginDate, CAST(DAuditNew.Value2 AS char(50)) AS UserName, KUAF.FirstName, KUAF.LastName
FROM KUAF INNER JOIN DAuditNew
ON CAST(KUAF.Name AS char(50)) = CAST(DAuditNew.Value2
AS char(50))WHERE (KUAF.Deleted = '0') AND (KUAF.Type = '0')
AND (DAuditNew.AuditStr = 'login')
AND (DAuditNew.AuditDate > to_date('31-DEC-2000'))
GROUP BY CAST(DAuditNew.Value2 AS char(50)), KUAF.FirstName, KUAF.LastName
ORDER BY LastLoginDate DESC

The script runs, but absolutely no values are obtained, indicating a imporperly stated script - I tried the instance where I asked in a live report what date to put in and got the same result - thinking that maybe the date format was wrong I tried har-coding a date in that was less than the earliest date in out DAuditNew table and got the same result (that is the script you see here).

 

[appnair]

is it just because you are not putting the right string stuff here

(DAuditNew.AuditStr = 'login'

I would try upper or lower to rule that out

(lower(DAuditNew.AuditStr) = 'login'
I belive the string it puts there in the DB is 'Login'

Not sure if that is what you are missing.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[appnair]

Oh I am sorry I did not read your post completely I would check if cast etc are sqlserver specific

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[markorjj]

Duhhh! Me Bad!

Looking for a forest and all I saw were those dammed trees - of course it wouldn't find anything like 'login' when the DB stores it as 'Login'.

Arggh! Thanks for the advice - everything works now when I change it to 'Login'.

 

[appnair]

that is one of the first things in string comparison that we are taught to do but we sometimes forget it has happened to me as well so I spotted it

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair