nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

A System Analysis LiveReport Question

Jim_Burke_(LMGTAdmin_(Delete)_2167536)

From one of the postings above and/or the LiveReport Sample Area, I grabbed a LR that looks at the Most Number of Logins in the Past Month.Most Number of Logins for the Past Month==================================Inputs: NoneSQL:SELECT kuaf.name AS "USERID",COUNT( daudit.event ) AS "LOGINS" FROM daudit, kuaf WHERE (daudit.auditdate >= %1 AND daudit.auditdate <= %2) AND daudit.event = 'LOGIN' AND daudit.userid = kuaf.id GROUP BY kuaf.name ORDER BY COUNT( daudit.event ) descParam %1 Last Month-StartParam %2 Last Month-End==================================What I'd really like to do is know how to take the data that is generated in this LR (above) and corrolate against the number of working days in the week or the month. The purpose being to illustrate that an aggregate number of people are using the system every business day. (i.e. "84% of our total users utilize Livelink 20 days of the month")I can adequately capture when people login. Unfortunately, 99% of the people simply kill their browser as their means of logging out. This means I can really determine how long a user users the system. What I'd like to show is that if the LR (above) reports that John Smith logged in 58 times last month, that at least 20 of those times matched working days (assuming 5 business days per week and 4 standard weeks per month).Maybe I'm not looking at the problem correctly either...so please give me some insight if you've got a different take on things.

Find more posts tagged with

Comments

Marita_Ventura_(DCAdmin_(Delete)_2237520)

The following should give you the no. of days each user logged in for a given period.SQL :-SELECT daudit.userid as "USERID",kuaf.name AS "NAME",COUNT( distinct TO_CHAR(DAUDIT.auditdate,'DD-MON-YY')) AS "DAYSLOGGED" FROM daudit, kuaf WHERE (daudit.auditdate >= %1 AND daudit.auditdate <= %2) AND daudit.event = 'LOGIN' AND daudit.userid = kuaf.id GROUP BY kuaf.name,daudit.userid ORDER BY ORDER BY DAYSLOGGED DESCParam %1 Period-StartParam %2 Period-EndHope this helps..Spratti

eLink User

Message from Marie Lindsay via eLinkClarification: Looks like this would give you the number of times a userlogged in during a period of days...> -----Original Message-----> From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf Of> eLink Discussion: Livelink LiveReports Discussion> Sent: Tuesday, August 07, 2001 10:23 AM> To: eLink Recipient> Subject: The following should give you the no. of days each user logged> in for a given...>>> The following should give you the no. of days each user logged in> for a given...> Posted by DCAdmin on 08/07/2001 11:19 AM>> The following should give you the no. of days each user logged in> for a given period.>> SQL :->> SELECT daudit.userid as "USERID",kuaf.name ASME",COUNT( > distinct TO_CHAR(DAUDIT.auditdate,'DD-MON-YY')) AS "DAYSLOGGED" > FROM daudit, kuaf > WHERE (daudit.auditdate >= %1 > AND daudit.auditdate <= %2) > AND daudit.event = 'LOGIN' > AND daudit.userid = kuaf.id > GROUP BY kuaf.name,daudit.userid > ORDER BY ORDER BY DAYSLOGGED DESC> > Param %1> Period-Start> > Param %2> Period-End> > Hope this helps..Spratti> > [To reply to this thread, use your normal e-mail reply function.]> > ============================================================> > Topic: A System Analysis LiveReport Question> https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2564283&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Marita_Ventura_(DCAdmin_(Delete)_2237520)

The count(distinct to_char(auditdate,'dd-mon-yy')) clause in the SQL will suppresses the repeating login entries for a particular date as the to_char format ignores the time part of the auditdate thereby taking only one login per day into account. Hope this clarifies.

Jim_Burke_(LMGTAdmin_(Delete)_2167536)

When I populated the LR, I need some clarification/help...First...are there really supposed to be two ORDER BY in the last portion of the SQL (i.e. ORDER BY ORDER BY)Second...I still keep getting an Invlaid Column Name errorNote...I am using Oracle 8.1.5 on Unix...but SQL should be transparent...shouldn't it?

Marita_Ventura_(DCAdmin_(Delete)_2237520)

I'm sorry about that. It was a typo error. Firstly, U need to have ORDER BY clause only once. Secondly, the column alias is not substituted in the livereport. So u have to retype the whole function if you want. Please replace the sql with the following. You may choose to ignore the statement from ORDER BY.. if u want to.SQL :-SELECT daudit.userid as "USERID",kuaf.name AS "NAME",COUNT( distinct TO_CHAR(DAUDIT.auditdate,'DD-MON-YY')) AS "DAYSLOGGED" FROM daudit, kuaf WHERE (daudit.auditdate >= %1 AND daudit.auditdate <= %2) AND daudit.event = 'LOGIN' AND daudit.userid = kuaf.id GROUP BY kuaf.name,daudit.userid order by COUNT( DISTINCT TO_CHAR(DAUDIT.auditdate,'DD-MON-YY')) desc

Jim_Burke_(LMGTAdmin_(Delete)_2167536)

This was great! Thanks for all the help...I'll have a another bunch of questions right on the heels of this one though. One side note for making this LR into a BARCHART, swap the first column from being UserID. The LR settings like having the first column as a STRING type.Change the SQL to: SELECT kuaf.name AS "NAME",COUNT( distinct TO_CHAR(DAUDIT.auditdate,'DD-MON-YY')) AS "DAYSLOGGED", daudit.userid as "USERID"

Jim_Burke_(LMGTAdmin_(Delete)_2167536)

The LR that has been posted by DCAdmin (thanks!) works great for showing Number of Times each specific user has logged in during a specific period.The data looks something like this:Username..........Days Logged InAdmin.............23jsmith............22jdoe..............22jqpublic..........22msmith............21etcThe next logical step would be to take this data and show a Livelink Utilization curve It would appear that the raw data has been generated...would the next step be a SQL tweak or a launch of a sub-report?Based on the sample data above, I would ultimately expect the next bit of data to look something like this:Days Logged In......Number of Users25..................024..................023..................122..................321..................1etc

Jim_Burke_(LMGTAdmin_(Delete)_2167536)

Assuming the data that the LiveReport covers is for a time period of one month, how does the LR discriminate on users that are continually logged in?For example, if a user logs in on the first of the month and stays logged in for 16 days. Does this LR capture this user as having logged in on only one day or having been logged in for 16?

Marie_Lindsay_(MLindsay_(Delete)_15608)

The LOGIN event is only recorded at the point when the user logs in, so the report earlier in this thread probably wouldn't reflect that user's "presence" in Livelink. If the general idea is to find out how many users are using your LL system each day, perhaps a report like this would be useful:select distinct(trunc(auditdate)) "Day", count(distinct userid) "Active Users" from daudit group by trunc(auditdate)This should tell you how many distinct people actually did one or more things that were recorded in the audit trail (whether it be log in, delete, fetch, view, or whatever) each day.The above is Oracle-specific (the trunc function discards the "time" part of the audit date). I think a SQL-Server version that would work is:select distinct(convert(char(10), auditdate, 111)) "Day", count(distinct userid) "Active Users" from daudit group by convert(char(10), auditdate, 111)