Tracking IIS Users in Orion - Who is Running Expensive Reports?

I have a situation this morning where one of my Orion users is attempting to run a very ugly report that results in 100% CPU utilization of my monster SQL server. All I can tell is what the query is, and that it's being run from my web portal (additional web server).

Is there a way to track an Orion user (AD user) and tie it to the query being run? I'm having a bear of a time trying to find my user so I can help them get their data without killing my SQL server. Nobody is raising their hand to fess up, but they keep running trying to get at the data...

What's the best way to track down my Orion abuser?

Find more posts tagged with

poor web performance

iis

orion user

sql server cpu

Accepted answers

aLTeReGo

The long request wait time will appear in your IIS logs with the IP address of the user's machine. You can then use UDT to track that back to the specific user if the IP address alone is insufficient. Alternatively, if you are using Windows Integrated Authentication with Orion, the Windows username of the user associated with that report query will als appear in the IIS logs.

All comments

aLTeReGo

lag

Thanks, aLTeReGo. I get the feeling LEM would be helpful here here, too. I'm on the the right track now. As a rudimentary powershell script to search the logs and output a csv of matching criteria, I'm working with the following in case anyone is interested in my ugly tool. I haven't exactly narrowed it down to the user calling the problem query, but I have a lot of data to chew on when folks start complaining about "Slowrion."

[STRING]$workingDir = "C:\orionScripts\IIS Log Parser\"

[STRING]$outputFile = "IIS_Output.csv"

[STRING]$output = $workingDir +$outputFile

[INT]$logDuration = 50000

[DATETIME]a = Get-Date()

[DATETIME]$beginWindow = "09:00:00"

[DATETIME]$endWindow = "12:00:00"

# [STRING]$logDir = $workingDir

# [STRING]$logToCheck = "TEST_IIS_u_ex171031.log"

[STRING]$logDir = "C:\inetpub\logs\LogFiles\W3SVC2\"

[STRING]$logToCheck = "u_ex171031.log"

[STRING]$IISLogPath = $logDir + $logToCheck

$IISLogFileRaw = [System.IO.File]::ReadAllLines($IISLogPath)

$headers = $IISLogFileRaw[3].split(" ")

$headers = $headers | where {$_ -ne "#Fields:"}

$IISLogFileCSV = Import-Csv -Delimiter " " -Header $headers -Path $IISLogPath

$IISLogFileCSV = $IISLogFileCSV | where {$_.date -notlike "#*"}

# $timeTaken = $IISLogFileCSV | foreach {$_.$("time-taken")}

FOREACH( $logDatum IN $IISLogFileCSV ) {

[INT]$timeTaken = $logDatum.$("time-taken")

[DATETIME]$timeLoaded = $logDatum.$("time")

# IF( $logDatum.$("sc-status") -NE "200" ){

IF( $timeTaken -gt $logDuration ){

# IF( ($logDatum.$("time") -gt $beginWindow) -AND ($logDatum.$("time") -lt $endWindow) ){

Write-Host $logDatum.$("c-ip") " - " $logDatum.$("cs-username") " - " $logDatum.$("time-taken")

Write-Output $logDatum | Export-CSV -Append -Path $output -NoTypeInformation

}ELSE{}

}

lag

With a little digging between expensive sql queries and the IIS logs, it turns out someone added some of the network wide charts to the node details page...

lag

The resource seemed to be a part of the problem. Removing it has relieved the CPU load on the SQL server, but the additional web server is now very slow. Some experience, different symptoms now.

lag

Memory has been running at a nominal level up to this event, and now it's pegged. I wound up rebuilding the web server, and am waiting on more memory just in case the solution is simply physical limitations. I figure I would have noticed before now, though, since we performed the upgrade about three weeks ago. I should have the web server back in service this week. I noticed that moving traffic back to our main Orion, I'm seeing huge memory consumption there now, but well under the physical limitations on that box (64GB). I love a good mystery, but I have real work that needs to get done... And so the saga of SlOwrion continues.