{"id":10971,"date":"2012-03-03T00:01:00","date_gmt":"2012-03-03T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2012\/03\/03\/use-powershell-to-collect-store-and-parse-iis-log-data\/"},"modified":"2012-03-03T00:01:00","modified_gmt":"2012-03-03T00:01:00","slug":"use-powershell-to-collect-store-and-parse-iis-log-data","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/use-powershell-to-collect-store-and-parse-iis-log-data\/","title":{"rendered":"Use PowerShell to Collect, Store, and Parse IIS Log Data"},"content":{"rendered":"<p><b>Summary<\/b>: Guest blogger, Microsoft PFE Chris Weaver, shows how to use Windows PowerShell to collect, store, and parse IIS log data.<\/p>\n<p>Microsoft Scripting Guy, Ed Wilson, is here. Today we have back with us Chris Weaver.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/1754.wes-3-3-12-1.jpg\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/1754.wes-3-3-12-1.jpg\" alt=\"Photo of Chris Weaver\" title=\"Photo of Chris Weaver\" \/><\/a><\/p>\n<p style=\"padding-left: 30px\">I have been working at Microsoft since late 2008, during that time I have been an engineer within CSS, a SharePoint&nbsp;2010 TAP, and most recently I have become a dedicated premier field engineer working with several of our premier customers to support their SharePoint infrastructure.&nbsp;I have been using Windows PowerShell for the last two years to simplify the administration and troubleshooting of SharePoint for my customers. I enjoy camping with my family and kite surfing in my spare time. (Yeah, right who has any of that&#8230;)<\/p>\n<p style=\"padding-left: 30px\">Blog: <a href=\"http:\/\/blogs.technet.com\/b\/christwe\/\" target=\"_blank\">Wondering Mind<\/a> (about issues with SharePoint and its supporting infrastructure)<\/p>\n<p>Raise your hands if you recently tried to parse through your IIS logs to get an answer. Did you find it easy? Did you still have the correct log files? One of my customers recently brought this problem to me saying that they were not happy with any of the current methods and wondered if I could make something to work with Windows PowerShell.<\/p>\n<p>They wanted to be able to remove IIS logs from their web front ends when they needed to and still maintain a long-term repository of this rich and valuable data. They had already centralized the IIS logs into one folder, which had the following structure of a parent folder for the repository and then a folder for each web application that contained files from the web front ends server.<\/p>\n<p>&nbsp;<a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/0116.wes-3-3-12-2.jpg\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/0116.wes-3-3-12-2.jpg\" alt=\"Image of folder\" title=\"Image of folder\" \/><\/a><\/p>\n<p>With the file collection already solved for me, I started on the script. I realized there where a few things I would have to accomplish:&nbsp;<\/p>\n<ul>\n<li>Create database and table structures.<\/li>\n<li>Extract data from the files. This is very simple because the IIS logs were Tab delimited with each entry on its own line.&nbsp;<\/li>\n<li>Clean-up the data.<\/li>\n<li>Import the data into SQL Server.<\/li>\n<\/ul>\n<p>It all turned out to be a lot simpler than I thought it would be. I started off by writing functions to do the following:&nbsp;<\/p>\n<h2>Create a database<\/h2>\n<p>This function uses the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/microsoft.sqlserver.management.smo.database.aspx\" target=\"_blank\">Smo.Database class<\/a> to get all the databases, and then it enumerates all the databases on SQL Server. It compares the database name to the one provided, and if it finds no match, I create the database. In either case, I return the database object.<\/p>\n<p style=\"padding-left: 30px\">function Create_Database<\/p>\n<p style=\"padding-left: 30px\">{<\/p>\n<p style=\"padding-left: 30px\">param($SQLSvr, [string]$DatabaseName, [string]$DBServer)<\/p>\n<p style=\"padding-left: 30px\">&nbsp;<\/p>\n<p style=\"padding-left: 30px\">foreach($db in $SQLSvr.Databases) # Check to see if our Database exists<\/p>\n<p style=\"padding-left: 30px\">{<\/p>\n<p style=\"padding-left: 30px\">if($db.Name -eq $DatabaseName)<\/p>\n<p style=\"padding-left: 30px\">{<\/p>\n<p style=\"padding-left: 30px\">return $db<\/p>\n<p style=\"padding-left: 30px\">}<\/p>\n<p style=\"padding-left: 30px\">}<\/p>\n<p style=\"padding-left: 30px\">$db = New-Object Microsoft.SqlServer.Management.Smo.Database($SQLSvr, $DatabaseName)<\/p>\n<p style=\"padding-left: 30px\">$db.Create()<\/p>\n<p style=\"padding-left: 30px\">return $db<\/p>\n<p style=\"padding-left: 30px\">}<\/p>\n<h2>Create a table<\/h2>\n<p>By using the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/system.collections.specialized.stringcollection.aspx\" target=\"_blank\">StringCollection<\/a> class, I add a SQL <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms174979.aspx\" target=\"_blank\">Create Table<\/a> statement to a string and then pass the string to my <b>Execute Statements<\/b> function. In that statement, I follow the <a href=\"http:\/\/support.microsoft.com\/kb\/296085\">article 296085<\/a> in the Microsoft Knowledge Base to create all the correct columns. Be aware that if you want to change the type of IIS log that you use, you need to change the columns that you create.<\/p>\n<p style=\"padding-left: 30px\">function Create_Table<\/p>\n<p style=\"padding-left: 30px\">{<\/p>\n<p style=\"padding-left: 30px\">param($DB, [string]$TableName)<\/p>\n<p style=\"padding-left: 30px\">&nbsp;<\/p>\n<p style=\"padding-left: 30px\">$TableScript = New-Object -Type System.Collections.Specialized.StringCollection<\/p>\n<p style=\"padding-left: 30px\">$TableScript.Add(&#8220;CREATE TABLE [dbo].[$TableName] ([date] [datetime] NULL,[time] [datetime] NULL ,[s-sitename] [varchar] (255) NULL,[s-computername] [varchar] (255) NULL ,[s-ip] [varchar] (50) NULL ,[cs-method] [varchar] (50) NULL ,[cs-uri-stem] [varchar] (512) NULL ,[cs-uri-query] [varchar] (2048) NULL ,[s-port] [varchar] (255) NULL ,[cs-username] [varchar] (255) NULL ,[c-ip] [varchar] (255) NULL ,[cs-version] [varchar] (255) NULL ,[cs(User-Agent)] [varchar] (512) NULL ,[cs(Cookie)] [varchar] (4096) NULL ,[cs(Referer)] [varchar] (2048) NULL,[cs-host] [varchar] (255) NULL ,[sc-status] [int] NULL ,[sc-substatus] [varchar] (255) NULL,[sc-win32-status] [varchar] (255) NULL,[sc-bytes] [int] NULL ,[cs-bytes] [varchar] (255) NULL ,[time-taken] [int] NULL)&#8221;) | Out-Null<\/p>\n<p style=\"padding-left: 30px\">Database_ExecuteNonQuery_Command $DB $TableScript #Create Table<\/p>\n<p style=\"padding-left: 30px\">}<\/p>\n<h2>Execute statements<\/h2>\n<p>Executes any non-query statement that you provide it as a string through the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/system.data.sqlclient.sqlcommand.executenonquery.aspx\" target=\"_blank\">ExecuteNonQuery<\/a> class.&nbsp;<\/p>\n<p><b>Note<\/b>&nbsp;&nbsp;&nbsp;The statement cannot perform queries on the database like SELECT.<\/p>\n<p style=\"padding-left: 30px\">function Database_ExecuteNonQuery_Command<\/p>\n<p style=\"padding-left: 30px\">{<\/p>\n<p style=\"padding-left: 30px\">param($SQLDataBase, $CommandScript)<\/p>\n<p style=\"padding-left: 30px\">$Error.Clear()<\/p>\n<p style=\"padding-left: 30px\">$ExecutionType = [Microsoft.SqlServer.Management.Common.ExecutionTypes]::ContinueOnError<\/p>\n<p style=\"padding-left: 30px\">$SQLDataBase.ExecuteNonQuery($CommandScript, $ExecutionType)<\/p>\n<p style=\"padding-left: 30px\">&nbsp;<\/p>\n<p style=\"padding-left: 30px\">trap {Write-Host &#8220;[ERROR]: $_&#8221;; continue}<\/p>\n<p style=\"padding-left: 30px\">}<\/p>\n<h2>Clean the log files<\/h2>\n<p>The IIS logs have headers and other lines that we do not want to import into the database. This function will get all the lines from the file by using <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ee176843.aspx\" target=\"_blank\">Get-Content<\/a>. By using <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ee176956.aspx\" target=\"_blank\">Select-String<\/a>, it removes any lines that contain a Regular Pattern, and then rewrites the file with all the good lines by using <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ee176959.aspx\" target=\"_blank\">Set-Content<\/a>.<\/p>\n<p style=\"padding-left: 30px\">function Clean_Log_File<\/p>\n<p style=\"padding-left: 30px\">{<\/p>\n<p style=\"padding-left: 30px\">param ($LogFile)<\/p>\n<p style=\"padding-left: 30px\">$Content = Get-Content $LogFile.FullName | Select-String -Pattern &#8220;^#&#8221; -notmatch<\/p>\n<p style=\"padding-left: 30px\">Set-Content $LogFile.FullName $Content<\/p>\n<p style=\"padding-left: 30px\">}<\/p>\n<h2>Then I started on the main logic&nbsp;<\/h2>\n<ul>\n<li>Do a little bit of error checking (this is a great place for you to improve because I have done only a little bit)<\/li>\n<li>Add the type <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms162169.aspx\" target=\"_blank\">Microsoft.SQLServer.Smo<\/a> and create a connection to my SQL Server<\/li>\n<li>Create my database or find the preexisting one<\/li>\n<li>Get all folders in the path provided<\/li>\n<li>Create my table (one per subfolder)<\/li>\n<li>Load all the files<\/li>\n<li>Clean the file<\/li>\n<li>Load the cleaned file into SQL Server<\/li>\n<\/ul>\n<p style=\"padding-left: 30px\">$LineScript = New-Object -Type System.Collections.Specialized.StringCollection&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<\/p>\n<p style=\"padding-left: 30px\">$LineScript.Add(&#8220;BULK INSERT $Database.[dbo].[$TableName] FROM `&#8221;$File`&#8221; WITH (BATCHSIZE = 10,FIRSTROW = 1,FIELDTERMINATOR = &#8216; &#8216;, ROWTERMINATOR = &#8216;\\n&#8217;)&#8221;) | Out-Null<\/p>\n<p style=\"padding-left: 30px\">Database_ExecuteNonQuery_Command $Database $LineScript<\/p>\n<h2>Rename the file<\/h2>\n<p>I rename the file with the .old extension, so I do not read the file more than once.<\/p>\n<p style=\"padding-left: 30px\">Rename-Item $File (($LogFile.FullName).TrimEnd($LogFile.extension) + &#8220;.old&#8221;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#Insure we don&#8217;t add contents of file to table again<\/p>\n<p><b>Note<\/b>&nbsp;&nbsp;&nbsp;You need to run with elevated permissions to be able to write to the database. You can do this by typing <b>runas<\/b> when you open Windows PowerShell, or read <a href=\"http:\/\/blogs.technet.com\/b\/christwe\/archive\/2012\/02\/16\/how-to-elevate-permissions-for-your-shell-within-a-script.aspx\" target=\"_blank\">my blog post<\/a> about how to do this with Task Manager.<\/p>\n<p>If everything works well, you should see the following in SQL Management Studio.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/1781.wes-3-3-12-3.jpg\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/1781.wes-3-3-12-3.jpg\" alt=\"Image of command output\" title=\"Image of command output\" \/><\/a><\/p>\n<p>After the script finishes running, you will be able to run <b>Select<\/b> statements and other SQL queries against your database to find information such as:&nbsp;<\/p>\n<ul>\n<li>Top users<\/li>\n<li>Top sites<\/li>\n<li>Top five users getting unauthorized access to sites<\/li>\n<\/ul>\n<p>Watch for the next post when I will develop a script that automatically runs different reports from the information that we have collected.<\/p>\n<p>~Chris<\/p>\n<p>Thanks Chris. This has been a great blog. The script can be found in <a href=\"http:\/\/gallery.technet.microsoft.com\/scriptcenter\/Writing-a-script-to-import-e0d095ee\" target=\"_blank\">the Script Repository<\/a>.<\/p>\n<p>I invite you to follow me on <a href=\"http:\/\/bit.ly\/scriptingguystwitter\" target=\"_blank\">Twitter<\/a> and <a href=\"http:\/\/bit.ly\/scriptingguysfacebook\" target=\"_blank\">Facebook<\/a>. If you have any questions, send email to me at <a href=\"mailto:scripter@microsoft.com\" target=\"_blank\">scripter@microsoft.com<\/a>, or post your questions on the <a href=\"http:\/\/bit.ly\/scriptingforum\" target=\"_blank\">Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n<p><strong>Ed Wilson, Microsoft Scripting Guy<\/strong>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Summary: Guest blogger, Microsoft PFE Chris Weaver, shows how to use Windows PowerShell to collect, store, and parse IIS log data. Microsoft Scripting Guy, Ed Wilson, is here. Today we have back with us Chris Weaver. I have been working at Microsoft since late 2008, during that time I have been an engineer within CSS, [&hellip;]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[297,56,326,3,130,61,45],"class_list":["post-10971","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-chris-weaver","tag-guest-blogger","tag-iis","tag-scripting-guy","tag-servers","tag-weekend-scripter","tag-windows-powershell"],"acf":[],"blog_post_summary":"<p>Summary: Guest blogger, Microsoft PFE Chris Weaver, shows how to use Windows PowerShell to collect, store, and parse IIS log data. Microsoft Scripting Guy, Ed Wilson, is here. Today we have back with us Chris Weaver. I have been working at Microsoft since late 2008, during that time I have been an engineer within CSS, [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/10971","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=10971"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/10971\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=10971"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=10971"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=10971"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}