3lastlogon_insert.sh

This script includes conversion of Windows NTTE/NT File time to standard time/data using Unix utils–

#!/bin/bash
oldIFS=$IFS
IFS=$'\n'

export LC_ALL='C'

#delete temp files
rm lastlogon_tmp.txt >/dev/null
rm batch_sql/3lastlogon_insert.sql >/dev/null

#start insert sql file
echo use cu_exchange\; >> 3lastlogon_insert.sql

#remove line endings, sort, etc.
cat lastlogon_exchange.txt | grep -v "DN,lastLogon" |tr -d '\r' >> lastlogon_tmp.txt


for i in $(cat lastlogon_tmp.txt);
do
        j=$(echo $i|sed -e "s#'#\\\'#g"|tr -d '"')      #clean up "'" for mysql
        dname=$(echo $j|rev|cut -f 2- -d ","|rev)
        
        #Constant, the Unix Epoch Time in NS since 1/1/1601
        epoch_in_ns=116444735995904000
        time_in_ns=
        time_in_ns=$(echo $j|rev|cut -f 1 -d ","|rev)
        if [ -z $time_in_ns ];then time_in_ns=0;fi
        #echo $time_in_ns
        
        time_since_epoch_in_s=
        if [ $time_in_ns -gt $epoch_in_ns ];then time_since_epoch_in_s=$(( (( $time_in_ns - $epoch_in_ns )) / 10000000 ));fi
        #echo $time_since_epoch_in_s

        if [ $time_since_epoch_in_s ]; then lastLogon=$(date -j -f "%s" "$time_since_epoch_in_s" "+%Y-%m-%d");fi
        #echo $lastLogon
        #echo $dname
        if [ -z $lastLogon ]; then lastLogon='0000-00-00';fi
        #echo $lastLogon
        #Insert statements
        echo update exchange_info set lastLogon\=\'$lastLogon\' where dname\=\'$dname\'\; >> 3lastlogon_insert.sql
done

rm lastlogon_tmp.txt
mv 3lastlogon_insert.sql batch_sql/

IFS=$oldIFS