We need to provide a audit report to the management on who has accessed the Atlassian tool - Jira in the last 30 days ?
Hello,
In the users list (site administration) you have the last time they were logged in. In the right corner of the page you have an export available
Andreia,
thanks for the suggestion. However we are not getting any option for exporting.
our jira version is 7.1.9
note:-we only need the reports for last 30 days
The audit logs in Jira can keep track of changes to user management, but they don't actually track when users last logged into the site. For that purpose, you might have better luck trying to follow the KB Retrieve last login dates for users from the database.
You should be able to use the SQL queries on that KB in order to generate a list of users that have at least had a log in to Jira within a specific time period. However you would need to directly access the database Jira is running on for this to work.
I hope this helps.Andy
Can we get this done by using DB query ?
We are using MSSQL and Can you share us the query to achieve that.
DBnames
Jira_DB
Jira version - 7.1.9
Jira version is 7.1.9
As @Andy Heinzer stated, the statements are referenced in the KB linked in his response.
MSSQL is
SELECT cwd_user.user_name, dateadd(second,cast(cast(cwd_user_attributes.attribute_value AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000')FROM cwd_user, cwd_user_attributesWHERE cwd_user.id = cwd_user_attributes.user_id AND cwd_user_attributes.attribute_name = 'login.lastLoginMillis'
CCM
Hi Vivuu,
The KB article I linked does include MS SQL specific queries for this: Retrieve last login dates for users from the database.
Please note that this KB has different queries to run depending on what version of Jira you're running. If you are using Jira 6.2 or higher on MS SQL the query would look like this:
<span>SELECT</span> <span>d</span><span>.</span>directory_name <span>AS</span> <span>"Directory"</span><span>,</span> u<span>.</span>user_name <span>AS</span> <span>"Username"</span><span>,</span> DATEADD<span>(</span>second<span>,</span> cast<span>(</span>attribute_value <span>as</span> <span>bigint</span><span>)</span><span>/</span><span>1000</span><span>,</span>{<span>d</span> <span>'1970-01-01'</span>}<span>)</span> <span>AS</span> <span>"Last Login"</span> <span>FROM</span> dbo<span>.</span>cwd_user u <span>JOIN</span> <span>(</span> <span>SELECT</span> <span>DISTINCT</span> child_name <span>FROM</span> dbo<span>.</span>cwd_membership m <span>JOIN</span> dbo<span>.</span>globalpermissionentry gp <span>ON</span> m<span>.</span>parent_name <span>=</span> gp<span>.</span>GROUP_ID <span>WHERE</span> gp<span>.</span>PERMISSION <span>IN</span> <span>(</span><span>'ADMINISTER'</span><span>,</span> <span>'USE'</span><span>,</span> <span>'SYSTEM_ADMIN'</span><span>)</span> <span>)</span> <span>AS</span> m <span>ON</span> m<span>.</span>child_name <span>=</span> u<span>.</span>user_name <span>LEFT</span> <span>JOIN</span> <span>(</span> <span>SELECT</span> <span>*</span> <span>FROM</span> dbo<span>.</span>cwd_user_attributes <span>ca</span> <span>WHERE</span> attribute_name <span>=</span> <span>'login.lastLoginMillis'</span> <span>)</span> <span>AS</span> <span>a</span> <span>ON</span> <span>a</span><span>.</span>user_id <span>=</span> u<span>.</span>ID <span>JOIN</span> dbo<span>.</span>cwd_directory <span>d</span> <span>ON</span> u<span>.</span>directory_id <span>=</span> <span>d</span><span>.</span>ID <span>ORDER</span> <span>BY</span> <span>"Last Login"</span> <span>DESC</span><span>;</span>
Andy
It looks like you're new here. Sign in or register to get started.
