We delete old unnecessary DHCP reservations of IP addresses

Sometimes in large organizations you need to conduct an audit of free IP-addresses. Users come to the site / leave / leave, IP addresses are reserved, although there is no one behind them. The search for such "dead souls" and do.

Consider an example of how reports are generated from MS DHCP logs.

The technology for generating reports on the use of address space consists of two parts:

1) separation of DHCP renew events and
')
2) SQL processing.

In the folder \\ dhcp_server \ c $ \ Windows \ System32 \ dhcp are the logs of the DHCP server. Their copies need to be collected in one folder on a local or network drive. For example, in c: \ tmp

From these files you can pull out all the renewal events dhcp lease renew:

cmd

C:

cd c:\tmp\

1) At the first stage, we select meaningful lines. Separate all the * .log files that you want to process, separated by commas. You can collect files in a few weeks by renaming them somehow.

c:\tmp>for /f "tokens=1,2,3,4,5,6,7,8 delims=," %a in (DhcpSrvLog-Mon.log,DhcpSrvLog-Tue.log,DhcpSrvLog-Wed.log,DhcpSrvLog-Thu.log,DhcpSrvLog-Fri.log,DhcpSrvLog-Sat.log,DhcpSrvLog-Sun.log) do if [%e] NEQ [] echo %b,%c,%d,%e,%f,%g >> text.csv

2) Add the header “Date, Time, Description, IP, Hostname, MAC” to the first line of the text2.csv file, fill in the remaining lines with renew events:

c:\tmp>echo Date,Time,Description,IP,Hostname,MAC >> text2.csv

c:\tmp>for /f "tokens=1,2,3,4,5,6 delims=," %a in (text.csv) do if "%c" EQU "Renew" echo %a,%b,%c,%d,%e,%f >> text2.csv

You can store text2.csv for each week and collect in one file by concatenation. If, for example, you need to check that the address was not used for a whole month, six months, etc., then you need to store text2.csv files for this period and glue them together before the next operation.

3) report generation:

c:\tmp >"c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" -i:csv -o:TPL -tpl:dhcp2.tpl "SELECT IP, hostname, mac, max(to_timestamp(add(add(Date, ' '),time), 'MM/dd/yy HH:mm:ss')) as timestamp, EXTRACT_TOKEN(ip, 0, '.') as octet1, EXTRACT_TOKEN(ip, 1, '.') as octet2, EXTRACT_TOKEN(ip, 2, '.') as octet3, EXTRACT_TOKEN(ip, 3, '.') as octet4, add(strrepeat('0',sub(3, to_int(strlen(octet4)))),octet4) as octet4lz, add(strrepeat('0',sub(3, to_int(strlen(octet3)))),octet3) as octet3lz INTO text2.html FROM text2.csv group by IP,mac,hostname order by octet1,octet2,octet3lz,octet4lz"

You will get the file text2.html, in which you can see the “live” computers that receive addresses via DHCP.

If you replace "-o: TPL -tpl: dhcp2.tpl" with "-o: CSV", "text2.html" with "b8.csv", then the output file can be imported into excel.

"c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" -i:csv -o:CSV "SELECT IP, hostname, mac, max(to_timestamp(add(add(Date, ' '),time), 'MM/dd/yy HH:mm:ss')) as timestamp, EXTRACT_TOKEN(ip, 0, '.') as octet1, EXTRACT_TOKEN(ip, 1, '.') as octet2, EXTRACT_TOKEN(ip, 2, '.') as octet3, EXTRACT_TOKEN(ip, 3, '.') as octet4, add(strrepeat('0',sub(3, to_int(strlen(octet4)))),octet4) as octet4lz, add(strrepeat('0',sub(3, to_int(strlen(octet3)))),octet3) as octet3lz INTO b8.csv FROM text2.csv group by IP,mac,hostname order by octet1,octet2,octet3lz,octet4lz"

LogParser download here:

www.microsoft.com/en-us/download/details.aspx?id=24659