Find the last login date for users in Bamboo

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

    

Purpose

Get a list of users showing their last login timestamp in order to audit application usage using both database and logging approaches.

tip/resting Created with Sketch.

Please keep in mind that if the user has a "Remember Me" token set, then the Last Login date from the database will not reflect the last time the user accessed Bamboo, but will instead show the last time they had to go through the login process. 

Environment

Bamboo local and LDAP/AD authentication:
  • Bamboo 6.6 and above
  • Only valid for local accounts in Bamboo. Will miss the last login timestamp when the authentication uses Jira or Crowd
  • This will not work on versions below Bamboo 6.6
Crowd authentication:
  • When Bambo authenticates via a Crowd database
JIRA authentication:
  • When Bambo authenticates via a Jira database
Logging approach:
  • No version requirements however log format will differ between versions which will require different processing.

Solution

Bamboo local an LDAP/AD authentication:

This solution will require you to have the appropriate access to run the below SQL queries on your Bamboo database

PostgreSQL 
SELECT d.directory_name AS "Directory", u.email_address AS "Email Address",
    u.user_name AS "Username",
    to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login", u.active
FROM cwd_user u
LEFT OUTER JOIN (
    SELECT *
    FROM cwd_user_attribute ca
    WHERE attribute_name = 'lastAuthenticated'
    ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC;
MySQL 
SELECT d.directory_name AS "Directory", u.email_address AS "Email Address",
    u.user_name AS "Username", 
    from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) AS "Last Login", u.active
FROM cwd_user u
LEFT OUTER JOIN (
    SELECT *
    FROM cwd_user_attribute
    WHERE attribute_name = 'lastAuthenticated'
    ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC;
Microsoft SQL 
SELECT d.DIRECTORY_NAME AS "Directory", u.EMAIL_ADDRESS AS "Email Address",
    u.USER_NAME AS "Username",
  Dateadd(second, Cast(ATTRIBUTE_VALUE AS bigint) / 1000, {d '1970-01-01'}) AS "Last Login", u.ACTIVE
FROM dbo.CWD_USER u
LEFT OUTER JOIN (
    SELECT *
    FROM dbo.CWD_USER_ATTRIBUTE ca
    WHERE ATTRIBUTE_NAME = 'lastAuthenticated'
    ) AS a ON a.USER_ID = u.ID
JOIN dbo.CWD_DIRECTORY d ON u.DIRECTORY_ID = d.ID
ORDER BY "Last Login" DESC;
Oracle 
SELECT d.directory_name, u.email_address AS "Email Address",
    u.user_name,
        TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date, u.active
FROM cwd_user u
LEFT OUTER JOIN (
    SELECT *
    FROM cwd_user_attribute ca
    WHERE attribute_name = 'lastAuthenticated'
    ) a ON a.user_id = u.ID
JOIN cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc;

Crowd authentication:

Use the provided SQL below to query your Crowd database. This solution will require you to have the appropriate access to run the below SQL queries.

PostgreSQL 
SELECT d.directory_name AS "Directory", u.email_address AS "Email Address",
    u.user_name AS "Username",
    to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login", u.active
FROM cwd_user u
LEFT OUTER JOIN (
     SELECT *
     FROM cwd_user_attribute ca
     WHERE attribute_name = 'lastAuthenticated' 
     AND directory_id=<Bamboo directory ID>
     ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id 
WHERE u.directory_id=<Bamboo directory ID>
ORDER BY "Last Login" DESC;
MySQL 
SELECT d.directory_name AS "Directory", u.email_address AS "Email Address",
    u.user_name AS "Username",
    from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) AS "Last Login", u.active
FROM cwd_user u
LEFT OUTER JOIN (
     SELECT *
     FROM cwd_user_attribute ca
     WHERE attribute_name = 'lastAuthenticated' 
     AND directory_id=<Bamboo directory ID>
     ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id 
WHERE u.directory_id=<Bamboo directory ID>
ORDER BY "Last Login" DESC;
Microsoft SQL 
SELECT d.directory_name AS "Directory", u.email_address AS "Email Address",
    u.user_name AS "Username",
    DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Last Login", u.active
FROM cwd_user u
LEFT OUTER JOIN (
     SELECT *
     FROM dbo.cwd_user_attribute ca
     WHERE attribute_name = 'lastAuthenticated' 
     AND directory_id=<Bamboo directory ID>
     ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id 
WHERE u.directory_id=<Bamboo directory ID>
ORDER BY "Last Login" DESC;
Oracle 
SELECT d.directory_name AS "Directory", u.email_address AS "Email Address",
    u.user_name AS "Username",
    TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date, u.active
FROM cwd_user u
LEFT OUTER JOIN (
     SELECT *
     FROM cwd_user_attribute ca
     WHERE attribute_name = 'lastAuthenticated' 
     AND directory_id=<Bamboo directory ID>
     ) AS a ON a.user_id = u.ID
JOIN cwd_directory d ON u.directory_id = d.ID
WHERE u.directory_id=<Bamboo directory ID>
ORDER BY last_login_date DESC;

JIRA authentication:

When authenticating in Bamboo from an external JIRA instance, you can run the following SQL statement against JIRA's database, however, when authenticating with the same username in JIRA and re-running the SQL statement above, the same gets updated:

SELECT Attr.attribute_value as LAST_AUTHENTICATED
  from cwd_user as Person
  join cwd_user_attributes as Attr
       on Person.id = Attr.user_id
 where Person.user_name = 'USERNAME'
   and Attr.attribute_name = 'lastAuthenticated';
LAST_AUTHENTICATED
1467639110107

After logging in to Jira, the LAST_AUTHENTICATED value changes.

LAST_AUTHENTICATED
1467639474640

This behaviour is better documented on BAM-17728 - Getting issue details... STATUS .

For more complete Jira SQL statements, please refer to this KB article:

Please use the logging approach below as a workaround for Jira authentication.


Logging approach:

For Bamboo versions prior to 9

This method is limited to cover the timeframe covered by your Bamboo application logs which rotate by default depending on size and count.

Application log retention is driven by the configuration in $BAMBOO_INSTALL/atlassian-bamboo/WEB-INF/classes/log4j.properties. While we can't configure an exact timeframe, we can increase the number of files we keep and their size before rotating:

#using 'bamboo home aware' appender. If the File is relative a relative Path the file goes into {bamboo.home}/logs
log4j.appender.filelog=com.atlassian.bamboo.log.BambooRollingFileAppender
log4j.appender.filelog.File=atlassian-bamboo.log
log4j.appender.filelog.MaxFileSize=100MB
log4j.appender.filelog.MaxBackupIndex=5
log4j.appender.filelog.layout=org.apache.log4j.PatternLayout
log4j.appender.filelog.layout.ConversionPattern=%d %p [%t] [%c{1}] %m%n

For Bamboo version 9 and above

No changes are required.


For example, assuming the default application log format where the user is logged as the 13th column (Bamboo 8.0+):

2021-07-16 13:57:37,644 INFO [http-nio-8085-exec-13 url: /bamboo/admin/elastic/manageElasticInstances.action] [BambooAuthenticator] Finished doing authentication checks for user, took 53.81 ms

We can retrieve a list of users who have accessed Bamboo for the current month using this grep:

grep -a -hr "Finished doing authentication checks for" $BAMBOO_HOME/logs/atlassian-bamboo.log* | awk -F ' ' 'BEGIN {date = $1} {if ($1 > date) a[$13] = $1} END {for (i in a) print i" : " a[i]}'

In versions of Bamboo older than 8.0, the user will be logged in the 11th column, so the grep will need to be adjusted:

grep -a -hr "Finished doing authentication checks for" $BAMBOO_HOME/logs/atlassian-bamboo.log* | awk -F ' ' 'BEGIN {date = $1} {if ($1 > date) a[$11] = $1} END {for (i in a) print i" : " a[i]}'

Sample output:

user, : 2021-08-11
user2, : 2021-08-10


Here's a script that will capture failed authentication attempts for a specific user. This is useful to identify the source of a connection that may be ending up locking the user account frequently:

#!/bin/bash
USER=bamboo
BAMBOO_HOME=/var/atlassian/application-data/bamboo
THREADS=$( grep "login : '${USER}' could not be authenticated with the given password" ${BAMBOO_HOME}/logs/atlassian-bamboo.log \
	     | cut -d" " -f4 | sed 's/\[//;s/\]//' )

echo "Failed login attempts for user ${USER}":
for t in ${THREADS} ; do
  egrep -e "\[${t}\].*[AccessLogFilter].*userlogin.action" ${BAMBOO_HOME}/logs/atlassian-bamboo-access.log | awk '{ print $1, $2, "=> Source IP:", $6 }'
done

Sample output:

$ find_lock_ip.sh
Failed login attempts for user bamboo:
2022-11-02 16:31:08,248 => Source IP: 10.0.9.6
2022-11-02 16:51:22,439 => Source IP: 10.0.9.6
2022-11-02 16:45:49,397 => Source IP: 10.0.9.6
2022-11-02 16:57:10,826 => Source IP: 10.0.9.6

Last modified on May 19, 2023

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.