• Monitoring Windows DHCP server leases with a simple bat file

    Every time I say "simple bat file" I get flashbacks to my old shop teacher Mr. Lambert. He was a brilliant wood craftsman and we were all simply a bunch of clowns. While he was instructing us on how to make something in the perfect way that he did it, he would always start by saying, "you make yourself a jig". In a way, a bat file feels to me like a jig. Sure there are more eloquent ways of doing it and you could go nuts and make a Frankenstein-ish monster, but sometimes it's just easier to write a bat file quick. In the interest of creating usable content to get this going, I offer some of my simple home network scripts and bat files. In this case, monitoring Windows server DHCP leases. Useful where you are paranoid that someone may have gotten past your wireless security... The caveat here being that this works for sure on Windows Server 2003, I haven't tested on 2008 yet as I have access to none.

    You will need two additional piece of softwares for this to work, and that's Microsoft's Log Parser and blat. If you haven't seen it, Log Parser allows you to use SQL statements to search log files, event files, etc. It's powerful, but we're not even using it that intelligently for this bat file. Blat simply let's you email out. With that, on to the bat file. You will probably need to do a little cleanup of spacing and formatting when you paste this in to a bat file.

    REM These are fairly self-explanatory, dhcphome is the dhcp log location the server
    REM Leave a comment about any others if you can't figure it out.
    set ScriptHome=C:\admintools\dhcpmonitor\
    set dhcphome=C:\WINDOWS\system32\dhcp\
    set LogParser="C:\Program Files\Log Parser 2.2\LogParser.exe"
    set blat=C:\admintools\blat262\full\blat.exe
    set adminemail=you@yourdomain.com
    set emaildomain=@yourdomain.com
    set SMTP=smtp.yoursmtpserver.net
    REM Clear files from last run
    pushd %ScriptHome%
    del *.csv
    del *.htm
    del *.log
    REM Output day of the week, this is important because DHCP logs like DhcpSrvLog-Wed.log
    date /t > day.txt
    REM This simply takes the first word in the output of date /t and sets it as the day
    REM you'll have to run this by 11:59pm of the current day, use a scheduled task...
    For /F "Tokens=1" %%a in (day.txt) Do Set Day=%%a
    REM This simply copies the DHCP log to the scripthome location for analysis
    copy %dhcphome%DhcpSrvLog-%Day%.log %ScriptHome%
    REM This is the first level of data manipulation
    REM Basically it looks for nulls in the DHCP log and gets rid of them by piping non-nulls to another file
    REM If you look at the DHCP log you'll see why we have to do this.
    for /f "tokens=1,2,3,4,5,6,7,8 delims=," %%a  in (%ScriptHome%DhcpSrvLog-%Day%.log) do if [%%e] NEQ [] echo %%b,%%c,%%d,%%e,%%f,%%g >> text.csv
    REM Another manipulation, I could give a crap less about DNS Updates
    for /f "tokens=1,2,3,4,5,6 delims=," %%a  in (%ScriptHome%text.csv) do if "%%c" NEQ "DNS Update Request" echo %%a,%%b,%%c,%%d,%%e,%%f >> text1.csv
    REM Another variation of above
    for /f "tokens=1,2,3,4,5,6 delims=," %%a  in (%ScriptHome%text1.csv) do if "%%c" NEQ "DNS Update Successful" echo %%a,%%b,%%c,%%d,%%e,%%f >> text2.csv
    REM Lastly to make this a nice email format I use log parser to get the entire contents
    REM of my latest manipulation and dump them in to an html template
    REM At this point I really could pull the MAC addresses and compare them to known MACs
    REM in a file, but I like seeing the activity on my small network, just this morning the DHCP report
    REM helped us find out Squeezebox Duet remote by telling me the last time it renewed it's address
    REM I was able to figure out what I was doing at the time and go look there
    %logparser% -i:csv -o:TPL -tpl:%ScriptHome%dhcp.tpl "SELECT * INTO '%ScriptHome%DHCPlog.htm' FROM text2.csv"
    REM  And then of course we email the report out to you.
    if exist %ScriptHome%DHCPlog.htm %blat% %ScriptHome%DHCPlog.htm -serverSMTP %SMTP% -f yourserver%emaildomain% -to %adminemail% -s "DHCP client report for %Day%"

    Lastly, we also need the template file for this to work. In the Log Parser line you see this needs to be named dhcp.tpl. Here are the contents of that:

    HTML Code:
    <font size=+1>DHCP report</font>
    <TABLE border=1>
    <TD width=5%><b>Date</b></TD>
    <TD width=5%><b>Time</b></TD>
    <TD width=10%><b>Description</b></TD>
    <TD width=5%><b>IP address</b></TD>
    <TD width=10%><b>Hostname</b></TD>
    <TD width=10%><b>MAC Address</b></TD>
    <TD width=5%>%Date%</TD>
    <TD width=5%>%Time%</TD>
    <TD width=10%>%Description%</TD>
    <TD width=5%>%IP Address%</TD>
    <TD width=10%>%Host Name%</TD>
    <TD width=10%>%MAC Address%</TD>