Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37

    Unanswered: newbie questions

    I am pretty new to databases and was wondering whether you guys can help me out or point me in some sort of direction. I am working on my senior design project for college and my group decided to use SQL Server for our databasing needs. So far we installed SQL Server and created out database with permissions and all that stuff. So here is my question:

    As of right now we are talking with a company (sniffer software) to get access to some of there code. This program provides information such as host ip and mac address, destination ip and mac address, protocol, port number, bits in, bits out, etc. What we wanted to do was get access to where this information is stored in there program and then push it to our database so we can do analysis on the data. But aside from getting access to this information in the program (we looked into writing an application that does all this ourselves but we are on a time limit) I am confused how to set the database up so that we can have automatic updates to the database using the information we pull from the program. I did a search here and did not find anything and i have been looking on google and the such and haven't had any luck finding anything that will point me in the right direction. If any of you guys can help us out with information it would be greatly appreciated

    thanks
    Evan

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    If you have access to their soure code, you would have to add a procedure somewhere in to write the data it collects to the DB. the procedure would have to called each time the program identified a new packet.

    Alternatively, if the program stores the information in log form, you would need to write an app to parse the log file and then write the data to SQL.

    Both of these are non-trivial tasks, but the might prove very educational.

    Regards,

    hmscott

  3. #3
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37
    As a last resort we can do that. The program does export to a text file and i wouldn't have a problem writing a program to parse that data but that would be very inefficient since we are looking for almost realtime updating to the database. SO if we can get access to that information in the program then we can just create a simple procedure that will update the database whenever a certain instance happens (ex: new packet hits the sniffer)? I guess my question was in that procedure you would set a log in and password that would allow access to the database and then just use insert functions? Sorry if these are so trivial. I have installed Oracle and SQL databases but my actual SQL databasing skills are very low level, i am trying to learn but have had a hard time finding a decent web-based or text resource

    thanks

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    It depends on what language the source code is written in, but there are several ways to do this. I am most familiar with VB, so here goes.

    In T-SQL:
    Code:
    -- execute this code from Query Analyzer
    -- New Database called "Sniffer"
    CREATE DATABASE Sniffer
    GO
    
    -- Shift database context to the new database
    USE Sniffer
    GO
    
    -- Create a new table to store the packet entries
    -- You need to modify this table to suit your needs
    CREATE TABLE SnifferLog (
      SourceIP varchar(15) null,
      DestIP varchar(15) null,
      MoreData varchar(20) null
    )
    GO
    
    -- Create a stored procedure to add new entries
    -- to the newly created table
    CREATE PROCEDURE spAddSnifferEntry (
      @SourceIP varchar(15),
      @DestIP varchar(15),
      @MoreData varchar(20)
    )
    
    AS
    
    INSERT INTO SnifferLog (SourceIP, DestIP, MoreData)
    VALUES (@SourceIP, @DestIP, @MoreData)
    
    GO
    Now in VB (6.0, I have not really used .NET yet):
    Code:
    Dim oConn as ADODB.Connection
    Dim oComm as ADODB.Command
    
    Set oConn = New ADODB.Connection
    oConn.ConnectionString = "Provider=SQLOLEDB.1;" & _
       "Server=MyServer;Initial Catalog=Sniffer;" & _
       "UID=MyUser;PWD=MyPassword"
    
    oConn.Open
    
    oComm.Connection = oConn
    oComm.CommandText = "spAddSnifferEntry"
    oComm.Parameters.Refresh
    oComm.Parameters("@SourceIP") = sSourceIP
    oComm.Parameters("@DestIP") = sDestIP
    oComm.Parameters("@MoreData") = sMoreData
    
    oComm.Execute
    If you encapsulate the vb code into a DLL, you can create an ActiveX object which can be instantiated and called from just about any source code.

    I have not touched on a myriad of things that you may need to do (security) indexes, performance tuning, but this shoud give you an idea of what's ahead.

    Regards,

    hmscott

  5. #5
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37

    questions

    I found a program that i can use under the gpl. It is written in C/C++. I downloaded the source code and I am looking at it now and trying to make sense of it and where the information that i need is stored. If i create a dll like you said in vb how exactly does that work, do i have to call it in the code itself or is it an external file? I am new to this whole thing and i am still trying to figure out how to compile the source code.

    What would be easier for me to do? Once i find the information that i need would it be easier to insert a procedure right after the information executes in the code or to use the dll? Does the dll hold references to the information that i want to push to my database? Sorry but i am new to this whole thing and am quite confused

    thanks

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you are talking about a NAI Sniffer, it exports several file formats that can be easily imported into SQL Server without writing any code at all.

    The tab delimited format works splendidly, and requires nothing more complex than a BULK INSERT command or a simple DTS package.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37
    No the product that we are using is ethereal which is open source. We chose this program because it is free and other programs that we looked into would cost us upwards of a $1000 to get access to the source. We previously had a sniffer that outputted to a text file but that is not much help to us since we would like to do real time updating to our database with the network info the sniffer collects. If we exported to a text file it would work but be incredibly inefficient and wouldn't be an accurate portrayal of what was actually going on in the network. I have worked with importing cvs files into SQL but like i previously stated it doesn't really work for us for what we are looking to do.

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245

    Re: questions

    I'm not real familiar with C++, but in VB, if you first created the DLL, you would do the following:

    1. Register the DLL on your computer (using COM+ or regsrv32)

    2. Instantiate the object in code

    3. Make the appropriate call

    Again, in VB, it would look something like:

    Code:
    Dim oSniffer 
    
    SET oSniffer = CreateObject("vbSniffer.Sniffer")
    
    oSniffer.AddRecord ("SourceIP","DestIP","MoreData")
    Where vbSniffer is the project name and Sniffer is the name of the class in the project file.

    I'm not sure that creating a VB DLL is the best route for you, however. C++ is (generally) faster and it does support ADO calls. I just don't know how, 'cause I've never used it before.

    Regards,

    hmscott

  9. #9
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37
    But at the very least i can just add SQL commands after where is see the information that i need execute in the open source code that i downloaded correct? I don't really care how clean it is i just want to make sure that i can push some of the informaiton that the application provides to our database.

  10. #10
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    are you aware that windows servers have a network monitor built right into it and it will dump to a text file AND its free?
    unfortunately the source code thing is not available but there are windows api's that point directly to the network monitor...
    and it might be possible to update sql server directly through these calls.

    I dont use it as much as i used to but it is significantly helpful.
    Last edited by Ruprect; 03-03-04 at 17:09.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by goodolE22
    But at the very least i can just add SQL commands after where is see the information that i need execute in the open source code that i downloaded correct?
    Yes, you can certainly do that. You may want to seriously consider putting the data into a buffer at that point, then having an asynchronous process pull the data from the buffer and put it into your database. A NIC in promiscuous mode will generate data in bursts, and some of those bursts will be way faster than any server can accept the data!

    Once you get the data into the server, then you can do all kinds of interesting analysis. Beware, in that this kind of data gets "stale" very quickly unless you do some relatively sophisticate statistical grooming to keep it fresh.

    -PatP

  12. #12
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37
    Yeah we were thinking about the best way to do the analysis. The whole project is to develop a smarter network that will allocate bandwidth to the users that need it most. We are mainly looking at improving VoIP QoS. But since we are receiving data that is no longer taking up bandwidth on the medium, i was thinking that the information would remain in the database or a perioud of 10 seconds after which the entry will be deleted. Hopefully that will somewhat decrease the size of the groaing databsae and also make sure that we are working with relatively accurate information. We will be running procedures on the database to categorize bandwidth used and hopefully go into hosts and applications that are using the most bandwidth so that our program would be able to either limit the information coming through a specified port or shut down the port all together if a certain app is taking up too much bandwidth. We are not really sure how much we will be able to accomplish for this project but this is what we would like to do. Unfortunately, all this is on hold until i can figure out how to compile the code and then figure out where the information i need is stored in ethereal so i can push it into the database. Looks like a lot of work for someone that is new to all this, hopefully at least i will learn something out of all this even if i don't succeed with getting everything working the way we hope

  13. #13
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37

    more questions

    Well unfortunately one of my gorup members met with one of the IT administrators for our school and he was pretty much no help. So here i am again and I am still completely clueless on how to get our C program on linux to communicated with our MS SQL 2000 database on windows. I looked up different options for freetds, unixodbc, among others. And I am not sure what to use. We set the ODBC driver to our database on our WIndows 2000 box through Administrator Tools in the control panel. We came across instances where you have a SQL db on linux and a MS SQL db on windows and want them to communicate, is this when you have to install drivers on the linux side? Because we were hearing that if the database is on windows and the program is on linux then you don't have to do anything to the linux side.

    Another thing from looking around ADO is part of vb programming right? So if we create an ActiveX dll and put our ADO code in there to connect to the database all we have to do is register the dll on windows and then call it through our C program on linux? We have our c program working so would we just call another function that includes the dll and pass the parameters that we want it to execute? Meaning the the dll contains code to push sourceip to the database. How do we pass the value of sourceip in our c program to the dll so it can be processed?

    unfortunately our sources at school have been no help and we don't know where to turn for these answers.

    thanks

  14. #14
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37

    please help

    ok well i am an idiot and don't know what i was thinking when i posted, but i just realized that i can't use ADO calls in my c program because the c program is running on linux not windows. So there is no way to reference that dll correct? Oh well i looks like i am going back to the drawing board once again.. Maybe i should just install mysql on my redhat box and then go from there. If i install mysql on my redhat 9 box and run everything on that rather than using the windows 2000 box for the database does anyone know of any good tutorials to show how to set everything up and connect to the database? I have never had to do it before so it would help for me to have something to look at. What ODBC drivers i need to install to establish connection and how do I make a connection within the c code that shows the tcpdump info? I am guessing i can't just put raw SQL commands in the c code so what are my options? Learning PHP and writing something in PHP to make the connection, updates, etc...and then somehow calling that from the c program? Once again any help would be appreciated and feel free to explain to me as though I am a 5 year old because as of right now i am having absolutely no luck with anything i have tried to do for this project.

    In terms of the ODBC driver please go into detail with what exactly i need to do, etc. School and my work haven't provided me with any help on these subjects and i don't know who to turn to to explain all this stuff to me

  15. #15
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    like a 5 year old eh

    ok

    go to the url www.dbforums.com and select mysql from the list
    there will be very smart people there who can answer mysql questions.

    it's not that i dont want to play ball with you junior,, it's just that the game is on....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •