Home
Extended ECM
API, SDK, REST and Web Services
Live Reports for User Login activity per Group - Livelink 9.0.0.1 on SQL2K
Nethaji_Narasimalu
Hello eLink,I wonder if anyone has come across the following Live Reports -Say User Login per Group over a period of time' - users logged in over date range' in order to figure out mostactive users per Department/Group.Please let me know.In addition it should provide total Users Login activitysorted by Depatment/Group.Ideally it should have Start/Finish date parameters.Any input would be appreciated.Regards,
Find more posts tagged with
Comments
eLink User
Message from Sean M Alderman via eLinkI think we have user logins between selected dates. I took a quick lookat the User Parameter that gives you a selectable User, but it won'tallow you to select a group. Here's the query we have for loginsbetween a timeframe. This is an Oracle report, so some of the functionsmight be different on SQL2K (e.g. to_char). I'd be interested to knowif there's a way to get a userinput of a groupID by selecting a groupfrom a popup dialog.SELECT Count(a.USERID) "Logins", b.firstname || ' ' || b.lastname"Name", b.mailaddress "eMail", b.name "Username", Max(a.auditdate) "LastAcess" FROM DAUDIT a, KUAF b WHERE a.EVENT = 'LOGIN' AND a.USERID = b.ID ANDto_char(a.AUDITDATE,'yyyy mm dd') Between to_char(%1,'yyyy mm dd') andto_char(%2,'yyyy mm dd') GROUP BY a.USERID, b.firstname, b.lastname, b.mailaddress, b.name ORDER BY Count(a.USERID) DESC, b.lastname, b.firstnameOn Thu, 2002-06-13 at 12:13, eLink Discussion: Livelink LiveReportsDiscussion wrote:> Live Reports for User Login activity per Group - Livelink 9.0.0.1 on SQL2K> Posted by BRITANNIAAdmin on 06/13/2002 12:08 PM> > Hello eLink,> > I wonder if anyone has come across the following Live Reports -> > Say User Login per Group over a period of time' - users logged in over date range' in order to figure out most> active users per Department/Group.Please let me know.> > In addition it should provide total Users Login activity> sorted by Depatment/Group.> > Ideally it should have Start/Finish date parameters.> > Any input would be appreciated.> > Regards,> > > > [To reply to this thread, use your normal e-mail reply function.]> > ============================================================> > Discussion: Livelink LiveReports Discussion>
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view>
; > Livelink Server:>
https://knowledge.opentext.com/knowledge/livelink.exe>
; > -- Sean M. AldermanITRACK Systems AnalystPACE/NCI - NASA Glenn Research Center(216) 433-2795Calling a windowed operating system "Windows" is like naming anautomobile "Wheels."
Nethaji_Narasimalu
Sean,This is brilliant, thank you. All I need now is to make surethat I can get the details of which Group a user belongs toin this report, that would be sufficient. All I need is to know how to call a group name and get it displayed as oneof the fields on this report. Any ideas?Please let me know if you have any thoughts...Thanks in advance.Regards,
Robert_Davies_(unlondonadmin_-_(deleted))
Dear JohnWe analyse activity in a variety of ways and for various reasons. I have attached a HTML file showing our activity folder reports. Below is detailed an activity report that shows users within groups. The input can be a group name/ partial group name and or User name/ partial user name(User Id). This SQL is for Oracle so a useful site to find eqivalencies for MS SQL functions is:
http://www.microsoft.com/technet/SQL/Technote/oracle.aspPrompts:Input
Type: string Prompt: Group NameInput Type: string Prompt: User NameSQLselect c.Name "User Name",c.firstname,c.lastname,b.Name "Group Name",substr(daudit.Dname,1,20) "Node",daudit.auditdate,substr(daudit.event,1,3) "Act" from KUAFChildren a,KUAF b,KUAF c,daudit where a.ID=b.ID and a.ChildID=c.ID and daudit.userid=c.id and (b.name like %1||'%%' or lower(b.name) like %1||'%%' or upper(b.name) like %1||'%%') and (c.name like %2||'%%' or lower( c.name) like %2||'%%' or upper(c.name) like %2||'%%') order by c.lastname,c.firstname,daudit.auditdateParam %1 : User Input 1Param %2 : User Input 2AutolivereportLet me know if you find this useful - as I said it would be easy to include a date range but we use this to assess a students total activity on a particular course.Best regardsAnne
Robert_Davies_(unlondonadmin_-_(deleted))
Dear JohnHTML file did not attach.Project Workspace: 01 Activity Reports - date ranges/users etc. Activity for a given date Activity for a given user on a given date Activity last Month for a given user Activity Month to date for a given user Activity on a given DAPI node Activity on a group of DAPI nodes Activity Today for a given user Create Activity for a given date Monthly activity report for Analog comparison Total Activity for a given group Total Activity for a given user User Activity by Group User Activity within Date Range [Modified]
Nethaji_Narasimalu
Dear Ann,Thank you so much for your responce. It is "User Activity by Group" report I am primarily concerned with.Can you please send me a copy of the SQL for it with fieldsif possible.Would be much appreciated.Thanks a million.Kind regards,
Robert_Davies_(unlondonadmin_-_(deleted))
Dear JohnIf you enter the group name on the report I sent thenyou will only get the activity for that group. You could modify this report to just count items instead of printing the detail.Here is another report I use that counts the number of times users in each group have logged in in the previous week. It uses a bar chart to display the data. I do various flavours of this report as well by modifying the date range parameters.SQLSELECT distinct k1.name AS "Group ID",COUNT( daudit.auditdate) AS "Times" FROM daudit, kuaf k1,kuaf k2 WHERE (daudit.auditdate >= %1 AND daudit.auditdate <= %2) AND daudit.event = 'LOGIN' AND daudit.userid = k2.id and K1.id=k2.groupid GROUP BY k1.name ORDER BY COUNT(daudit.auditdate) ascParam %1 : This week startParam %2 : This week endReport Format: Bar ChartBy changing the filter on 'daudit.event' or eliminating it you can change the report to analyse different types of events. However the 'Bar Chart' format doesn't handle large displays that well.Best regardsAnne
Nethaji_Narasimalu
Dear AnnThanks very much for this. It is exactly what I need. Theonly thing is that I am not so big on Oracle and I am trying to translate this into SQL2K.Can you let me know what is the significance of kuaf k1 andkuaf k2 references?Look forward to hearing from you soon.Kind regards,
Nethaji_Narasimalu
Dear Anne,Please ignore my previous message. I got it to work on SQL2K all right. Now I am just tuning some extras thatI want to get out of these reports.Thanks again.Kind regards,
Nethaji_Narasimalu
Dear Anne,I got most of the reports working now, the only thing I need to accomplish is to find a way of representing '%%' string var in SQL2K.I have a question for you - in the report as above do you need to do the input for both fields or is it notnecessary?Please let me know.Regards,
Robert_Davies_(unlondonadmin_-_(deleted))
Dear JohnThe % is the wildcard character. As far as I can determine it is the same for MS SQL. In this SQL I concatenate the wildcard character with the input, thus if one of the inputs is not entered then the values are compared against a wildcard character therefore no filtering is done. The reason I use %% is because Livelink previously needed two wildcard characters to perform the wildcard function. I don't know if this is the case with Livelink on MS SQL.It doesn;t matter which input field you use, indeed because of the use of Wild cards you could leave out both.This answers your second question I think 'do you need all fields input' the answer is no because by concatenating the input with a wildcard if there is no input then all values are selected.I use the functions 'lower' and 'upper' to prevent any problem with uppercase or lowercase input. I.E. pclt is equivalent to PCLTBest regardsAnne
Robert_Davies_(unlondonadmin_-_(deleted))
Dear JohnHave just read your email asking about %% in Livereports. I have just replied to your other query. As I said this is just a wildcard. MS SQL seems to use the same wildcard. The double % is used because Livelink needed the double percentage to process the wildcard properly. However if you ran the same SQL using the Oracle SQL tool then you would only need one %.Below is a useful address if you are converting Oracle SQL to MS SQL
http://www.microsoft.com/technet/SQL/Technote/oracle.aspBest
regardsAnne
Nethaji_Narasimalu
Thanks Anne,It appears that Oracle wildcards for Livelink Live Reportsof the "||'%%'" should translate nicely to "+ '[%]%'" format but I am still having some problems with gettingit to work.I will keep trying...Regards,
Nethaji_Narasimalu
Anne,Can you please let me know exactly what is being reported onwithin "Monthly activity report for Analog comparison"?Perhaps you can send me a copy of this report, if possible.There are also a few other reports that I am interested infrom the above list -1. Activity last Month for a given user 2. Activity Month to date for a given user3. User Activity by Group 4. User Activity within Date Range [Modified]Perhaps you can post them via eLink in a zipped format.Much appreciated, thank you in advance.Regards,
Nethaji_Narasimalu
Anne,I was testing the above report on Livelink 9.0.0.1 on SQL2Kas you know and I found smth really strange. I created a test user account and assigned this user to a test UserGroup. Then I logged in as the test user, did a view or smth like that and logged out of Livelink.However, when I run the report as above it showed 7 loginevents for that Group. That is kind of strange as the Test Group did not even exist before test user account and I onlylogged in once using the above test account.I wonder if it is counting some login events several times or there is some floors with the report in question.I am currently trying to look through the records in order to find out what can be wrong with the report/ our db...Look forward to hearing from you soon.Regards,
Robert_Davies_(unlondonadmin_-_(deleted))
Dear JohnCheck the SQL - if daudit.event is filtered for 'Login' then there may be a problem, if it is not filtered then list all the events for that user and compare figures. I will forward the other reports later today or tomorrow. If you do have a query on any of the reports it is easier for me to identify the exact report you are referring to if you quote the SQL.Best regardsAnne
Robert_Davies_(unlondonadmin_-_(deleted))
Activity reports for Analog Comparison was a count of activity events on the Livelink servers by month in an attempt to provide some type of comparison with a Web activity analysis tool called Analog.1 and 2 are basically the same reports with different date range parameters:Input User Prompt User?SQLSELECT kuaf.name AS "USERID", daudit.event AS "Activity" ,daudit.AuditDate "Date", daudit.Dname "Node affected", daudit.DataId "DAPI Node Id." FROM daudit, kuaf WHERE (daudit.auditdate >= %1 AND daudit.auditdate <= %2) AND (daudit.userid = kuaf.id and daudit.userid = %3) ORDER BY daudit.AuditDate descParam %1 Last Month StartParam %2 Last Month EndParam %3 User Input 1ReportFormat AutoLivereportActivity Month to date uses Param %1 This month StartParam %2 This month EndApart from those changes everything else is the sameUser Activity be group is just a modification of the report I already gave youUser activity within date rangeInput type Date Prompt From?Input type Date Prompt To?SQL:SELECT Count(a.USERID) "Logins", b.firstname || ' ' || b.lastname "Name", b.mailaddress "eMail", b.name "Username", Max(a.auditdate) "Last Acess" FROM DAUDIT a, KUAF b WHERE a.EVENT = 'LOGIN' AND a.USERID = b.ID AND to_char(a.AUDITDATE,'yyyy mm dd') Between to_char(%1,'yyyy mm dd') and to_char(%2,'yyyy mm dd') GROUP BY a.USERID, b.firstname, b.lastname, b.mailaddress, b.name ORDER BY Count(a.USERID) DESC, b.lastname, b.firstnameParam %1 User Input 1Param %2 User Input 2Format AutolivereportThe to_char function just strips out the time from the date.Did you resolve your possible duplication query?Best regardsAnne
Nethaji_Narasimalu
Anne,I hope you will get a chance to forward me other reportsat some point today.I am checking out why could it be that the login event iscounting 1 Login several times and I will let you knowlater if you are interested.BTW - Are you using any security features at your site, forexample RSA?Regards,
Robert_Davies_(unlondonadmin_-_(deleted))
Activity ReportsDear JohnI put up the SQL you requested last Friday (under previous reply). I would be delghted to hear if/why there is double counting.Anne
Nethaji_Narasimalu
Dear Anne,I am sorry but I cannot see the SQL that you posted that yourefer to in your reply and your email. Can you please tell me where was it posted to? Alternitively you can email itto myself to my email address as it follows from my mail.Look forward to hearing from you soon.Regards,Edward Dudetsky
