none
Searching IIS Logs For Outlook Anywhere Connections RRS feed

  • Question

  • Hi everyone,

    We have enabled advanced IIS logging on our Exchange 2016 servers and I've been tasked with preparing a report from those logs showing users that connect from external IPs using Outlook Anywhere. Ideally we want the report to show usernames for the last 30 days. Since we enabled advanced logging in IIS our log files have massively grown in average size (some at 4GB) and that makes searching through the logs much harder. We have 11 Exchange servers and searching across all of them for the last 30 days is extremely slow. I wrote a PowerShell script to do this and in an attempt to make the output file smaller I am excluding internal and private IP addresses. Here is the script:

    $Servers = Get-ExchangeServer | Select Name | Sort Name
    
    ForEach ($Server in $Servers)
    {
        $logs = 
        $ServerName = $Server | %{$_ -replace '@{Name=', ''} | %{$_ -replace '}', ''}
        $log_path = "\\" + $ServerName + "\d$\inetpub\logs\LogFiles\W3SVC1"
        $logs = Get-ChildItem $log_path -Recurse -Include *.log | ? { $_.LastWriteTime -gt (Get-Date).AddDays(-30) } | Select Name
    
        ForEach ($log in $logs)
        {
            $logname = $log | %{$_ -replace '@{Name=', ''} | %{$_ -replace '}', ''}
            $filename = $log_path + "\" + $logname
            Get-Content $filename | %{$_ -replace '#Fields: ', ''} | ?{$_ -notmatch '^#'} | %{$_ -replace 'Source-IP', 'SourceIP'} | ConvertFrom-Csv -Delimiter ' ' | Select date,time,s-ip,cs-method,cs-uri-stem,cs-uri-query,s-port,cs-username,c-ip,'cs(User-Agent)','cs(Referer)',sc-status,sc-substatus,sc-win32-status,time-taken,SourceIP | Where-Object{$_.SourceIP -notmatch "^10.|^192.168.|^172.|^-"} | Export-CSV \\FileServer\ExchangeLogs\HugeCombinedIISLog.csv -NoTypeInformation -Append
        }
    }

    The output file is still huge, well over 100GB, and I don't have anything that can open a file that size. I thought about further restricting the search to {$_.cs-uri-stem -eq '/rpc/rpcproxy.dll'} but I'm not entirely sure how to do that. How do I include search criteria with the Where-Object statement above while also excluding IP addresses?

    Can anybody please offer any assistance here?

    Thanks

    Thursday, January 10, 2019 6:25 PM

Answers

  • Where-Object{$_.SourceIP -notmatch '^10\.|^192\.168\.|^172\.|^-' -and $_.'cs-uri-stem' -match '/rpc/rpcproxy.dll'}

    You need to learn basic PowerShell.  You do not know how to define scriptblocks and how to use parens.

    help Where-Object -ShowWindow

    Test case:

    PS D:\scripts> '/rpc/rpcproxy.dll' -match '/rpc/rpcproxy.dll'
    True


    \_(ツ)_/




    • Edited by jrv Thursday, January 10, 2019 7:14 PM
    • Marked as answer by Jason Hollenberg Thursday, January 10, 2019 7:16 PM
    Thursday, January 10, 2019 7:10 PM

All replies

  • Where{ $_ -match '/rpc/rpcproxy.dll'

    \_(ツ)_/

    Thursday, January 10, 2019 6:41 PM
  • How do I combine that with my existing Where statement that has a "notmatch" in it and I'm searching a completely different column? 

    Where-Object{$_.SourceIP -notmatch "^10.|^192.168.|^172.|^-"}

    Like this?

    Where-Object({$_.SourceIP -notmatch "^10.|^192.168.|^172.|^-"} -or {$_ -match '/rpc/rpcproxy.dll'})

    Thursday, January 10, 2019 6:46 PM
  • Use "-and"


    \_(ツ)_/

    Thursday, January 10, 2019 6:47 PM
  • or something like Where-Object -FilterScript { $_ -ilike “*msrpc*” }

    Thanks, Ashish (I can be wrong but can't be rude) “Tell me and I forget, teach me and I may remember, involve me and I learn.” MCITP, MCT, MCSE

    Thursday, January 10, 2019 6:49 PM
  • or something like Where-Object -FilterScript { $_ -ilike “*msrpc*” }

    Thanks, Ashish (I can be wrong but can't be rude) “Tell me and I forget, teach me and I may remember, involve me and I learn.” MCITP, MCT, MCSE

    "match" always works better in these cases.  It doesn't need wildcards a nd can sniff out difficult patterns.


    \_(ツ)_/

    Thursday, January 10, 2019 6:52 PM
  • I tried this:

    Where-Object({$_.SourceIP -notmatch "^10.|^192.168.|^172.|^-"} -and {$_ -match '/rpc/rpcproxy.dll'})

    ...and it produced an empty output file. I tried this:

    Where-Object({$_.SourceIP -notmatch "^10.|^192.168.|^172.|^-"} -and {'$_.cs-uri-stem' -match '/rpc/rpcproxy.dll'})

    ...and got the same result. When I tried this it gave an error:

    Where-Object({$_.SourceIP -notmatch "^10.|^192.168.|^172.|^-"} -and {$_.cs-uri-stem -match '/rpc/rpcproxy.dll'})
    Get-IISLogs.ps1:16 char:410
    + ... SourceIP -notmatch "^10.|^192.168.|^172.|^-"} -and {$_.cs-uri-stem -m ...
    +                                                              ~~~~
    Unexpected token '-uri' in expression or statement.
    At Get-IISLogs.ps1:16 char:414
    + ... eIP -notmatch "^10.|^192.168.|^172.|^-"} -and {$_.cs-uri-stem -match  ...
    +                                                             ~~~~~
    Unexpected token '-stem' in expression or statement.
        + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : UnexpectedToken
    
    I'm wondering if the problem is caused by the fact that cs-uri-stem and SourceIP are different columns in my CSV?

    Thursday, January 10, 2019 7:04 PM
  • Where-Object{$_.SourceIP -notmatch '^10\.|^192\.168\.|^172\.|^-' -and $_.'cs-uri-stem' -match '/rpc/rpcproxy.dll'}

    You need to learn basic PowerShell.  You do not know how to define scriptblocks and how to use parens.

    help Where-Object -ShowWindow

    Test case:

    PS D:\scripts> '/rpc/rpcproxy.dll' -match '/rpc/rpcproxy.dll'
    True


    \_(ツ)_/




    • Edited by jrv Thursday, January 10, 2019 7:14 PM
    • Marked as answer by Jason Hollenberg Thursday, January 10, 2019 7:16 PM
    Thursday, January 10, 2019 7:10 PM
  • Thanks for the help, that worked.
    Thursday, January 10, 2019 7:16 PM