Storing DHCP log file in SQL server database

Question

Storing DHCP log file in SQL server database

Parthipan-244146

SSCrazy

Points: 2178
More actions
October 19, 2008 at 7:01 am

#194767

Hi All,
Is there anyway to store DHCP log file in SQL server database and the procedure for automating it?
Any third party utility to do this?
BR,
Parthipan.S

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

do you mean from the Windows DHCP log? What's the format? I'm guessng it's text. SSIS might be the best solution to automate this. Build a package that reads the log from the server and then load it into the database. If you can separate things out and normalize the daa, great. If not, I'd drop it into a single varchar column able. Then you can easily stage it there and always transform it later.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

Parthipan (10/19/2008)
Is there anyway to store DHCP log file in SQL server database and the procedure for automating it?

Yes, but you're going to have to parse it using a script or some such. There's a lot of info to consider, especially when you consider Windows 2000/2003 DHCP doesn't just handle DHCP but also does DNS registration.

Parthipan (10/19/2008)
Any third party utility to do this?

Not that I am aware of. Also, if you're just looking to insert each line as text, SSIS would work as Steve put forward. But if you're looking to do more than that, if you're looking to get the events, the MAC addresses, etc., you're going to have to put some time into parsing the logs. Log Parser might be able to do it, but I haven't seen anything using it, unlike with Exchange, IIS, etc.

K. Brian Kelley
@kbriankelley

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 3

Steve/Brian,

Thanks for your information..

Yes..I am planning to import Windows DHCP log text file in SQL database. I am very concerned about parsing the log file so that data can be stored in different columns.

BR,

Parthi

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 4

Parthipan,

You could look into Ms LogParser utility,

I find it an amazing tools and its core exsitence is there to read, analyze and manipulate logfiles

It can also be used to make a smooth insert in any sqlserver database

check out :http://www.logparser.com

Its pretty self explaining and there are examples given with it.

Combine this with powershell and you can build a fast hell of an application just for this purpose.

wkr,

Eddy

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 5

I have doctored this script to collect the windows event logs from my SQL boxes it uses log parser and is pretty intuitive:

http://www.sqlservercentral.com/scripts/31350/

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

andrewkane17 (10/21/2008)

I have doctored this script to collect the windows event logs from my SQL boxes it uses log parser and is pretty intuitive:

http://www.sqlservercentral.com/scripts/31350/%5B/quote%5D

Log Parser may be able to do it. However, the DHCP logs are some of the most convoluted I've seen on the Windows side. I'll have to take a look when I get back to work.

K. Brian Kelley
@kbriankelley

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 7

Hi All,

Thanks for your valuable information.

let me write down my understanding to get clarified again

* using DTS package we can automate the task of storing DHCP logs on SQL server database

* any way manual intervention is needed to parse the log file using Logparser utility to store the data in different columns

Thanks again..

BR,

Parthipan