Post reply

Storing DHCP log file in SQL server database

  • 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

  • October 19, 2008 at 11:27 am

    #886891

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • October 19, 2008 at 10:23 pm

    #886963

    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

  • October 20, 2008 at 2:57 am

    #887007

    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

  • October 20, 2008 at 12:06 pm

    #887266

    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

  • October 21, 2008 at 2:57 pm

    #887847

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

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

  • October 21, 2008 at 7:41 pm

    #887909

    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://qa.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

  • October 21, 2008 at 11:37 pm

    #887946

    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

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

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