×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Get users Last Login Data

thread436-1812106

Get users Last Login Data

Get users Last Login Data

(OP)
Hi Everyone,

I had this thread on SQL Server forum and was told since I am using MySQL to ask for this request here:


I am a newbie getting started with SQL and did some search online but so far have not been able to solve and gather the data I need.

I need to get all users last login data so I may then delete user accounts that not logged in for a long time as I have reached the max license usage for this system.

I have found the Opinio SQL table and drilled down to table OPS_UserAttribute where I can see column named AttributeName which when I run a search on this table using MySQL the data results in last login instead of showing me the actual date of last login (below a screenshot of data I am looking for)
.

I ran below SQL command but all I got was data as 0 or 1:



Thank you in advance for your support!

RE: Get users Last Login Data

Just a guess, but is the select statement carrying out a comparison and returning 0 or 1 depending on whether the comparison is true or false?

Do you need to also get some other fields?

RE: Get users Last Login Data

(OP)
When I run below Select statement to gather all column data from the OPS_UserAttribute table I can see the "last login" - I think what I need is to decipher the "StringValue" and\or the LongValue, BigTextValue real data as it shows Null on some but I can see data on LongValue column but that shows numeric numbers instead of actual Date data...




Correct I need to match this "Last Login" info with the users which I believe is under the table OPS_User which shows column "UserId" on both tables

RE: Get users Last Login Data

The long string is a unix timestamp. The first row is for May 25, 2021. Convert here... https://www.epochconverter.com/

CODE

SELECT *, FROM_UNIXTIME(`LongValue`) AS `timestamp` FROM opinio.OPS_UserAttribute WHERE `AttributeName` = 'last login' ORDER BY `LongValue` ASC

This shows what you've queried before (*) but adds a column at the end with a readable timestamp (FROM_UNIXTIME(`LongValue`)). It also orders the result so the oldest logins appear first (ASCending).

Caution should be exercised here. Limit your SQL activity to SELECT; not DELETE or UPDATE. Without fully understanding the structure of this system, removing accounts in SQL may wreck the application, especially if other data is related to these accounts in other tables. Use this only to list users, but remove the users within the web application.

RE: Get users Last Login Data

(OP)
Thank you so much for the support!

I ran below Code and I got many 1969 year date since "LongValue" was "0" which means user never logged in so no timestamp (that's ok)... then I changed order by to "DESC" and I can see "NULL" value under timestamp. As an example for 1 userid 10553 system shows date of last login as: May 20, 2021 2:10 PM but comes up as "Null" value running below code (looks like Code was not able to convert the "LongValue":

CODE --> 

SELECT *, FROM_UNIXTIME(`LongValue`) AS `timestamp` FROM opinio.OPS_UserAttribute WHERE `AttributeName` = 'last login' ORDER BY `LongValue` DESC








RE: Get users Last Login Data

That null for `timestamp` is very odd. I cannot explain that.

RE: Get users Last Login Data

(OP)
How can I add to the Code the "Login" data coming from the "opinio.OPS_User" table as both "opinio.OPS_User" and "opinio.OPS_UserAttribute" tables have the unique column match "Userid"

CODE --> 

select Login, UserId from opinio.OPS_User

RE: Get users Last Login Data

(OP)
samjim,

Did a research on FROM_UNIXTIME showing null values and I got it working, basically because unix time is in seconds and data shows stored it in milliseconds requiring to divide by 1000:

CODE --> 

SELECT *, FROM_UNIXTIME(`LongValue` /1000) AS `timestamp` FROM opinio.OPS_UserAttribute WHERE `AttributeName` = 'last login' ORDER BY `LongValue` DESC


RE: Get users Last Login Data

Quote (Marclem)

because unix time is in seconds and data shows stored it in milliseconds requiring to divide by 1000

Whoops. I was not paying attention to the length of those strings. Well done!

RE: Get users Last Login Data

>How can I add to the Code the "Login" data coming from the "opinio.OPS_User" table
>as both "opinio.OPS_User" and "opinio.OPS_UserAttribute" tables have the unique
>column match "Userid"

...
From opinio.OPS_UserAttribute, opinio.OPS_User
WHERE opinio.OPS_UserAttribute.Userid = opinio.OPS_User.Userid

or use aliases for your tables:
...
From opinio.OPS_UserAttribute A, opinio.OPS_User U
WHERE A.Userid = U.Userid

or
SELECT *, FROM_UNIXTIME(`LongValue` /1000) AS `timestamp`,
(Select Login From opinio.OPS_User Where UserId = opinio.OPS_UserAttribute.UserId) AS TheUser
FROM opinio.OPS_UserAttribute
WHERE `AttributeName` = 'last login'
ORDER BY `LongValue` DESC


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Get users Last Login Data

(OP)
Thank you so much Andy!

I went ahead and added another field from OPS_user table:

CODE --> 

SELECT FROM_UNIXTIME(`LongValue` /1000) AS `timestamp`,
(Select Login From opinio.OPS_User Where UserId = opinio.OPS_UserAttribute.UserId) AS BOPID,
(Select UserName From opinio.OPS_User Where UserId = opinio.OPS_UserAttribute.UserId) AS UserName
FROM opinio.OPS_UserAttribute
WHERE `AttributeName` = 'last login'
ORDER BY `LongValue` DESC


Thank you as well spamjim!

Not sure if it is possible to do, during the sql code, but when I export the data into a csv file the "timestamp" value gets gibberish which I have to manually do a custom "Format Cell" on the column and set as "yyyy-mm-dd" to get data correctly in excel as "2016-04-04"

RE: Get users Last Login Data

You may want to Google: "mysql convert timestamp to date" smile

Also, you may get a better performance doing this instead:

SELECT FROM_UNIXTIME(A.LongValue /1000) AS `timestamp`,
U.Login AS BOPID, U.UserName
FROM opinio.OPS_UserAttribute A, opinio.OPS_User U
WHERE A.UserId        = U.UserId
  And A.AttributeName = 'last login'
ORDER BY LongValue DESC

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Get users Last Login Data

(OP)
Thanks Andy,

I did a research as you suggested and ended as (will as well do some edit as you suggested to have a better performance):

CODE --> 

Select DATE_FORMAT(FROM_UNIXTIME(`LongValue` /1000), '%m/%d/%Y') AS `timestamp`,
(Select Login From opinio.OPS_User Where UserId = opinio.OPS_UserAttribute.UserId) AS BOPID,
(Select UserName From opinio.OPS_User Where UserId = opinio.OPS_UserAttribute.UserId) AS UserName
FROM opinio.OPS_UserAttribute
WHERE `AttributeName` = 'last login'
ORDER BY `LongValue` DESC

RE: Get users Last Login Data

(OP)
Ended with below code:

CODE --> 

SELECT DATE_FORMAT(FROM_UNIXTIME(`LongValue` /1000), '%m/%d/%Y') AS `timestamp`,
U.Login AS BOPID, U.UserName
FROM opinio.OPS_UserAttribute A, opinio.OPS_User U
WHERE A.UserId        = U.UserId
  And A.AttributeName = 'last login'
ORDER BY LongValue DESC

Thank you everyone!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close