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; } } }

Count of distinct logins

ray_chance

I have a live report that outputs group names and total logins (for whatever Dept. and time frame the user plugs in). This however is not what management wants. What I need to produce is a list of all base groups with corresponding distinct login counts. That is, if a user logs in 3 times in one day, he/she should still only be conted once. Could you tell me how to approach this? Thanks!Here is the live report for (non distinct) log in counts for groups:SELECT K2.NAME AS "GROUP", K1.NAME, COUNT(K1.NAME) AS "TOTAL LOGINS" FROM KUAF K1, KUAF K2, DAUDIT D1 WHERE K1.GROUPID = K2.ID AND K2.NAME LIKE CONCAT(%1, '%%') AND K1.ID = D1.USERID AND D1.EVENT = 'LOGIN' AND D1.AUDITDATE >= %2 AND D1.AUDITDATE <= %3 AND K1.DELETED = 0 GROUP BY K2.NAME, K1.NAMEstring Dept. Namedate Begin Datedate End DateUser Input1User Input2User Input3

Find more posts tagged with

Comments

Robert_Davies_(unlondonadmin_-_(deleted))

I have a report that counts unique logins for all groups using This week start and This week end as the hard coded date range values. It uses 'distinct' and some date manipulation to get unique logins. I am sure you could incorporate these refinements in your report:SELECT distinct k1.name AS "Group ID",COUNT(distinct TO_CHAR(daudit.auditdate,'DY')) 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(distinct TO_CHAR(daudit.auditdate,'DY')) ascParam %1 - This week start dateParam %2 - This week end dateI actually output this as a Bar Chart but I am sure it would work as an ordinary report as well. The key to getting the unique events is, as I said above, the key word 'distinct' and cutting the date down to just the day of the week (because the time is attached to the date if you use the whole date it is unique for every time a user logs in).I hope this is usefulAnne

Robert_Davies_(unlondonadmin_-_(deleted))

Dear RussI was interested in your report and so did some work on it this morning - I use Oracle SQL by the way - because of your use of the keyword 'CONCAT' I wasn't sure if you did.SELECT K2.NAME AS "GROUP", K1.NAME, COUNT(K1.NAME) AS "TOTAL LOGINS", COUNT(distinct TO_CHAR(d1.auditdate,'DY')) AS "Times" FROM KUAF K1, KUAF K2, DAUDIT D1 WHERE K1.GROUPID = K2.ID AND lower(K2.NAME) LIKE lower(%1||'%%') AND K1.ID = D1.USERID AND D1.EVENT = 'LOGIN' AND D1.AUDITDATE >= %2 AND D1.AUDITDATE <= %3 AND K1.DELETED = 0 GROUP BY K2.NAME, K1.NAMEI selected 'Date' as the input type for param % 2 and param %3, inserted the distinct statement, and made the string input case insensitive. This works nicely.Best regardsAnne

ray_chance

Thanks Anne. You've been very helpful!

ray_chance

One question...how did you make the string input, case insensitive? I too use Oracle. Thanks.

ray_chance

Never mind. I see it in the script. I thought maybe there was a setting on the LiveReport Edit screen that I was unaware of.

ray_chance

Anne, I do have a couple of questions, now that I've played with the report. You mentioned that you use This week start and This week end as hard coded date range values, but it appears above that you allow the user to enter the date range values. Am I missing something? Also, the distinct TO_CHAR is a good function to know, but what does the 'DY' accomplish? Thanks!

Robert_Davies_(unlondonadmin_-_(deleted))

Dear RussWhen I said 'hard coded date' what I meant was instead of using 'string' as the input type I selected 'Date' - this means you have less chance of typing errors in your input - although it is easy not to select the correct date range. When I have done this type of date range report I have tended to use Livelinks hard coded 'This week start' 'This week End' as my % parameters - this way no input from the user is required.
The reason I use DY in the date is because this is what breaks the date down into 'DAY' only and this is why the 'DISTINCT' works. If you don't just select the day part of the date then the date is unique for every user sign on during the day because time is part of the Oracle date field i.e. it is a different value. Does this make sense?Best regardsAnne

ray_chance

It makes perfect sense. Thank you for the explanation.Best Regards