AD-Anmeldedatum per csvde exportieren und in Excel darstellen

Active Directory speichert das Anmeldedatum eines Benutzers (bzw. eines Computers) in einem etwas eigenwilligen Format. Dieses gibt die Anzahl der 100-Nanosekunden-Intervalle an, die seit dem 1. Januar 1601 vergangen sind. Dies ergibt dann Werte wie 130104058290831818 (die technisch noch dazu in mehreren Einträgen hinterlegt sind, aber das führt an dieser Stelle zu weit).

Viele Anzeige-Werkzeuge konvertieren diese Einträge wieder in lesbare Daten. Nicht so csvde.exe, ein Dienstprogramm, das seit Windows 2000 in den Adminwerkzeugen für das AD enthalten ist. Hier bekommt man einfach den Wert im obigen Format zurück. Möchte man nun etwa in einer Excel-Übersicht ein lesbares Datum daraus machen, geht das folgendermaßen.

Tun wir so, als hätten wir Anmeldedaten mit folgendem csvde-Kommando exportiert:

csvde -u -f comp.txt -r "(objectClass=computer)" -l name,operatingSystem,lastLogonTimestamp

Die so erzeugte Ausgabedatei comp.txt lässt sich mit Excel öffnen. Hierbei ist es von Vorteil, wenn die Dateiendung “txt” lautet und nicht “csv”, denn so startet Excel den Import-Assistenten und man kann das Komma als Trennzeichen auswählen.

In Excels Import-Assistenten sollte man das Komma als Trennzeichen angeben, um die CSV-Datei zu öffnen.

Jedenfalls enthält die Spalte lastLogonTimestamp das Datum in der oben gezeigten Form. Um es lesbar zu machen, eignet sich – in einem deutschen Excel – die folgende Formel:

=WENN(D2>0;D2/(8,64*10^11)-109205;"")

Excel schreibt nun einen numerischen Wert in die Zielzelle. Diesen formatiert man als “Datum, kurz”, wenn das reine Datum ausreicht. Oder man nutzt ein benutzerdefiniertes Format, um das Datum mit der Uhrzeit auszugeben, etwa dieses hier:

TT.MM.JJJJ hh:mm:ss

Die Formel zeigt das AD-Anmeldedatum korrekt an.

Ein Hinweis noch zu dem Feld lastLogonTimestamp: Es gibt nicht (bzw. nicht unbedingt) das tatsächliche letzte Anmeldedatum zurück, sondern nur einen “ungefähren” Wert, der bis zu einer Woche alt sein kann. Das reicht für viele Auswertungen völlig aus. Der Wert dieses Feldes wird zwischen allen Domänencontrollern der Domäne repliziert und lässt sich daher einfach abfragen. Der “Schwesterwert” lastLogon hingegen speichert zwar immer das letzte Anmeldedatum, aber nur für den jeweiligen Domänencontroller, und er wird nicht repliziert – hierfür müsste man also alle DCs einer Domäne abfragen, um den “tatsächlichen” Anmeldezeitpunkt zu erhalten (auch bekannt als “real last logon date”).