Get users Last Login Data 1

[Marclem]

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!

 

[pjw001]

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?

 

[Marclem]

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

 

[spamjim]

The long string is a unix timestamp. The first row is for May 25, 2021. Convert here...

https://www.epochconverter.com/

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.

 

[Marclem]

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":

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

 

[spamjim]

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

 

[Marclem]

How can I add to the Code the "Login" data coming from the "opiniPS_User" table as both "opiniPS_User" and "opiniPS_UserAttribute" tables have the unique column match "Userid"

select Login, UserId from opinio.OPS_User

 

[Marclem]

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:

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

 

[spamjim]

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!

 

[Andrzejek]

>How can I add to the Code the "Login" data coming from the "opiniPS_User" table
>as both "opiniPS_User" and "opiniPS_UserAttribute" tables have the unique
>column match "Userid"

[tt]...
From opiniPS_UserAttribute, opiniPS_User
WHERE opiniPS_UserAttribute.Userid = opiniPS_User.Userid[/tt]

or use aliases for your tables:
[tt]...
From opiniPS_UserAttribute A, opiniPS_User U
WHERE A.Userid = U.Userid[/tt]

or
[tt]SELECT *, FROM_UNIXTIME(`LongValue` /1000) AS `timestamp`,[blue]
(Select Login From opiniPS_User Where UserId = opiniPS_UserAttribute.UserId) AS TheUser[/blue]
FROM opiniPS_UserAttribute
WHERE `AttributeName` = 'last login'
ORDER BY `LongValue` DESC
[/tt]

---- Andy

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

 

[Marclem]

Thank you so much Andy!

I went ahead and added another field from OPS_user table:

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

 

[Andrzejek]

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

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

[pre]SELECT FROM_UNIXTIME(A.LongValue /1000) AS `timestamp`,
U.Login AS BOPID, U.UserName
FROM opiniPS_UserAttribute A, opiniPS_User U
WHERE A.UserId = U.UserId
And A.AttributeName = 'last login'
ORDER BY LongValue DESC [/pre]

---- Andy

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

 

[Marclem]

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):

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

 

[Marclem]

Ended with below 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!