Ads by Google
Most Popular
Home Network Sections
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.
Code:
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:
<LPHEADER> <HTML> <HEAD> <font size=+1>DHCP report</font> </HEAD> <BODY> <TABLE border=1> <TR> <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> </tr> </LPHEADER> <LPBODY> <TR> <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> </TR> </LPBODY> <LPFOOTER> </TABLE> </LPFOOTER> </BODY> </HTML>
Home Network Categories