In my last blog, you learn how IIS (or similar) log can be uploaded to SQL DB. Once logs are uploaded you can analyze it using PowerShell and create fancy report as well. Let’s see how this can be done.
Following script gives number of request per IIS page with response code.
$Query1=" Use IISLogReview SELECT TOP 20 [sc-STATUS] Response, [cs-uri-stem] Access_Page, count(*) Total_Request from IISLOG GROUP BY [sc-STATUS], [cs-uri-stem] ORDER BY COUNT(*) DESC" $SqlOut1=Invoke-Sqlcmd -ServerInstance SQL1 -Query $Query1 -QueryTimeout 65535 $PrintOut1=$SqlOut1|Select-Object Response,Access_Page,Total_Request|Format-Table -AutoSize $PrintOut1
Following script gives number of request per user.
$Query2=" Use IISLogReview SELECT COUNT(*) Total_Request, [s-username] UserName FROM IISLOG GROUP BY [s-username] ORDER BY Total_Request DESC" $SqlOut2=Invoke-Sqlcmd -ServerInstance SQL1 -Query $Query2 -QueryTimeout 65535 $PrintOut2=$SqlOut2|Select-Object Total_Request,UserName |Format-Table -AutoSize $PrintOut2
This is the complete tool/function however this time it will generate a HTML output.
Function AnalyseIIS { PARAM ( #Default SQL will be local host [string]$SQLServer=$env:computername, #Default HTML File will be c;\temp\yyyymmdd.html [string]$HTMLFile='C:\TEMP\'+(Get-Date).tostring("yyyyMMddhhmm")+'.html' ) $Query1= "Use IISLogReview SELECT TOP 20 [sc-STATUS] Response, [cs-uri-stem] Access_Page, count(*) Total_Request from IISLOG GROUP BY [sc-STATUS], [cs-uri-stem] ORDER BY COUNT(*) DESC" $Query2= "Use IISLogReview SELECT COUNT(*) Total_Request, [s-username] UserName FROM IISLOG GROUP BY [s-username] ORDER BY Total_Request DESC" $a = "" $a = $a + "BODY{background-color:peachpuff;font-family: Calibri; font-size: 12pt;}" $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}" $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}" $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}" $a = $a + "" $SqlOut1=Invoke-Sqlcmd -ServerInstance $SQLServer -Query $Query1 -QueryTimeout 65535 $PrintOut1=$SqlOut1|Select-Object Response,Access_Page,Total_Request| ConvertTo-HTML -PreContent '</pre> <h2>Statistics Based on IIS Reponse, Page and Count</h2> <pre> ' -head $a |Out-String $SqlOut2=Invoke-Sqlcmd -ServerInstance $SQLServer -Query $Query2 -QueryTimeout 65535 $PrintOut2=$SqlOut2|Select-Object Total_Request,UserName | ConvertTo-HTML -PreContent '</pre> <h2>Statistics Based on UserName and Count</h2> <pre> ' -head $a |Out-String ConvertTo-Html -Title "IIS Log Analysis" -PostContent $PrintOut1,$PrintOut2 |Out-File $HTMLFile Invoke-Item $HTMLFile #open html after processing } AnalyseIIS #OR Run with Parameter, Eg. #AnalyseIIS -SQLServer SQL1 -HTMLFile C:\TEMP\IISReview.html
You can have different select queries based on analysis you need and I believe my last two blogs can give you direction on how large set of text files can be uploaded to SQL then analyze it using PowerShell.
Advertisements