Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.
LOAD
"Pre-W2K Name", (Account)
if ("Pre-W2K Name" like 'User*' , 'USER',
if("Pre-W2K Name" like 'ADM*' , 'ADM',
if("Pre-W2K Name" like 'BAL*' , 'BAL',
if("Pre-W2K Name" like 'GEN*' , 'GEN',
if("Pre-W2K Name" like 'VC*' , 'VC',
if("Pre-W2K Name" like 'ADP*' , 'ADP',
if("Pre-W2K Name" like 'ROOM_*' , 'Room',
if("Pre-W2K Name" like 'FORMATION*' , 'FORMATION',
if("Pre-W2K Name" like 'SVC*', 'SVC'))))))))) as ACCOUNTGROUP,
if("Role" like 'DISABLED' , 'DISABLED',
if("Role" like 'DISABLED\NO_PWDEXP' , 'Disabled and Expired Password',
if("Role" like 'DISABLED\NO_PWDEXP\NO_PWDREQD' , 'Disabled, Password Expired and no password required',
if("Role" like 'NO_PWDEXP' , 'No Password Expired',
if("Role" like 'NO_PWDEXP\ENCRYPT' , 'Encrypted',
if("Role" like 'NO_PWDEXP\NO_PWDREQD' , 'No Password',
if("Role" like 'NO_PWDEXP\SMART' , 'Smart',
if("Role" like 'NO_PWDEXP\DELEGATE', 'Delegated')))))))) as ACCOUNTSTATUS,
"Display Name",
"Last Logon",
"Exhange Alias",
"E-Mail",
"Lync User",
Role,
DN,
City,
Today(),
TextBetween( DN, 'CN=', ',') as [TypeName],
TextBetween( DN, 'OU=', ',') as [SiteName],
TextBetween( DN, 'DC=', ',') as DC,
"Last login Time",
Status
FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE
Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2
;
Have you tried updating the number format on your field to 'MM hh:mm:ss'
Using your 2 rows of test data
Logon:
load * Inline [
Account , Last Logon , Last login Time, Interval difference
0109222J , 6/18/2015 12:55:40 PM , 2/21/2017 6:34:59 PM , 614(days) or 20.467 Month
0109227R , 2/26/2017 1:00:15 PM , 2/27/2017 1:00:15 PM , 1(day) or 0.033 Month
];
Final:
load
*,
deltamonths > 2 as flag
;
load
Account,
[Last Logon],
[Last login Time],
Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as t1,
Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as delta,
Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt')) as deltadays,
Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 as deltamonths
Resident Logon;
DROP Table Logon;
.
try to add a where filter on your data
maybe you have to change the format code, orange
.....
.....
FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE
Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2
;
.
LOAD
"Pre-W2K Name", (Account)
if ("Pre-W2K Name" like 'User*' , 'USER',
if("Pre-W2K Name" like 'ADM*' , 'ADM',
if("Pre-W2K Name" like 'BAL*' , 'BAL',
if("Pre-W2K Name" like 'GEN*' , 'GEN',
if("Pre-W2K Name" like 'VC*' , 'VC',
if("Pre-W2K Name" like 'ADP*' , 'ADP',
if("Pre-W2K Name" like 'ROOM_*' , 'Room',
if("Pre-W2K Name" like 'FORMATION*' , 'FORMATION',
if("Pre-W2K Name" like 'SVC*', 'SVC'))))))))) as ACCOUNTGROUP,
if("Role" like 'DISABLED' , 'DISABLED',
if("Role" like 'DISABLED\NO_PWDEXP' , 'Disabled and Expired Password',
if("Role" like 'DISABLED\NO_PWDEXP\NO_PWDREQD' , 'Disabled, Password Expired and no password required',
if("Role" like 'NO_PWDEXP' , 'No Password Expired',
if("Role" like 'NO_PWDEXP\ENCRYPT' , 'Encrypted',
if("Role" like 'NO_PWDEXP\NO_PWDREQD' , 'No Password',
if("Role" like 'NO_PWDEXP\SMART' , 'Smart',
if("Role" like 'NO_PWDEXP\DELEGATE', 'Delegated')))))))) as ACCOUNTSTATUS,
"Display Name",
"Last Logon",
"Exhange Alias",
"E-Mail",
"Lync User",
Role,
DN,
City,
Today(),
TextBetween( DN, 'CN=', ',') as [TypeName],
TextBetween( DN, 'OU=', ',') as [SiteName],
TextBetween( DN, 'DC=', ',') as DC,
"Last login Time",
Status
FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE
Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2
;
.