topic Re: Active directory- last login & large numbers in QlikView

Active directory- last login & large numbers

adamdavi3s — Wed, 28 Aug 2013 10:26:22 GMT

Hi All,

I am trying to convert the last login time from AD into a date.

Obviously I appreciate its held in integer8, but that conversion seems to work OK.

The issue I have is that for some reason it is only working sometimes.... it has to be the data format but I can't figure it out at all!

Here are the various things I have tried, and below is a snapshot of the data which shows it weirdly working for some but not others:

LOAD DISTINCT

        DisplayName,

        SAMAccountName,

        LastLogon,

        num(num#(LastLogon)) as num,

        left(LastLogon,9) as left,

        right(LastLogon,9) as right,

        left(LastLogon,9) & right(LastLogon,9) as combined,

        evaluate(trim(LastLogon)) as lastlogon,

        Div(LastLogon, 1e10) & Mod(LastLogon, 1e10) As aLongNumber,

        Timestamp(evaluate(trim(LastLogon)) / 864000000000 - 109207) as last_logon,

        Timestamp(evaluate(LastLogontimestamp) / 864000000000 - 109207) as last_logon_timestamp

Re: Active directory- last login & large numbers

mr_janne — Wed, 28 Aug 2013 10:42:08 GMT

Hi Adam,

"The lastLogon attribute is not designed to provide real time logon information.
With default settings in place the lastLogontimeStamp will be 9-14 days behind the current date.

Source: “The LastLogonTimeStamp Attribute” – “What it was designed for and how it works” - Ask the Directory Services Team - Sit…

regards.janne

Re: Active directory- last login & large numbers

adamdavi3s — Wed, 28 Aug 2013 10:47:08 GMT

Hi Janne,

Thank you yes, I had read that but at this time we're not concerned as we're simply trying to identify users who have not logged in for over 90 days so the 9-14 day delay / disparity isn't too concerning

The concern here I believe is with the formatting of the data, unless that disparity is creating an un-convertable string?