Delete old unnecessary DHCP reservations of IP addresses

Sometimes large organizations need to audit free IP addresses. Users come to the place / leave / leave, IP addresses remain reserved, although no one is behind them. The search for such "dead souls" and do.

Let's look at an example of how reports from MS DHCP logs are obtained.

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 \\ dhcp_server \ c $ \ Windows \ System32 \ dhcp folder are the DHCP server logs. 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 get all the dhcp lease renew update events:

cmd

C:

cd c:\tmp\

1) At the first stage, we highlight significant lines. List all the * .log file names 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 it into one file by concatenation. If, for example, you need to verify that the address has not been used for a whole month, six months, etc., then you need to store the text2.csv files for this period and glue it before the next operation.

3) report generation: This will

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"

result in a text2.html file in which you can see “live” computers receiving addresses through 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