The basic project to be accomplished is to create a SQL Server/TSQL stored procedure that will take Windows Server IIS logging information and to store into a series of SQL Server tables to track patterns of access of users and crawlers/robots. The goal is keep track of different classes of web requesters by the IP address, browser/device types, their location, what pages they accessed (when first, last, and how many times), with a lot of focus on detailed tracking of all crawlers/robots to our site. There are four additional tables that get populated when a crawler/robot is detected. Three REST API providers are used to get detailed information from the IP Address and User Agent Information provided of the requester that we want to then store in the database.
• All code should be written in TSQL invoked as a stored procedure
• The database will be SQL Server 2017 or later
• I can provide a dedicated development server accessible 24x7 via RDP over the Internet w/Windows Server 2016, SQL Server 2017, and Visual Studio Community Edition installed and ready to go.
• I can provide a full SQL Server TSQL script to create the schema for your use
• There are 5 primary tables that get populated by the arguments to the stored procedure
• The solution requires getting data from three REST API providers (IPStack, from 51degrees, HackerTarget that all have free API access for testing)
• There are 4 additional tables that need populating if the request is detected to be a crawler/robot
• Please read the attachment for the detailed schema and requirements for how the fields are populated and how the tables need to be linked to each other
The arguments to the stored procedure are from IIS ServerVariables ([login to view URL]) as follows:
REMOTE_ADDR, HTTP_HOST, URL, QUERY_STRING, HTTP_USER_AGENT, REQUEST_METHOD, HTTP_ACCEPT, HTTP_COOKIE, and HTTP_ACCEPT_LANGUAGE
Attached is a detailed schema description and what needs to be populated in the database.
Hello, I am a c# developer and a sql server certified developer i can provide you this very smart solution and will work on it dedicatedly. you can view my profile and then we can discuss further