Convert AD Logon Time to Readable Dates

Posted on by ChristineAli 
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

Discover more from Christine Alifrangis

Subscribe to get the latest posts sent to your email.

Leave a Reply