Solved

Parsing and extracting fields from DHCP logs with Powershell

Posted on 2014-09-08
40
729 Views
Last Modified: 2014-09-28
I am trying to take a Windows Server DHCP log and be able to extract specific fields from it like IP Address, MAC Address etc. and write this data to another file. However when I read the log with Powershell after converting to csv the log is not listing the fields. In the actual csv opened with Excel the fields are there. I also tried using Get-Content and assigning the results to a variable and piping to GM but that did not list any members of the csv file. Please include specific code examples.

How can I accomplish this?

Thx
0
Comment
Question by:wayy2be
  • 19
  • 18
40 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 40311250
Do you have an example of the log ?

If you could share it, that might make it easier.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40311286
You might need to do the parsing with the Microsoft log parser utility and then read that into your Powershell script.
http://www.microsoft.com/en-us/download/details.aspx?id=24659
0
 

Author Comment

by:wayy2be
ID: 40347492
Sorry guys I have been away on holiday. I will try to post a sample of the logs.  Thanks
0
 

Author Comment

by:wayy2be
ID: 40347748
Here is what the log looks like:

ID,Date,Time,Description,IP Address,Host Name,MAC Address,User Name, TransactionID, QResult,Probationtime, CorrelationID,Dhcid.
24,05/09/14,00:00:44,Database Cleanup Begin,,,,,0,6,,,
25,05/09/14,00:00:44,0 leases expired and 0 leases deleted,,,,,0,6,,,
25,05/09/14,00:00:44,0 leases expired and 0 leases deleted,,,,,0,6,,,
24,05/09/14,00:14:00,Database Cleanup Begin,,,,,0,6,,,
25,05/09/14,00:14:00,0 leases expired and 0 leases deleted,,,,,0,6,,,
25,05/09/14,00:14:00,0 leases expired and 0 leases deleted,,,,,0,6,,,
30,05/09/14,00:16:32,DNS Update Request,192.168.5.210,host1.abc123.org,,,0,6,,,
11,05/09/14,00:16:32,Renew,192.168.5.210,host1.abc123.org,180373D6CF36,,2852268423,0,,,
32,05/09/14,00:16:32,DNS Update Successful,192.168.5.210,host1.abc123.org,,,0,6,,,

I want to be able to pull out parts of this log like mac address, host name etc. What is the best way to do this in PS?
Thanks
0
 
LVL 29

Expert Comment

by:becraig
ID: 40347790
ok so pipe in the log using import-csv


Open in new window

import-csv file.log | select-object  "Mac Address", "host name", "IP Address"
0
 

Author Comment

by:wayy2be
ID: 40347863
Thanks for the reply. Here is what I get:


Mac Address                             host name                               IP Address
--------------------                          ------------------                             ----------------

Only headings no data.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40347871
I have run the same command and get values where values exist (which might indicate the values do not exist for some entries.  (e.g. ,,,, would indicate empty columns since the data is comma separated).

PS C:\Users\Administrator\Desktop>  import-csv file.log | select-object  "Mac Address", "host name", "IP Address"

MAC Address                             Host Name                               IP Address
-----------                             ---------                               ----------






                                        host1.abc123.org                        192.168.5.210
180373D6CF36             host1.abc123.org                        192.168.5.210
                                        host1.abc123.org                        192.168.5.210

Open in new window


As you will notice above only one entry exists for the MAC address and there are no values for the first rows for those specific columns.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40347873
We can also add specific filters if needed and that way we will only return columns where specific data exists .
0
 

Author Comment

by:wayy2be
ID: 40347906
I see the host name and IP address in the log but PS just returns the headings and scrolls down a page before going back to the PS command prompt.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40347921
To be frank that is not possible, the values you expect should be host name and ip may be under other headings.

Here is an easy way to prove that some columns are simply EMPTY

import-csv file.log | select-object  * | ft

Open in new window


If you have a specific query you want to run we can do that but you cannot expect the command to return results for columns where none exist.
0
 

Author Comment

by:wayy2be
ID: 40347933
Using that command on the same log it returns:
Microsoft DHCP Service Activity Log                                                                                  
-----------------------------------                                                                                  
Event ID  Meaning                                                                                                    
00    The log was started.                                                                                           
01    The log was stopped.                                                                                           
02    The log was temporarily paused due to low disk space.                                                          
10    A new IP address was leased to a client.                                                                       
11    A lease was renewed by a client.                                                                               
12    A lease was released by a client.                                                                              
13    An IP address was found to be in use on the network.                                                           
14    A lease request could not be satisfied because the scope's address pool was exhausted.                         
15    A lease was denied.                                                                                            
16    A lease was deleted.                                                                                           
17    A lease was expired and DNS records for an expired leases have not been deleted.                               
18    A lease was expired and DNS records were deleted.                                                              
20    A BOOTP address was leased to a client.                                                                        
21    A dynamic BOOTP address was leased to a client.                                                                
22    A BOOTP request could not be satisfied because the scope's address pool for BOOTP was exhausted.               
23    A BOOTP IP address was deleted after checking to see it was not in use.                                        
24    IP address cleanup operation has began.                                                                        
25    IP address cleanup statistics.                                                                                 
30    DNS update request to the named DNS server.                                                                    
31    DNS update failed.                                                                                             
32    DNS update successful.                                                                                         
33    Packet dropped due to NAP policy.                                                                              
34    DNS update request failed.as the DNS update request queue limit exceeded.                                      
35    DNS update request failed.                                                                                     
50+    Codes above 50 are used for Rogue Server Detection information.                                               
QResult: 0: NoQuarantine                                                                                             
ID                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
15                                                                                                                   
30                                                                                                                   
10                                                                                                                   
32                                                                                                                   
30                                                                                                                   
13                                                                                                                   
32                                                                                                                   
30                                                                                                                   
10                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
30                                                                                                                   
11                                                                                                                   
32                                                                                                                   
24                                                                                                                   
18                                                                                                                   
25                                                                                                                   
25                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
24                                                                                                                   
16                                                                                                                   
25                                                                                                                   
25                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25                                                                                                                   
24                                                                                                                   
25                                                                                                                   
25

Open in new window

0
 
LVL 29

Expert Comment

by:becraig
ID: 40347936
So at this point I am assuming the log you gave me was not an accurate sample of what you are trying to parse ?


The file I am looking at based on your comment has a first row as below:


ID,Date,Time,Description,IP Address,Host Name,MAC Address,User Name, TransactionID, QResult,Probationtime, CorrelationID,Dhcid.
0
 

Author Comment

by:wayy2be
ID: 40347939
No it is the same exact log file, that is what is so weird. I get two completely different results.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40347943
Can you please run the command below so we can be sure we are working on the same file:

import-csv file.log | get-member

Open in new window

0
 

Author Comment

by:wayy2be
ID: 40347944
TypeName: System.Management.Automation.PSCustomObject

Name                                MemberType   Definition                                                        
----                                ----------   ----------                                                        
Equals                              Method       bool Equals(System.Object obj)                                    
GetHashCode                         Method       int GetHashCode()                                                  
GetType                             Method       type GetType()                                                    
ToString                            Method       string ToString()                                                  
Microsoft DHCP Service Activity Log NoteProperty System.String Microsoft DHCP Service Activity Log=Event ID  Meaning
0
 

Author Comment

by:wayy2be
ID: 40347945
The files are .log and appear in Windows as text files
0
 
LVL 29

Expert Comment

by:becraig
ID: 40347959
Ok so we are definitely not working with the same file, or the file has "Microsoft DHCP Service Activity Log " as the first line.

If that is the case then all we need to do is this.
$file = "file.log"; $filedata = gc $file | select-object -skip 1 | out-file wocomment.csv
import-csv wocomment.csv | select-object "Mac Address", "host name", "IP Address"
0
 

Author Comment

by:wayy2be
ID: 40348064
Still no data:

WARNING: One or more headers were not specified. Default names starting with "H" have been used in place of any missing headers.

Mac Address                                                  Host Name                                                    IP Address                                                
-----------                                                  ---------                                                    ----------

Bizarre!

Hey Aikimark - where is the snippet? That would have helped to see it.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40348065
Can you please post exactly what command you are running.

Your output says headers not specified which indicates clearly you are doing something I am not indicating in my post.

This was my suggestion simply::
$file = "file.log"; $filedata = gc $file | select-object -skip 1 | out-file wocomment.csv
import-csv wocomment.csv | select-object "Mac Address", "host name", "IP Address" 

Open in new window

0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:wayy2be
ID: 40348066
I think the problem is being caused based on the structure of the log file.
0
 

Author Comment

by:wayy2be
ID: 40348067
The command I ran is the one you provided:

$file = "file.log"; $filedata = gc $file | select-object -skip 1 | out-file wocomment.csv
 import-csv wocomment.csv | select-object "Mac Address", "host name", "IP Address"
0
 
LVL 29

Expert Comment

by:becraig
ID: 40348068
Can you give me an actual copy of the log right up until the line with the headers ?

e.g.

Microsoft DHCP Service Activity Log
ID,Date,Time,Description,IP Address,Host Name,MAC Address,User Name, TransactionID, QResult,Probationtime, CorrelationID,Dhcid.

Open in new window


I am thinking there are other lines that need to be skipped before we read the headers but need to be sure.
0
 

Author Comment

by:wayy2be
ID: 40348071
Microsoft DHCP Service Activity Log


Event ID  Meaning
00      The log was started.
01      The log was stopped.
02      The log was temporarily paused due to low disk space.
10      A new IP address was leased to a client.
11      A lease was renewed by a client.
12      A lease was released by a client.
13      An IP address was found to be in use on the network.
14      A lease request could not be satisfied because the scope's address pool was exhausted.
15      A lease was denied.
16      A lease was deleted.
17      A lease was expired and DNS records for an expired leases have not been deleted.
18      A lease was expired and DNS records were deleted.
20      A BOOTP address was leased to a client.
21      A dynamic BOOTP address was leased to a client.
22      A BOOTP request could not be satisfied because the scope's address pool for BOOTP was exhausted.
23      A BOOTP IP address was deleted after checking to see it was not in use.
24      IP address cleanup operation has began.
25      IP address cleanup statistics.
30      DNS update request to the named DNS server.
31      DNS update failed.
32      DNS update successful.
33      Packet dropped due to NAP policy.
34      DNS update request failed.as the DNS update request queue limit exceeded.
35      DNS update request failed.
50+      Codes above 50 are used for Rogue Server Detection information.

QResult: 0: NoQuarantine, 1:Quarantine, 2:Drop Packet, 3:Probation,6:No Quarantine Information ProbationTime:Year-Month-Day Hour:Minute:Second:MilliSecond.

ID,Date,Time,Description,IP Address,Host Name,MAC Address,User Name, TransactionID, QResult,Probationtime, CorrelationID,Dhcid.
0
 

Author Comment

by:wayy2be
ID: 40348072
I tried skipping around like 35, 40 etc. but same result
0
 
LVL 29

Expert Comment

by:becraig
ID: 40348090
It is line 32

I am looking at just doing this from first match to EOF but try this:
$file = "file.log"; $filedata = gc $file | select-object -skip 32 | out-file wocomment.csv
import-csv wocomment.csv | select-object "Mac Address", "host name", "IP Address"

Open in new window

0
 

Author Comment

by:wayy2be
ID: 40348104
That works!  I didn't try 32 LOL :-)
0
 
LVL 29

Expert Comment

by:becraig
ID: 40348105
Ok so here is another option since ID is the first column.

$found = $false
gc file.log | % { if ($_ -match '^["ID,"]') { $found = $true };
if ($found) { $_ >> wocomment.csv} }
import-csv wocomment.csv | select-object "Mac Address", "host name", "IP Address"

Open in new window

0
 
LVL 29

Expert Comment

by:becraig
ID: 40348108
The logic behind the above is really simple I set a condition which evaluates to false until we find the first instance of ID, then simply push the rest to the csv then process the csv file.
0
 

Author Comment

by:wayy2be
ID: 40348112
Since there are several log files, one for each day, how can I tell the script to grab each one and parse and then combine to one list?  The name of the logs is DhcpSrvLog-Tue.log, DhcpSrvLog-Wed.log etc.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40348151
Are they the only files in the folder ?

If so we can just do a loop and add all the content to one file with the headers.
Not the most attractive
e.g.

$headerval = "ID,Date,Time,Description,IP Address,Host Name,MAC Address,User Name, TransactionID, QResult,Probationtime, CorrelationID,Dhcid."
$headerval | Out-File fulllog.log
gci logfolder | ? {$_.extension -eq ".log"} | %
{
$found = $false
gc $_ | % { if ($_ -match '^["ID,"]') { $found = $true }; if ($found) { $_ >> logsl.log } 
gc logsl.log | select-object -skip 1 | out-file fulllog.log -append
Remove-Item logsl.log -Force
}
}

Open in new window

0
 

Author Comment

by:wayy2be
ID: 40348192
They would be in the same folder I think, but there may be different log files in there too.  Not sure I would have to check when I return to the office.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40348200
Then we could make a minor modification to read only those files.

$headerval = "ID,Date,Time,Description,IP Address,Host Name,MAC Address,User Name, TransactionID, QResult,Probationtime, CorrelationID,Dhcid."
$headerval | Out-File fulllog.log
gci logfolder | ? {$_.name -like "dhcp*.log"} | %
{
$found = $false
gc $_ | % { if ($_ -match '^["ID,"]') { $found = $true }; if ($found) { $_ >> logsl.log } 
gc logsl.log | select-object -skip 1 | out-file fulllog.log -append
Remove-Item logsl.log -Force
}
}

Open in new window

0
 

Author Comment

by:wayy2be
ID: 40348217
Got this when I ran it:
cmdlet ForEach-Object at command pipeline position 3
Supply values for the following parameters:
Process[0]:
0
 
LVL 29

Accepted Solution

by:
becraig earned 500 total points
ID: 40348225
Ok I see why, here you go:

$headerval = "ID,Date,Time,Description,IP Address,Host Name,MAC Address,User Name, TransactionID, QResult,Probationtime, CorrelationID,Dhcid."
$headerval | Out-File fulllog.log
gci logger | ? {$_.name -like "dhcp*.log"} | % {
$logfile = $_.fullName
$found = $false
write-host "gc $logfile"
gc $logfile | % { if ($_ -match '^["ID,"]') { $found = $true }; if ($found) { $_ | out-file logsl.log -append} 
}
gc logsl.log | select-object -skip 1 | out-file fulllog.log -append
write-host "logs processed"
Remove-Item logsl.log -Force
}

Open in new window

0
 

Author Comment

by:wayy2be
ID: 40348550
Thanks for your help on this. I get this error (What is logger?):

gci : Cannot find path 'C:\logs\dhcp\logger' because it does not exist.
At line:3 char:1
+ gci logger | ? {$_.name -like "dhcp*.log"} | % {
+ ~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (C:\logs\dhcp\logger:String) [Get-ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand
0
 
LVL 29

Expert Comment

by:becraig
ID: 40348803
That was my folder name for testing, you need to replace it with your path to your logs folder.
0
 

Author Comment

by:wayy2be
ID: 40349082
Awesome!  Works perfectly!  Many thanks for your patience and help with this!
0
 
LVL 29

Expert Comment

by:becraig
ID: 40349266
Np Happy to help.


Also remember logparser is a relaly cool tool for working with logs as well, it has a slight learning curve but it is powerful.
http://www.stevebunting.org/udpd4n6/forensics/logparser.htm
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now