Have you ever wanted to parse through your IIS web logs to create a “WebTrends” like application in ColdFusion?
This tutorial will show you how you can parse through the IIS log files and have variables ready to insert into databases, print or to achieve whatever you want with them. Let’s begin:
The first thing we need to do is to define some default values, these will be used later on in the application.
The values we will specify will be:
Keep in mind that this tutorial is showing you how to process a single log file at a time, if you want to do multiple log files at a time a few tweaking changes will be needed. Contact me for additional help.
<!------------ By default set value ------------>
<cfparam name="file2load" default="C:logslog.txt">
<cfset entry_ws3_number = "ws3srv">
<cfset entry_URL = "www.yoursite.com">
<!------------ Load the file ---------------->
<cffile action="READ"
file="#file2load#"
variable="log_data">
<cfoutput>
<hr>
<!------- Break by line ---------->
<cfloop index="rc" list="#log_data#" delimiters="#chr(13)##chr(10)#">
<!---------- Check that it's an actual line ------->
<cfif #left(rc, 1)# eq "#chr(35)#">
<!-------- the current line is not a valid log item, skip and go to the next line ----------->
<cfelse>
<!----------- by item --------->
<CFSET Value = 0>
<cfloop index="id" list="#rc#" delimiters="#chr(32)#">
<CFSET FirstValue = "#id#">
<CFSET Value = Value + 1>
<CFIF Value is 1>
<cfset date = "#FirstValue#">
<CFELSEIF Value is 2>
<cfset time = "#FirstValue#">
<CFELSEIF Value is 3>
<cfset c_ip = "#FirstValue#">
<CFELSEIF Value is 4>
<cfset cs_username = "#FirstValue#">
<CFELSEIF Value is 5>
<cfset s_ip = "#FirstValue#">
<CFELSEIF Value is 6>
<cfset s_port = "#FirstValue#">
<CFELSEIF Value is 7>
<cfset cs_method = "#FirstValue#">
<CFELSEIF Value is 8>
<cfset cs_uri_stem = "#FirstValue#">
<CFELSEIF Value is 9>
<cfset cs_uri_query = "#FirstValue#">
<CFELSEIF Value is 10>
<cfset sc_status = "#FirstValue#">
<CFELSEIF Value is 11>
<cfset User_Agent = "#FirstValue#">
<CFELSEIF Value is 12>
<cfset Referer = "#FirstValue#">
<CFELSE>
</cfif>
</cfloop>
<!----------- Display the values --------------->
Date: #date#<BR>
Time: #time#<BR>
Cust_IP: #c_ip#<BR>
Username: #cs_username#<BR>
Site_IP: #s_ip#<BR>
Site_Port: #s_port#<BR>
Method: #cs_method#<BR>
Site_path: #cs_uri_stem#<BR>
Query: #cs_uri_query#<BR>
Status: #sc_status#<BR>
Browser: #User_Agent#<BR>
Referer: #Referer#<HR>
<!------------- Here you may insert into the DB ---------------->
<cfquery name="qInsertEntry" datasource="iis_logs">
insert into iis_logs(
entry_date,
entry_time,
entry_Cust_IP,
entry_Username,
entry_Site_IP,
entry_Site_Port,
entry_Method,
entry_Site_path,
entry_Query_Strings,
entry_Status,
entry_Browser,
entry_Referer,
entry_ws3_number,
entry_URL
)
values(
#CreateODBCDate(date)#,
#CreateODBCTime(Time)#,
'#c_ip#',
'#cs_username#',
'#s_ip#',
'#s_port#',
'#cs_method#',
'#cs_uri_stem#',
'#cs_uri_query#',
'#sc_status#',
'#User_Agent#',
'#Referer#',
'#entry_ws3_number#',
'#entry_URL#'
)
</cfquery>
</cfif>
</cfloop>
</cfoutput>
It’s that simple, you are reading and processing the logs, you can now query the “iis_logs” database to create reports and to let your users know how many visitors, hits and page views you’ve had this week! I suggest you schedule this tag and let it run once per day reading your logs for that day, that way you will have all this data in your database for easy access at any time!
Added 01/02/2003 (Pablo Varando):
I’ve been asked by some of our users to explain how you would process this for multiple files in a directory, so I wanted to write an addition to this tutorial with more information about that, so here we go!
<!------------ By default set value ------------>
<cfparam name="LogsDir" default="C:logs">
<cfset entry_ws3_number = "ws3srv">
<cfset entry_URL = "www.yoursite.com">
<!--- Do a CFDirectory to retrieve the log files **ONLY** --->
<cfdirectory action="list"
directory="#LogsDir#"
name="LogFiles"
filter="*.log">
<!--- Loop through the directory and process each file --->
<cfloop query="LogFiles">
<!--- Specify the filename to a variable --->
<cfset file2load = "#LogsDir##Name#">
<!------------ Load the file ---------------->
<cffile action="READ"
file="#file2load#"
variable="log_data">
<cfoutput>
<hr>
<!------- Break by line ---------->
<cfloop index="rc" list="#log_data#" delimiters="#chr(13)##chr(10)#">
<!---------- Check that it's an actual line ------->
<cfif #left(rc, 1)# eq "#chr(35)#">
<!-------- the current line is not a valid log item, skip and go to the next line ----------->
<cfelse>
<!----------- by item --------->
<CFSET Value = 0>
<cfloop index="id" list="#rc#" delimiters="#chr(32)#">
<CFSET FirstValue = "#id#">
<CFSET Value = Value + 1>
<CFIF Value is 1>
<cfset date = "#FirstValue#">
<CFELSEIF Value is 2>
<cfset time = "#FirstValue#">
<CFELSEIF Value is 3>
<cfset c_ip = "#FirstValue#">
<CFELSEIF Value is 4>
<cfset cs_username = "#FirstValue#">
<CFELSEIF Value is 5>
<cfset s_ip = "#FirstValue#">
<CFELSEIF Value is 6>
<cfset s_port = "#FirstValue#">
<CFELSEIF Value is 7>
<cfset cs_method = "#FirstValue#">
<CFELSEIF Value is 8>
<cfset cs_uri_stem = "#FirstValue#">
<CFELSEIF Value is 9>
<cfset cs_uri_query = "#FirstValue#">
<CFELSEIF Value is 10>
<cfset sc_status = "#FirstValue#">
<CFELSEIF Value is 11>
<cfset User_Agent = "#FirstValue#">
<CFELSEIF Value is 12>
<cfset Referer = "#FirstValue#">
<CFELSE>
</cfif>
</cfloop>
<!----------- Display the values --------------->
Date: #date#<BR>
Time: #time#<BR>
Cust_IP: #c_ip#<BR>
Username: #cs_username#<BR>
Site_IP: #s_ip#<BR>
Site_Port: #s_port#<BR>
Method: #cs_method#<BR>
Site_path: #cs_uri_stem#<BR>
Query: #cs_uri_query#<BR>
Status: #sc_status#<BR>
Browser: #User_Agent#<BR>
Referer: #Referer#<HR>
<!------------- Here you may insert into the DB ---------------->
<cfquery name="qInsertEntry" datasource="iis_logs">
insert into iis_logs(
entry_date,
entry_time,
entry_Cust_IP,
entry_Username,
entry_Site_IP,
entry_Site_Port,
entry_Method,
entry_Site_path,
entry_Query_Strings,
entry_Status,
entry_Browser,
entry_Referer,
entry_ws3_number,
entry_URL
)
values(
#CreateODBCDate(date)#,
#CreateODBCTime(Time)#,
'#c_ip#',
'#cs_username#',
'#s_ip#',
'#s_port#',
'#cs_method#',
'#cs_uri_stem#',
'#cs_uri_query#',
'#sc_status#',
'#User_Agent#',
'#Referer#',
'#entry_ws3_number#',
'#entry_URL#'
)
</cfquery>
</cfif>
</cfloop>
</cfoutput>
<!--- End up the loop of files --->
</cfloop>
And that’s about it, that is how you process multiple files at the same time!
Question? Comments? Email Me……
Click here to sign up for FREE Tech. newsletters from Murdok!
EasyCFM.Com introduces at least three new tutorials each week, written by the webmaster (Pablo Varando) and also from individual people who post their own tutorials for visitors to learn from. For more information please visit: http://www.easycfm.com [EasyCFM is Hosted by Colony One On-Line – http://www.colony1.net]
