So more information about the project... I am working on a syslog-ng project to input syslog messages into mysql. The syslog-ng.conf file is the only way to tell syslog-ng where to put data. You can perform filtering based on predefined syslog-ng macros such as $HOST, $DATE $TIME etc... however within this .conf file I am not able to create functions and to a replace($host,".","_"). The reason I am creating a table for each individual host or IP address is because of the amount of data and trying to minimize search time. These tables will hold millions of records each and to limit the search time, the application picks the table for the device and performs the search. For those who are ready to respond with their own input on how syslog-ng should be configured, I will add that syslog-ng is using file destinations. It is using file destinations over program or pipe destinations for performance reasons... Thus, syslog-ng creates a file that contains a bunch of CREATE TABLE entries. I then import those files with a cron script into mysql. This is where I am looking to convert IP address into something without periods. I cannot perform logic within the .conf file and it appears MySQL will not accept periods. Please let me know if any more info is needed. Thanks!
On 10/3/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > Replies embedded: > > "Patrick" <[EMAIL PROTECTED]> wrote on 10/03/2005 03:43:20 PM: > > > There are many ways to approach this. How are you receiving the IP > > data? Are you reading a file or other stream or are you trying to > > process the table creation by reading a column from a previously > > populated table through a select statement? > > > > The functions, inet_ntoa() and inet_addr(), are part of most > > networking libraries. These are the common functions to convert > > dotted quad notation. If you wanted to write you own function, an > > IPv4 address is broken down as follows: > > > > AAA.BBB.CCC.DDD > > \ \ \ \ > > \ \ \ DDD > > \ \ CCC x CCC > > \ BBB x BBB x BBB > > AAA x AAA x AAA x AAA > > I am not sure of your algorithm. Perhaps I am just not understanding your > notation. > > > > > Add the results and you have your unique 32bit number. > > > > eg. 10.10.10.1 <http://10.10.10.1/> = 10,000 + 1,000 + 100 + 1 > > = 11,101 > > > Let me try a different number(one a little less friendly to base10): > > the address 4.4.4.4 <http://4.4.4.4/>: > 4^4 + 4^3 + 4^2 + 4 = 256 + 64 + 16 + 4 = 340 > > Which would be the same as: 2.4.16.4 <http://2.4.16.4/> = 340 > your method does not seem to generate unique numbers for each IP > address.... > > Maybe I just don't get your description. > > > Mathematically speaking: The base10 representation of the 32-bit number > that 2.30.40.10 <http://2.30.40.10/> corresponds to would be: > (2 * 256*256*256) + (30 * 256*256) + (40 * 256) + 10 = 35530762 > > Which is the same thing we get from > > mysql>select inet_aton('2.30.40.10 <http://2.30.40.10/>'); > +-------------------------+ > | inet_aton('2.30.40.10 <http://2.30.40.10/>') | > +-------------------------+ > | 35530762 | > +-------------------------+ > 1 row in set (0.04 sec) > > as was mentioned earlier... > > > > > If you are not able to pre-process (scrub) the incoming data > > programmatically, you would need to create a UDF in MySQL to perform > > the conversion, or, alternatively, if you want to use MySQL SELECT > > statement as-is could replace the 'period' with an 'underscore' > > using MySQL's built-in string functions like so: > > > > Assumptions: Reading IP address from an existing table named > > IP_Addresses with a column named ip_address and a column named status. > > > > SELECT "CREATE TABLE ",REPLACE(ip_address,'.','_'), " [insert > > create options here]" FROM IP_Addresses WHERE status ="ACTIVE" > > Dynamic SQL? Not with that statement. He is going to need to create his > SQL statement client-side and send it pre-formatted to the server. MySQL > 5.0 has the beginnings of dynamic SQL and I am not 100% sure it would > accept what you typed. > > > > > You would obviously add your "CREATE TABLE" options and "INTO > > OUTFILE" options as needed. > > This would be an alternative to converting IPv4 to 32bit Integer. > > > > I hope this helps... > > > > If at all possible, it is probably best to continue in the MySQL > > list, there are some pretty clever people out there > > > > Pat... > > > > [EMAIL PROTECTED] > > CocoNet Corporation > > SW Florida's First ISP > > 825 SE 47th Terrace > > Cape Coral, FL 33904 > > > > ----- Original Message ----- > > From: Chance Ellis > > To: Patrick > > Sent: Monday, October 03, 2005 2:22 PM > > Subject: Re: Table names with periods > > > > > > Patrick, > > > > I have been trying to figure out how I can convert an IP address > > to a 32bit integer within a SQL create statement. > > You are mixing purposes. MySQL has a CREATE TABLE statement but it > requires a string literal. You cannot build a CREATE TABLE statement on the > fly -inside- MySQL. You have to build your statement client-side and send it > (as a complete statemnt) to MySQL to process. > > MySQL has a function that converts IP addresses into numbers (see above) > but you cannot combine that with a CREATE TABLE statement. > > > > > Is this possible or am I thinking about this all wrong? The input > > Yes, I think you are all wrong. You are being too "literal" in your design > choices. Generally if your data storage design requires you to add tables > whenever you add a new "whatever", that is a bad design. The better thing to > do is to create one table that can hold the entire class of "whatevers" and > differentiate between them with data markers. I assume that each of these > IP-named tables would look identical to every other (same column names, same > column types, ...)? The preferred method of modelling this is to create one > table (that looks just like each IP table was going to look) and adding a > column to it for the IP address. I know I am not the first person to > recommend this design (I can think of at least two others that have also > tried). > > Just so that we aren't all telling you to possibly do the wrong thing: Why > do you feel that individual IP tables is a correct DATABASE design? It may > be an acceptable PROGRAMMING design (one IP list object per address) but > this is probably one of those points where good DB design and good OO design > diverge. > > > I am given is a straight IP address. I have no way of modifying it > > other than some option in the SQL create statement is possible. I > > want to create a new table for each IP address. Without getting too > > much into the details, these are my requirements and I have been > > wasting alot of time trying to figure out how to change this string > > in the create statement. > > > The reason it has been so hard to do what you wanted to do is because what > you wanted to do is hard to maintain. Either trust us on our design > recommendation or tell us more about your problem. Depending on what you > tell us, perhaps neither design is appropriate. Please, and this goes for > others reading this later, never be afraid of getting into "too many > details". The details make all of the difference!! The better you can > describe your situation, the more likely you are to get a useful response. > > <remainder snipped> > > Kindest regards, > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine >
