Exportar los logs del IIS de Sharepoint a una base de datos

La idea es exportar todos los logs generados del IIS para generar reportes custom sobre el uso de Sharepoint. Después podría generar reportes de SSRS para generar reportes planificados para monitorear y sacar estadísticas del uso de Sharepoint. El script de powershell sólo toma el archivo del día anterior, de esta manera se va procesando diariamente sin generar mucha sobrecarga sobre la base de datos o sobre el IIS al procesar una gran cantidad de datos. De todos modos el script podrá ser configurado para procesar la cantidad de días que requieran (ej: 15 días previos). Siempre se puede ejecutar desde logparse las consultasm pero quería generar reportes más detallados y que se fácil generar reportes históricos.

Primero instalamos LogParser en el servidor donde se quiere procesar los logs

http://www.microsoft.com/en-us/download/details.aspx?id=24659

Segundo, en el IIS de Sharepoint, configurar el logging de la plataforma

Seteo los siguientes fields para loguear

Tercero, creo la base de datos y la tabla donde se guardaran los logs. Yo estaré usando una sola tabla para cargar logs de varios web applications, pero es recomendable crear una tabla por web application si van a mantener logs de varios días y si tienen muchas accesos al IIS.

La base se llamará IISLogs

A continuación creo la tabla, llamada IIS

Después creo un usuario de SQL (se puede hacer también con windows authentication, pero va evitar complejidad, uso SQL Authentication)

El usuario debe tener el permiso de dbowner sobre la tabla “IIS”.

Cuarto, subimos el siguiente script al servidor, y realizamos las configuraciones necesarias. Hay que cambiar las variables en rojo.

$LogParserExe = "C:\Program Files (x86)\Log Parser 2.2\LogParser.exe"

Function ProcessLogFiles ($RequestPath,$urlSite) {    
    try
    {
        $Date = Get-Date
        $Date = $Date.adddays(-1)
        $Date2Str = $Date.ToString("yyyMMdd")
        $Files = gci $RequestPath -Filter "*.log"
        ForEach ($File in $Files){
            $FileDate = $File.creationtime
            $CTDate2Str = $FileDate.ToString("yyyyMMdd")
            if ($CTDate2Str -eq $Date2Str) {
                Write-Host "Procesando el archivo " +  $File.FullName
              
                [string] $query = "SELECT" `
                                  + " TO_TIMESTAMP(date, time) AS EntryTime" `
                                  + ",c-ip AS ClientIpAddress" `
                                  + ",cs-method AS Method" `
                                  + ",STRCAT('"+ $urlSite + "',cs-uri-stem) AS UriStem" `
                                  + ",cs-uri-query AS UriQuery" `
                                  + ",s-port AS Port" `
                                  + ",cs-username AS Username" `
                                  + ",cs(User-Agent) AS UserAgent" `
                                  + ",sc-status AS HttpStatus" `
                                  + ",sc-substatus AS HttpSubstatus" `
                                  + ",sc-win32-status AS Win32Status" `
                                  + ",sc-bytes AS BytesFromServerToClient" `
                                  + ",cs-bytes AS BytesFromClientToServer" `
                                  + ",time-taken AS TimeTaken INTO IIS " `
                                  + " FROM " + $File.FullName

                Write-Host $query
              
                [string[]] $parameters = @()
   
                $parameters += $query
                $parameters += "-i:IISW3C"
                $parameters += "-o:SQL"
                $parameters += "-server:NOMBRESERVIDOR"
                $parameters += "-database:IISLogs"
                $parameters += "-username:USUARIO_CREADO"
                $parameters += "-password:PASSWORD_DEL_USUARIOCREADO"               
   
                Write-host "Parameters: $parameters"
   
                Write-Host "Importing log files to database..."
                & $LogParserExe $parameters

            }#if ($CTDate2Str -eq $Date2Str)

        }#ForEach ($File in $Files)
    }
    catch
    {
        write-host "Caught an exception: $_.Excepti" -ForegroundColor Red
        write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
        write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red
    }

}#Function ProcessLogFiles ($RequestPath)

ProcessLogFiles "D:\Data\Microsoft\LogsIIS\W3SVC378636303" "http://ur_webapplication"

ProcessLogFiles "D:\Data\Microsoft\LogsIIS\W3SVC582736404" “http://ur_webapplicationOTHER”

Les dejo el archivo en el siguiente link para descargar también: descargar

A continuación podemos crear una tarea planificada para que se ejecute todos los días el script de powershell. Link

También se debería crear una tarea planificada en SQL Server para que vaya eliminando los logs que tengan N días de antiguedad.

Ej: cada 2 semanas voy a borrar los logs.

Agrego una tarea “Execute T-SQL Statement Task

En el caso que quieras ejecutar el script para que procese logs de N días, debes cambiar la siguiente línea.

$Date = $Date.adddays(-1)

Una vez que tenemos los datos en el SQL podemos hacer consultas reportes muy detallados (con Excel) o planificados(SSRS). Veamos un par de ejemplos,.

Ejemplo 1:  Navegadores más usados.

Ejemplo 2: uso de excel 2013. Voy a sacar la cantidad de bytes que envío cada usuario y recibió del servidor.

Ahora lo proceso con PivotTable

Ejemplo 3: resumen de HTTP Status

Veo que tengo muchos request con status 401, lo cual debería revisar. También veo que tengo muchos request con status 304, es que no hay cambios entre request solicitados (en este caso es buena señal.)

Pueden sacar más queries del siguiente link:

http://mlichtenberg.wordpress.com/2011/02/03/log-parser-rocks-more-than-50-examples/

http://logparserplus.com/Examples