Solved

Parsing and extracting fields from DHCP logs with Powershell

Posted on 2014-09-08
40
706 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 28

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 28

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 28

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 28

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 28

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 28

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 28

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 28

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 28

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 28

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 28

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 28

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 28

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 28

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 28

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 28

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 28

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 28

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

17 Experts available now in Live!

Get 1:1 Help Now