1 Reply Latest reply on Feb 7, 2011 11:45 AM by philmodjunk

    Tracking When Users are Sharing USERIDs with Friends

    KevinForte

      Title

      Tracking When Users are Sharing USERIDs with Friends

      Post

      Ok.  I am not sure of the best way to approach this issue, so I'll throw this out there and hope someone can direct me in the right direction.

      Here is my DB schema

      Tables

      USERS (USERID,<bunch of user attributes>)

      ACCESS (USERID,Date,IP)

      IPADDRESS (IP,ISP,RevDNS)

      The ACCESS table has an entry for each time the USER accesses the website and the IPADDRESS table has all the attributes for the individual ipaddresses.

      The USERS are accessing the website from many different devices and therefore different ISPs.  It is very evident that each user accesses the website from at least a Home ISP, a work ISP and a mobile ISP.  This is pretty normal.  We are trying to identify when a USER is sharing a USERID with another person who is logging in from a different ISP.

      We monitor access every day, so we would like to generate a few reports to identify this violation of our terms of service.

      Since the USERS are logging in upwards of 50 times per day, sometimes even more, I would like to summarize all the logins from the same ISP with a count next to it.  We have already addressed the IPADDRESS table having the same value in the ISP field so grouping those records is not the problem.

      I'd like to create a Layout based on the USERS table and have some portals that list the ACCESS data but not the individual records.  I would like to have each portal row be a summary of all the records with the same ISP and a count.

      Can you create a portal with summary data?

      I was planning to have a portal for each day of the week on the Layout and was hoping to show the summary data for that filtered day.

      Please help.

      Thanks

      FileMakerSummaryPortals.jpg

        • 1. Re: Tracking When Users are Sharing USERIDs with Friends
          philmodjunk

          I suggest you turn this problem upside down here.

          If you base your layout on Access, you can set up a list view type summary report with a sub summary part "sorted by" IP so that you get one row of data per IP address for a given user. You can include data from the USERS table in the header and perform a find from the button that accesses this page to limit the records to just those for a given user. (Or this report can be sorted by User, then by ID to produce a report of all users' access histories.