Logo
Head
«
»

LastLogonTimestamp in lesbares Format bringen mit Excel

Um alte Leichen im AD zu finden wollte ich mir eine Liste aller LastLogonTimestamps aus dem AD ziehen und diese dann im Excel umrechnen. Das habe ich früher schon mit Unix-Timestamps so gemacht und hat immer prima geklappt. Leider macht Windows da nicht so einfach mit!

Das Exportieren der Daten kann mit “csvde” schnell und einfach gemacht werden:

csvde -f export.csv -r "(objectclass=user)" -l "cn, sn, samaccountname, lastLogonTimestamp"

Die CSV-Datei kann nun einfach in Excel importiert werden.

Der Timestamp im AD-Attribut “lastLogonTimestamp” ist leider kein gewöhnlicher UnixTimestamp der sich einfach mit Excel umrechnen lässt. Dieser Timestamp ist ein 8byte langer Wert basierend auf den Nanosekunden seit dem 01.01.1601 geteilt durch 100. Erschwerend dazu kommt, dass Excel nicht mit Daten vor dem 01.01.1900 rechnen kann.

Da ich selbst bei Mathe gerne dem geschehen ausserhalb des Klassenzimmers zugeschaut habe war ich auf Hilfe aus dem Web angewiesen ;-) Bei einem benachbarten Blogger habe ich dann endlich eine Formel gefunden:

=IF(C2>0,C2/(8.64*10^11) - 109205,"")

Wobei C2 die Zelle mit dem Timestamp ist. Eine genaue Erklärung der Formel findet ihr unter der Quellenangabe.

Für die deutsche Version von Excel muss die Formel wie folgt verwendet werden:

=WENN(C2>0;C2/(8.64*10^11) - 109205;"")

Kleiner Hinweis: Der Timestamp wird nur aktualisiert wenn er zum Zeitpunkt des letzten Logins älter als 2 Wochen war! Falls das nicht zutrifft bleibt der alte Wert bestehend! Wer aber nur auf der Suche nach alten User- oder Computerleichen ist sollte damit keine Probleme haben.

Eine andere Möglichkeit ist wie erwähnt über VB-Scripte. Ein gutes Beispiel dafür habe ich hier gefunden: rlmueller.net

Quelle Formel: SysAdmin-Stuff @ Blogspot

Tags: , ,

This entry was posted on Dienstag, März 31st, 2009 at 14:20 and is filed under IT. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “LastLogonTimestamp in lesbares Format bringen mit Excel”

  1. Februar 17th, 2010 at 17:52

    Uwe says:

    Hi, als kleine Ergänzug könnte dieser Tip noch ganz gut sein:

    Wenn man den Lastlogon[TimeStamp] als Zahl vorliegen hat
    z.b. 129101978813806888

    Dann kann man das mit der Nachfolgenden Funktion in ein
    Datum umwandeln (VBS)

    ‘————————————–
    ‘LOGON(TIMESTAMP) UMRECHNEN
    ‘————————————–
    function datetime(str_datetime)

    ‘Variable
    dim strdate

    ‘Prüfen ob ein Wert oder eine Zahl unglich 0 vorhanden ist
    if len(str_datetime) > 1 then

    ‘Ergebnis berechnen
    strdate = ((((str_datetime / 10000000) / 3600) / 24) -109207)

    ‘Für Excel berechnen (,2 bedeutet nur Datum ausgeben)
    datetime = formatDateTime(strdate,2)

    ‘Fehler zurück geben
    else

    datetime = 0
    end if

    end function

    Gruß
    Uwe

  2. Juli 28th, 2010 at 11:00

    Peter says:

    Kleiner Fehler in der deutschen Version
    Müsste heißen
    =WENN(C2>0;C2/(8,64*10^11) – 109205;”")

  3. Juli 28th, 2010 at 15:04

    ionee says:

    Es kommt auf die regionalen Einstellungen an – für Deutsch (Schweiz) ist meine Version korrekt. Für Deutsch (Deutschland) wird Deine Version passen. Danke für die Info!

  4. August 23rd, 2011 at 21:57

    Rolfchen W. says:

    Hey, danke für den Tipp – genau so eine (einfache) Umwandlung habe ich gesucht. Besonders auch ohne dabei auf ungewöhnliche Fremdsoftware zurückgreifen zu müssen. Prima.

Leave a Reply

Comment Spam Protection by WP-SpamFree