8 Replies Latest reply on Apr 6, 2011 7:58 PM by gewald

    Table relationship based on IP address and wild cards possible?

    gewald

      Title

      Table relationship based on IP address and wild cards possible?

      Post

      I need to link two tables based on a column of IP addresses in one table with a column using wild cards to represent ranges of addresses using wild cards (like 123.456.789.*). I need that value to link to 123.456.789.1, or .2.  or .3 etc), but I can't seem to get it to work. Was hoping there would be a "begins with" relationship since none of my IP addresses have wild cards in the middle of the address (no 123.*.456.789 values). 

        • 1. Re: Table relationship based on IP address and wild cards possible?
          philmodjunk

          Can't use wild cards but two alternative methods come to mind:

          A text field with a list of values such as:

          123.456.789.1
          123.456.789.2
          123.456.789.3

          Enables a relationship that matches to all of those values.

          If you define a calculation field that drops off all text to the right of the last decimal point, all of these records have a single value to which you can match.

          Left ( IPAddressField ; 12) would work for that.

          • 2. Re: Table relationship based on IP address and wild cards possible?
            gewald

            Unfortunately, the IP address isn't always the same length. It could be 123.4.5.678. It doesn't put the zeroes or spaces in. I was dreading that this might be the only work around that you mentioned. In some cases it would require me to create 256 records, or 256x256 records depending on where the wild cards were going to fall. Filemaker never seems to have native support for valuable number formats. 

            • 3. Re: Table relationship based on IP address and wild cards possible?
              philmodjunk

              There are other ways to return just the digits to the left of the last period.

              Left ( IPAddressField ; Position ( IPAddressField ; "." ; 1 ; 3 ) )

              will return all text to the left of the third period (inlcuding the period).

              • 4. Re: Table relationship based on IP address and wild cards possible?
                gewald

                Found an interesting thread on another Filemaker Forum...(fmforums)

                This makes it easy to get the middle values of the address which might be 1-3 characters long. 

                Create four fields using this formula:

                IP1 = GetValue(Substitute(IP field name; "."; "¶"); 1)

                IP2 = GetValue(Substitute(IP field name; "."; "¶"); 2)

                IP3 = GetValue(Substitute(IP field name; "."; "¶"); 3)

                IP4 = GetValue(Substitute(IP field name; "."; "¶"); 4) 

                The Substitute() makes a list.
                The GetValue() pulls out the item from the list.

                Substitute(IP; "."; "¶") replaces each occurrence of the "." with a line break. This makes each segment of the IP address appear on a separate line. A FileMaker list is a series of values with each value appearing on a separate line.

                GetValue(a_list; 2) will get the second value in a list. 

                Doesn't solve my wild card issue, still open for suggestions on that. Might have to create all the possible values for each position, so 256, or 256x256 extra rows in the comparison table. 

                • 5. Re: Table relationship based on IP address and wild cards possible?
                  philmodjunk

                  Please give an example of where you need a wild card. This isn't an option with FileMaker, but as the previous posts indicated, there are often alternatives that get the job done. In the above case, you can use calculations to break your IP address into pieces and match just by the pieces that are significant to avoid the need for a "wildcard".

                  • 6. Re: Table relationship based on IP address and wild cards possible?
                    gewald

                    I'm trying to compare data from a website , and isolate the traffic from specific IP addresses for reporting purposes. I have a table with specific IP addresses, but while half of them are specific addresses, there are several that allow for any value from 1-255 in the third and/or fourth position of the IP address. 

                    So I need to find all the traffic from 9.125.12.1-255, or worse from 10.85.1-255.1-255. 

                    Garrett

                    • 7. Re: Table relationship based on IP address and wild cards possible?
                      philmodjunk

                      Since the first part of the IP address is the same (9.125.12) use a calculation field to match just by that portion of the IP address.

                      9.125.12.1-255

                      Define the calculation field I suggested earlier:

                      Left ( IPAddressField ; Position ( IPAddressField ; "." ; 1 ; 3 )  - 1) // - 1 drops off the last period

                      If we name that calculation field cIP3, this relationship works for the first example:

                      TableOccurrence1::cIP3 = TableOccurrence2::cIP3

                      10.85.1-255.1-255

                      In this case define cIP2 as

                      Left ( IPAddressField ; Position ( IPAddressField ; "." ; 1 ; 2 )  - 1)

                      TableOccurrence1::cIP2 = TableOccurrence2::cIP2

                      While these examples both use the calculation field for both sides of the relationship, you can also define one side with a text field, enter just the first 3 sets of digits (cIP3 relationship) or 2 sets of digts (cIP2 relationship) to match by just those values.

                      If you are using FileMaker 11, you might also consider using a filtered portal to fine tune the matching. Here's a demo file that includes two options for using a filtered portal in this manner: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

                      If you have FileMaker Advanced, it's possible to create a custom function that produces a wild card match. I've been playing around with such a function after another forum participant asked a similar question. It can be found in my Known Bugs List database. If you have a copy of advanced, you can import the function into your file if you find it useful.

                      • 8. Re: Table relationship based on IP address and wild cards possible?
                        gewald

                        Sadly I don't have Advanced. I will take a look at the filtered portal example. 

                        The one thing I'm still sorting through is the relationship the with table of IP Addresses with extra details. The comparison IP Address is expressed with wild cards for the web host to grant access to visitors from those IP addresses, but the traffic log shows the full address. I guess I could try a multi field comparison, starting with the first two IP address parts, and if those match compare with three, then four. It could get heavy as more data is involved, though. Potentially over 250K records a year. 

                        Garrett