akohan
asked on
Distribution of data in sql server
Hello,
I have some ideas to process the following steps but will appreciate it if some experts share their thoughts with me:
(frequency is daily)
1) Information from IIS log file will be exported into a CSV (using IIS Logger in PS) file then a C# code will insert them all in a table (let's call it RawTable).
2) After having all the information for let's say for today, I need to break them into different tables. One table holds onto Client IPs and Server IPs, one table will have the browsers info and cookies information and etc
In general I'm having around 7 tables.
what technique do you suggest I go with? should I write few stored procedures or a 3rd party tool?''
*This process happens every midnight or early morning.
Thanks.
I have some ideas to process the following steps but will appreciate it if some experts share their thoughts with me:
(frequency is daily)
1) Information from IIS log file will be exported into a CSV (using IIS Logger in PS) file then a C# code will insert them all in a table (let's call it RawTable).
2) After having all the information for let's say for today, I need to break them into different tables. One table holds onto Client IPs and Server IPs, one table will have the browsers info and cookies information and etc
In general I'm having around 7 tables.
what technique do you suggest I go with? should I write few stored procedures or a 3rd party tool?''
*This process happens every midnight or early morning.
Thanks.
Why do must you do an export to CSV. IIS's HTTP logs are well documented and well formed. Why not just have the C# code consume the original logs? This way you remove an un-needed step in the loading process.
Why not take it one step further? instead of importing the https logs file, line for line, into a table... why not process the logs using C#? Then store the parsed data into your 7+ tables. This removes a second step in the loading process.
To do this in a different way...
1. consume http logs with Microsoft's Lop Parser tool (http://technet.microsoft.com/en-us/scriptcenter/dd919274.aspx)
2. run log parser with the various SQL-like queries to get the data you want into your tables.
3. output the results of your queries to a csv file
4. use the SQL Server Bulk Insert command to put the data into your 7+ tables. http://msdn.microsoft.com/en-us/library/ms178129(v=sql.110).aspx
I guess it all depends on how much work you want to do or if this is just a learning experience.
I'd say, to learn.. go the C# coding path. To get it done with tools that are readily available and well documented... Log Parser and Bulk Insert.
Dan
Why not take it one step further? instead of importing the https logs file, line for line, into a table... why not process the logs using C#? Then store the parsed data into your 7+ tables. This removes a second step in the loading process.
To do this in a different way...
1. consume http logs with Microsoft's Lop Parser tool (http://technet.microsoft.com/en-us/scriptcenter/dd919274.aspx)
2. run log parser with the various SQL-like queries to get the data you want into your tables.
3. output the results of your queries to a csv file
4. use the SQL Server Bulk Insert command to put the data into your 7+ tables. http://msdn.microsoft.com/en-us/library/ms178129(v=sql.110).aspx
I guess it all depends on how much work you want to do or if this is just a learning experience.
I'd say, to learn.. go the C# coding path. To get it done with tools that are readily available and well documented... Log Parser and Bulk Insert.
Dan
ASKER
Hi Dan,
Yes, already I am using Log Parser but didn't think of Bulk Insert. I heard it in past but never used it.
I will get back to you guys,
Thanks.
Yes, already I am using Log Parser but didn't think of Bulk Insert. I heard it in past but never used it.
I will get back to you guys,
Thanks.
Any luck on this?
Dan
Dan
ASKER
Hello Dan,
Yes, I'm thinking of Sql Server Bulk insert but can it be automated?
The truth is that I'm using LogParser so currently *.log > *.csv > then my code reads it and inserts into database but now that you have pointed out to Sql Server Bulk insert then thinking of using it.
Thanks.
Yes, I'm thinking of Sql Server Bulk insert but can it be automated?
The truth is that I'm using LogParser so currently *.log > *.csv > then my code reads it and inserts into database but now that you have pointed out to Sql Server Bulk insert then thinking of using it.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the details.
If you need "regular expressions" to mine your data, this can be incorporated into the SQL instance using this technique:
https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/
Regards Marten