I kept getting Critical and Warning alerts because I created the play back on my Orion server in my DC, but then my Virgina and Nor Cal AWS servers were not nearly as fast (< .5 sec on orion server). So I let them run for a bit and wanted to get the averages, then I wanted to know which ones weren't close. So I thoung I would share my work, here is the SQL query I ended up using:
SELECT top 1000 seum_agents.name, seum_Transactions.name, SEUM_RecordingSteps.stepid, SEUM_RecordingSteps.name, SEUM_TransactionSteps.WarningThreshold, SEUM_TransactionSteps.CriticalThreshold, SEUM_TransactionSteps.OptimalThreshold, AVG(SEUM_StepResponseTime_Detail.Duration)*2 as warn, AVG(SEUM_StepResponseTime_Detail.Duration)*3 as crit, AVG(SEUM_StepResponseTime_Detail.Duration) as average
FROM [SEUM_TransactionSteps]
inner join seum_Transactions on seum_Transactions.transactionid = SEUM_TransactionSteps.transactionid
inner join seum_agents on seum_agents.agentid = seum_transactions.agentid
inner join SEUM_RecordingSteps on SEUM_RecordingSteps.stepid = SEUM_TransactionSteps.stepid
inner join SEUM_StepResponseTime_Detail on SEUM_StepResponseTime_Detail.transactionid = SEUM_TransactionSteps.transactionid and SEUM_StepResponseTime_Detail.TransactionStepId = SEUM_TransactionSteps.TransactionStepId
where SEUM_StepResponseTime_Detail.status <> 2 and SEUM_StepResponseTime_Detail.status <> 0 and seum_agents.name = 'Orion Server'
group by seum_agents.name, seum_Transactions.name, SEUM_RecordingSteps.name, SEUM_RecordingSteps.stepid, SEUM_TransactionSteps.WarningThreshold, SEUM_TransactionSteps.CriticalThreshold, SEUM_TransactionSteps.OptimalThreshold
having (
abs(SEUM_TransactionSteps.WarningThreshold - AVG(SEUM_StepResponseTime_Detail.Duration)*2) > 800
or abs(SEUM_TransactionSteps.CriticalThreshold - AVG(SEUM_StepResponseTime_Detail.Duration)*3) > 1200
or abs(SEUM_TransactionSteps.OptimalThreshold - AVG(SEUM_StepResponseTime_Detail.Duration)) > 400
)
order by seum_agents.name, seum_Transactions.name, SEUM_RecordingSteps.stepid
