It's all about the answers!

Ask a question

What table contains contributor last login date?

Mike Shkolnik (9657160143) | asked Sep 15 '11, 5:48 p.m.
I just searched through all the database tables trying to find one with the last login date for contributors and did not find it. Likely it has some other unusual name. Any ideas? There is a list of all the tables here:

I need to create a report showing the last login dates of all RTC users, so if there is some other way of getting that please let me know.

Accepted answer

permanent link
Mike Shkolnik (9657160143) | answered Jul 13 '12, 12:43 p.m.
edited Jul 13 '12, 12:48 p.m.
Someone on our DevOps team figured out how to get this info.

Mike, attached is the script I used to generate the list of RTC users and their last login.

Here is the entry I have in the crontab file to run it monthly:

10     0      1      *      *      /home/jazzadm/bin/list-rtc-logins 2>&1 | mail -s "Recent RTC user logins sorted by user"

The script file, titled "list-rtc-logins":
#!/usr/bin/env bash
trap "exit 1" 1 2 3 15
function usage {
        echo "Usage: $(basename "$0") [-d] [-u]" 1>&2
echo " -d Sort by date" 1>&2
echo " -u Sort by user" 1>&2
        exit 2
while getopts du OPTCHAR; do
case $OPTCHAR in
d) SFLAG=+2;;
u) SFLAG=+1;;
?) usage;;
shift $(($OPTIND - 1))
#echo "Remaining arguments are: $*"
case $# in
0)     ;;
*)      usage;;
LOGFILES=$(find /opt/app/jazz/server/tomcat/logs -name localhost_access_log.\*txt)
GZLOGFILES=$(find /opt/app/jazz/server/tomcat/logs -name localhost_access_log.\*gz)
| sed -e '/buildman/d' -e '/^-/d' \
-e 's/^[0-9]*\.[0-9]*\.[0-9]*\.[0-9]* - //' -e 's/ [+-][0-9].*//' \
-e 's/\[//' \
-e 's;\([0-9][0-9]\)/Jan/\([0-9][0-9][0-9][0-9]\).;\2-01-\1T;' \
-e 's;\([0-9][0-9]\)/Feb/\([0-9][0-9][0-9][0-9]\).;\2-02-\1T;' \
-e 's;\([0-9][0-9]\)/Mar/\([0-9][0-9][0-9][0-9]\).;\2-03-\1T;' \
-e 's;\([0-9][0-9]\)/Apr/\([0-9][0-9][0-9][0-9]\).;\2-04-\1T;' \
-e 's;\([0-9][0-9]\)/May/\([0-9][0-9][0-9][0-9]\).;\2-05-\1T;' \
-e 's;\([0-9][0-9]\)/Jun/\([0-9][0-9][0-9][0-9]\).;\2-06-\1T;' \
-e 's;\([0-9][0-9]\)/Jul/\([0-9][0-9][0-9][0-9]\).;\2-07-\1T;' \
-e 's;\([0-9][0-9]\)/Aug/\([0-9][0-9][0-9][0-9]\).;\2-08-\1T;' \
-e 's;\([0-9][0-9]\)/Sep/\([0-9][0-9][0-9][0-9]\).;\2-09-\1T;' \
-e 's;\([0-9][0-9]\)/Oct/\([0-9][0-9][0-9][0-9]\).;\2-10-\1T;' \
-e 's;\([0-9][0-9]\)/Nov/\([0-9][0-9][0-9][0-9]\).;\2-11-\1T;' \
-e 's;\([0-9][0-9]\)/Dec/\([0-9][0-9][0-9][0-9]\).;\2-12-\1T;' \
| awk '
        name = $1;
        dt = $2;
        if (a[name] < dt)
a[name] = dt;
        for (x in a) {
                printf "%s\t%s\n", x, a[x];
}'  | sort -k $SFLAG
exit 0
Ralph Schoon selected this answer as the correct answer

Nilesh Patil commented Oct 25 '13, 12:22 a.m.

Hi Mike Shkolnik,
 I am new to this. I have to get report for all contributors with last logged in time. Could you please where to place this script, how to run this script and as mentioned "
Here is the entry I have in the crontab file to run it monthly" which file should I make this entry

Thanks in advance

6 other answers

permanent link
VK L (8177146159) | answered Jul 14 '12, 9:48 a.m.
 Hi Mike,
                This script looks specific for tomcat. What are the required chANges in the case of the apps deployed on WAS with User registry on AD?


permanent link
Mike Shkolnik (9657160143) | answered Jul 16 '12, 2:07 p.m.
It is indeed for Tomcat. My DevOps person has opted not to recommend script changes for an environment we don't have and can't test and suggests that your scripting person should be able to make the necessary changes. Or perhaps someone else on the forum who is familiar with scripting can make and test the changes and post their results?

permanent link
VK L (8177146159) | answered Jul 17 '12, 3:45 a.m.
Hi Mike,
              My query is related to the method to be adopted to retrieve the last login info - in case of RTC/JTS deployed on WAS instead of Tomcat. Because, I am unable to find the localhost_access_log file in the WAS paths . This solution looks to be specific to Tomcat.
Please advise on the method to adopt in case of WAS Deployments.


permanent link
Mike Shkolnik (9657160143) | answered Jul 17 '12, 1:22 p.m.
Sorry, our guy has no knowledge of WAS. Perhaps someone else here can help?

permanent link
Mike Shkolnik (9657160143) | answered Sep 04 '13, 7:08 p.m.
I am writing an app that reads in all the log files and gives you a list of all users with their last login date. I have this working for Tomcat. If someone on WAS can send me a sample log file, I will get this working for WAS as well. I will post this on a blog soon.

Kevin Ramer commented Nov 14 '13, 3:44 p.m. | edited Nov 14 '13, 3:44 p.m.

For what it's worth:  Bare WAS doesn't record the user id as does Tomcat.  E.g. to get access logs WAS has to be configured for NCSA Http logging.  It's entries look something like:

IP %l - [17/Apr/2013:02:14:55 -0400] "GET /qm/service/ HTTP/1.1" 404 -
IP %l - [17/Apr/2013:02:14:55 -0400] "POST /ccm/service/ HTTP/1.1" 200 -
IP %l - [17/Apr/2013:02:14:56 -0400] "GET /jts/contributorRecords/?query=since+%3E%3D+%222013-04-17T05%3A13%3A56Z%22%5E%5EdateTime&withExtensions=true HTTP/1.1" 200 -

whereas Tomcat usually have
IP - user@dom [ timestamp ] "activity..."

permanent link
anoop mc (74810172216) | answered Nov 13 '13, 12:46 a.m.

Hi Mike,

We have RTC and JTS deployed on windows platform, which uses Apache Tomcat Application Server.

Could you please guide us how to get this deployed to pull out the similar report.

Mike Shkolnik commented Nov 14 '13, 2:00 p.m.

I'm afraid I don't know much about unix scripting, so I don't know how it's implemented. I have written a "user management tool" which reads all the log files off a local drive (you need to copy them from the server first) and then spits out a report showing last login (as well as team allocations, work hours, and other activity). I will offer this up for use by others once I get licenses working. I have been able to figure out how to pull license type (such as Contributor, Developer, Stakeholder) from the API yet.

Your answer

Register or to post your answer.