I maintain a little open source project that deals with IDS alert data. I want to add IP reputation to my event queries and I am stuck on how I should implement it.
The user will have the option of bringing in lists from different providers and the limit will not be fixed. These lists will be a single column of IP addresses. list 1: IP listing list 2: IP listing list 3: IP listing ... There can, and most likely will be duplication of addresses across the different lists. The number of lists that a host is a member of will be an indication of its reputation. The desired result will be something like: event count | event signature | src ip | country | ip reputation | dst ip | country | ip reputation The lists will be updated once each day or on demand. I already have a mappings table that provides country information for ip's in the event table which is joined during the event queries. The mappings table contains a little under 500,000 addresses and grows slowly - say 50 to 100 addresses / day. As new ip's appear in the event table, they are mapped to a country. Questions: 1) Should I just create a new table for every list the user adds and then do joins on these? 2) Should I put the lists in 1 table somehow? 3) As the lists are done daily, should I just run a midnight task that parses each list and adds the information to the mappings table. I have no idea what the format would look like. I was thinking of creating a varchar and have something like: list1|list2|list9|list20 and then just breaking it out in the code. The entire table would of course need to be scanned each day to check whether or not an address had been taken off a list. (efficiency?) Any comments/suggestions would be greatly appreciated. Thanks. -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
