You should be able to do the conversion when you run the cron script. Or at least just before. Preprocess the output from syslog-ng to translate all . to _. in the table names.
Jerl On 10/3/05, Chance Ellis <[EMAIL PROTECTED]> wrote: > > 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> <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> <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> <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> <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> < > http://2.30.40.10/>'); > > +-------------------------+ > > | inet_aton('2.30.40.10 <http://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 > > > >
