show no. of users logged in per month (bar chart)

Sander_van_der_Moolen_(verkeeruser1_-_(deleted))

Hi,I know there are a lot of threads in here about counting active, inactive, and deleted users, and i already use these reports (and a big thanks! to all of you who share their Livereports within this group!), but now i would like to take this a step further: I want to create a Livereport showing a bar chart of a: the number of logins per month, and b: the number of different users logged in per month, both over a period of (for instance) six months. That is, this month and the five preceding ones. Preferably without user input.I got as far as a count of users and total logins for one month, but i just can't figure out how get user counts of different time periods in one result page.Can this be done through a single sql statement?Again, thanks a lot!Regards,SanderP.s. I know i'm quite active lately in this discussion forum, but Livereports are so much fun!

Find more posts tagged with

Comments

x-unileveruser11_-_(deleted)

All the info is in DAUDIT, and you can use the DISTINCT keyword to seperate them out, be careful as you may have to ignore the timestamp portion of the event date to get this to work.Also remember that many of your users will be logging in multiple times each day rather than once.

Sander_van_der_Moolen_(verkeeruser1_-_(deleted))

Hi Ben,Ok, i got that far. I have a statement to show me all logins for a given timeperiod:select count(userid) "Aantal Logins" from daudit where event='login' and auditdate between %1 and %2;Where %1 and %2 is the time period.And i have a statement that shows me the distinct users that logged in in that time period:select kuaf.name "Login naam", daudit.auditdate "Datum en Tijd" from daudit, kuaf where daudit.event='login' and daudit.auditdate between %1 and %2 and daudit.userid=kuaf.id group by daudit.auditdate, kuaf.name order by kuaf.name;Now, for my bar chart i won't need the usernames, just the number (count) of users. The thing is, these statements will give me only one count, and i want one chart which will show me (for instance) six counts per month over a period of six months, sorted by month. And that's the part i can't figure out.Thanks for the help!Sander.

Sander_van_der_Moolen_(verkeeruser1_-_(deleted))

select substring(convert(char, auditdate, 106), 4, 8) "Month", count(distinct(userid)) from daudit where event='login' group by substring(convert(char, auditdate, 106), 4, 8);This is for mssql. To get this in Oracle you will need to substitute to_char(auditdate, 'dd mm yy') for the convert(char, auditdate, 106) part.One for the books?ciao,Sander

Sander_van_der_Moolen_(verkeeruser1_-_(deleted))

Here i have some slight changes to the substring part, because the former one would be in alphabetical order...The entire statement with the changes:select substring(convert(char, auditdate, 104), 4, 7) "Month", count(distinct(userid)) "No. of users" from daudit where event='login' group by substring(convert(char, auditdate, 104), 4, 7);Select the bar chart as report type.Sander.

David_Beliveau_(sanmin013_-_(deleted))

This report works great. I also modified it a bit to get the total logins per month, rather than just distinct user count. I noticed when the cursor is moved over a bar, the value is displayed however I did not see anywhere in the livereport creation process where you could specify to have the actual value printed on the chart for each bar, or even a legend. Is this possible?Thanks, Dave Beliveau