owa-first.ps1

gistfile1.ps1

#requires -version 4.0

cls

[System.Reflection.Assembly]::LoadWithPartialName('System.IO.Compression.FileSystem') | Out-Null

$SourcePattern = '\\ul-fs\logs\*cas*.zip'
$CurrentDir = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition)

$ProgDir = (${env:ProgramFiles(x86)}, ${env:ProgramFiles} -ne $null)[0]
$LogParserExe = (Join-Path $ProgDir '\Log Parser 2.2\LogParser.exe')

If(-not(Test-Path -path $LogParserExe)){
    'LogParser.exe is missing at {0}' -f $LogParserExe
    'download link: https://www.microsoft.com/en-us/download/details.aspx?id=24659'
    exit 1
}

$processedFile = (Join-Path $CurrentDir 'processed.txt')
$processedDir = (Join-Path $CurrentDir 'processed')

if(!(Test-Path -Path $processedDir )){
   New-Item -ItemType directory -Path $processedDir | Out-Null
}

If(Test-Path -path $processedFile){
    $processed = Get-Content $processedFile
}Else{
    $processed = [array] @();
}

$cas = gci –Path $SourcePattern

$cas | % {
[String] $n = $_.Name
[String] $f = $_.FullName
 If($processed -notcontains $n){
 'Processing {0}...' -f $n

 $tempDir = (Join-Path $CurrentDir ([System.Guid]::NewGuid().toString()))

 if(!(Test-Path -Path $tempDir )){
   New-Item -ItemType directory -Path $tempDir | Out-Null
 }

 [System.IO.Compression.ZipFile]::ExtractToDirectory($f, $tempDir)

 $csv = (Join-Path $processedDir ($n -replace '.zip', '.csv'))


 $query = @"
  SELECT date as casDate, 
         cs-uri-stem as uri, 
         coalesce( extract_token(REPLACE_STR(to_lowercase(cs-username), 'kontur/', 'kontur\\'),1,'kontur\\'), extract_token(to_lowercase(cs-username),0,'@kontur')) as loginName, 
         c-ip as ip, 
         cs(User-Agent) as useragent
   INTO {0} 
   FROM '{1}\*' 
    
   WHERE cs-username IS NOT NULL 
   AND sc-status=200 
   AND cs-username NOT LIKE 'healthmailbox%' 
   AND c-ip NOT LIKE '192.168%' 
   AND c-ip NOT LIKE '172.16%'
   AND c-ip NOT LIKE '10.%'
   AND c-ip <> '::1'
   AND c-ip NOT LIKE '46.17.20%'
"@ 
 
& $LogParserExe -i:IISW3C ($query -f $csv, $tempDir) -o:CSV

 Remove-Item $tempDir -recurse

 add-content $processedFile $n

 }

}