Convert AD Logon Time to Readable Dates

select

*,

DATEADD(hh, cast((LastLogon0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) as LastLogonDay,

DATEDIFF(dd,DATEADD(hh, cast((LastLogon0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) , GETDATE()) DaysSinceLastLogon



from v_R_System vrs

—————-

If you have added the lastLogonTimestamp field into the AD System Discovery, you can use this SQL Statement:


select

*,

DATEADD(hh, cast((LastLogon0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) as LastLogonDay,

DATEDIFF(dd,DATEADD(hh, cast((LastLogon0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) , GETDATE()) DaysSinceLastLogon,

DATEADD(hh, cast((LastLogonTimestamp0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) as LastLogonTimeStamp,

DATEDIFF(dd,DATEADD(hh, cast((LastLogonTimestamp0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) , GETDATE()) DaysSinceLastLogonTimeStamp

from v_R_System vrs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s