Converting Active Directory lastlogontimestamp To Service-Now format

[garyopela]

So, recently I wrote an integration to pull in all of our computer information from LDAP to pull into Service-Now to create an all-data comparison report. The report allows us to pull up a quick health report of all of our computers. This pulls in Last Login from SCCM (Configuration Manager), Last Login from AD, and will soon pull in Last Anti-Virus Scan data.

It provides a report, and will show green/yellow/red for the background of the cells for each of the three metrics gathered based on how long it has been since a computer has reported to each of the three systems. We can see from this that if only one of the sources is shown as red, then we need to dispatch a technician. If they are all three red, then it is likely that that machine has been removed from the network.

In the future, we will add in automated IMAC and Incident generation based on the data in the report.

So, now that the background of the report has been mentioned, I ran into one slight issue. The lastlogontimestamp in AD is a very odd format. It is measured in Epoch seconds, but not since 1970 like most systems. For some reason, Microsoft decided to begin their epoch at the year 1601 or so, so the existing date conversions that are out there for UNIX Epoch time won't work. After much internet research, I came up with the following in my transform map:

var dat = LDAPtimeToDate(source.u_lastlogontimestamp);
answer = getDate(dat);

function getDate(date1){
 return(date1.getFullYear() + "-" + (date1.getMonth()+1) + "-" + date1.getDate());
}

function LDAPtimeToDate(LDAPstring)
{
               var iYearsFrom1601to1970 = 1970 - 1601;
               var iDaysFrom1601to1970 = iYearsFrom1601to1970 * 365;

               iDaysFrom1601to1970 += parseInt(iYearsFrom1601to1970 / 4); // leap years
               iDaysFrom1601to1970 -= 3; // not sure exactly why this is 3

               var iSecondsFrom1601to1970 = iDaysFrom1601to1970 * 24 * 60 * 60;

               var iTotalSecondsSince1601 = parseInt(LDAPstring/ 10000000);

               var iTotalSecondsSince1970 = iTotalSecondsSince1601 - iSecondsFrom1601to1970;
               var oFinishedDate = new Date(iTotalSecondsSince1970 * 1000);
               return oFinishedDate;
}

So what we do, is you feed the lastlogintimestamp from AD to the LDAPtimeToDate function, then run that through the getDate function, and voila! It is returned into a useable format for Service-Now.

I want to be clear that I didn't come up with this code on my own, I googled and found it. I just wanted to post the final product here for you guys, so maybe I can save you all some time too. I did have to tweak it some to get it to fit properly in here, but the general gist of it was created by many other people.