Where is Statistic threshold info stored in orion?

For SAM components it can be a real pain to pick out the threshold details and display what exactly makes them turn red, this is the query that I use to in my alert message to display the source of component issues.  It covers all the types of monitors we use but might not cover everything in your environment.

${N=SwisEntity;M=Application.Node.Caption} ${N=SwisEntity;M=Application.ApplicationAlert.ApplicationName} ${N=SwisEntity;M=ComponentAlert.ComponentName} is ${N=SwisEntity;M=Status;F=Status} `
Reason: ${SQL: select isnull((
select
cast(concat(case
when cst.ComponentType in (1,8) and cs.Availability=2 then concat(cs.InstanceCount, ' instances of ',serv.value, ' found')
when cst.ComponentType in (2,37,38,39,55,63) and cs.Availability=2 then cst.ErrorMessage
when cst.ComponentType in (9) and cs.Availability=2 then concat(serv.value, ' ',cst.ErrorMessage)
when cst.ComponentType in (6,14) and cs.Availability in (2,12) then concat(replace(replace(replace(url.value,'${IP}',n.IP_Address),'${port}',port.value),'${Node.Caption}',n.caption), ' returned: ',cst.ErrorMessage)
when cst.ComponentType in (42) and cs.Availability in (2,5,6) then concat(cs.StatisticData, ' events matched the rule, see component view for details')
when cst.ComponentType in (21) and cs.Availability=2 then concat(cav.multivaluestatistics, ' - ',replace(cav.multivaluemessages,'Statistic:',''))
when cst.ComponentType in (1,6,8,9,14,20,21,45,32,37) and cs.Availability = 6 then concat(
case when multivaluestatistics is not null then concat(cav.multivaluestatistics, ' - ',replace(cav.multivaluemessages,'Statistic:',''))
else '' end
, case when tbc.ThresholdName is not null then concat(tbc.thresholdname ,' is '
, case when tbc.thresholdname = 'CPU' then cst.ComponentPercentCPU
when tbc.thresholdname = 'PMem' then cst.ComponentPercentMemory
when tbc.thresholdname = 'VMem' then cst.ComponentPercentVirtualMemory
when tbc.thresholdname = 'IOReadOperationsPerSec' then cst.ComponentIOReadOperationsPerSec
when tbc.thresholdname = 'IOWriteOperationsPerSec' then cst.ComponentIOWriteOperationsPerSec
when tbc.thresholdname = 'IOTotalOperationsPerSec' then cst.ComponentIOTotalOperationsPerSec
when tbc.thresholdname = 'Response' then cst.ComponentResponceTime
when tbc.thresholdname = 'StatisticData' then cst.ComponentStatisticData
end
, ', threshold is ' , case
when tbc.thresholdoperator = 0 then 'greater than '
when tbc.thresholdoperator = 1 then 'greater than or equal to '
when tbc.thresholdoperator = 2 then 'equal to '
when tbc.thresholdoperator = 3 then 'less than or equal to '
when tbc.thresholdoperator = 4 then 'less than '
when tbc.thresholdoperator = 5 then 'not equal to '
end
, cast(tbc.critical as varchar)
, ' for ', isnull(isnull(ovr.criticalpolls,t.criticalpolls),1)
, case when isnull(ovr.criticalpolls,t.criticalpolls) != isnull(ovr.criticalpollsinterval,t.criticalpollsinterval) then concat(' of ',isnull(ovr.criticalpollsinterval,t.criticalpollsinterval)) else '' end
, case when isnull(ovr.criticalpolls,t.criticalpolls) > 1 then ' polls' else ' poll' end, CHAR(13)
) else '' end)
when cs.Availability=2 then '...'
else 'To be categorized...'
end, CHAR(10)) as xml) as Reason

from nodes n
join APM_Application a on a.nodeid=n.nodeid
join APM_Component c on c.applicationid=a.id
join APM_CurrentComponentStatus cs on c.id=cs.componentID
join APM_CurrentStatistics cst on cst.componentid=c.id
join APM_StatusMetadata st on st.ApmStatusValue=cs.Availability
left join APM_ThresholdsByComponent tbc on tbc.ComponentID=c.id and tbc.Critical < 1.797E+308
and (
(tbc.thresholdname = 'StatisticData' and (
(tbc.thresholdoperator = 0 and (cst.componentstatisticdata > statisticcritical)) or
(tbc.thresholdoperator = 1 and (cst.componentstatisticdata >= statisticcritical)) or
(tbc.thresholdoperator = 2 and (cst.componentstatisticdata = statisticcritical)) or
(tbc.thresholdoperator = 3 and (cst.componentstatisticdata <= statisticcritical)) or
(tbc.thresholdoperator = 4 and (cst.componentstatisticdata < statisticcritical)) or
(tbc.thresholdoperator = 5 and (cst.componentstatisticdata != statisticcritical))))

or

(tbc.thresholdname = 'Response' and (
(tbc.thresholdoperator = 0 and (cst.componentresponcetime > responsetimecritical)) or
(tbc.thresholdoperator = 1 and (cst.componentresponcetime >= responsetimecritical)) or
(tbc.thresholdoperator = 2 and (cst.componentresponcetime = responsetimecritical)) or
(tbc.thresholdoperator = 3 and (cst.componentresponcetime <= responsetimecritical)) or
(tbc.thresholdoperator = 4 and (cst.componentresponcetime < responsetimecritical)) or
(tbc.thresholdoperator = 5 and (cst.componentresponcetime != responsetimecritical))))

or

(tbc.thresholdname = 'CPU' and (
(tbc.thresholdoperator = 0 and (cst.componentpercentcpu > cpucritical)) or
(tbc.thresholdoperator = 1 and (cst.componentpercentcpu >= cpucritical)) or
(tbc.thresholdoperator = 2 and (cst.componentpercentcpu = cpucritical)) or
(tbc.thresholdoperator = 3 and (cst.componentpercentcpu <= cpucritical)) or
(tbc.thresholdoperator = 4 and (cst.componentpercentcpu < cpucritical)) or
(tbc.thresholdoperator = 5 and (cst.componentpercentcpu != cpucritical))))

or

(tbc.thresholdname = 'PMem' and (
(tbc.thresholdoperator = 0 and (cst.componentpercentmemory > physicalmemorycritical)) or
(tbc.thresholdoperator = 1 and (cst.componentpercentmemory >= physicalmemorycritical)) or
(tbc.thresholdoperator = 2 and (cst.componentpercentmemory = physicalmemorycritical)) or
(tbc.thresholdoperator = 3 and (cst.componentpercentmemory <= physicalmemorycritical)) or
(tbc.thresholdoperator = 4 and (cst.componentpercentmemory < physicalmemorycritical)) or
(tbc.thresholdoperator = 5 and (cst.componentpercentmemory != physicalmemorycritical))))

or

(tbc.thresholdname = 'VMem' and (
(tbc.thresholdoperator = 0 and (cst.componentpercentvirtualmemory > virtualmemorycritical)) or
(tbc.thresholdoperator = 1 and (cst.componentpercentvirtualmemory >= virtualmemorycritical)) or
(tbc.thresholdoperator = 2 and (cst.componentpercentvirtualmemory = virtualmemorycritical)) or
(tbc.thresholdoperator = 3 and (cst.componentpercentvirtualmemory <= virtualmemorycritical)) or
(tbc.thresholdoperator = 4 and (cst.componentpercentvirtualmemory < virtualmemorycritical)) or
(tbc.thresholdoperator = 5 and (cst.componentpercentvirtualmemory != virtualmemorycritical))))

or

(tbc.thresholdname = 'IOReadOperationsPerSec' and (
(tbc.thresholdoperator = 0 and (cst.componentioreadoperationspersec > ioreadoperationsperseccritical)) or
(tbc.thresholdoperator = 1 and (cst.componentioreadoperationspersec >= ioreadoperationsperseccritical)) or
(tbc.thresholdoperator = 2 and (cst.componentioreadoperationspersec = ioreadoperationsperseccritical)) or
(tbc.thresholdoperator = 3 and (cst.componentioreadoperationspersec <= ioreadoperationsperseccritical)) or
(tbc.thresholdoperator = 4 and (cst.componentioreadoperationspersec < ioreadoperationsperseccritical)) or
(tbc.thresholdoperator = 5 and (cst.componentioreadoperationspersec != ioreadoperationsperseccritical))))

or

(tbc.thresholdname = 'IOWriteOperationsPerSec' and (
(tbc.thresholdoperator = 0 and (cst.componentiowriteoperationspersec > iowriteoperationsperseccritical)) or
(tbc.thresholdoperator = 1 and (cst.componentiowriteoperationspersec >= iowriteoperationsperseccritical)) or
(tbc.thresholdoperator = 2 and (cst.componentiowriteoperationspersec = iowriteoperationsperseccritical)) or
(tbc.thresholdoperator = 3 and (cst.componentiowriteoperationspersec <= iowriteoperationsperseccritical)) or
(tbc.thresholdoperator = 4 and (cst.componentiowriteoperationspersec < iowriteoperationsperseccritical)) or
(tbc.thresholdoperator = 5 and (cst.componentiowriteoperationspersec != iowriteoperationsperseccritical))))

or

(tbc.thresholdname = 'IOTotalOperationsPerSec' and (
(tbc.thresholdoperator = 0 and (cst.componentiototaloperationspersec > iototaloperationsperseccritical)) or
(tbc.thresholdoperator = 1 and (cst.componentiototaloperationspersec >= iototaloperationsperseccritical)) or
(tbc.thresholdoperator = 2 and (cst.componentiototaloperationspersec = iototaloperationsperseccritical)) or
(tbc.thresholdoperator = 3 and (cst.componentiototaloperationspersec <= iototaloperationsperseccritical)) or
(tbc.thresholdoperator = 4 and (cst.componentiototaloperationspersec < iototaloperationsperseccritical)) or
(tbc.thresholdoperator = 5 and (cst.componentiototaloperationspersec != iototaloperationsperseccritical))))

)
left join APM_Threshold t on c.id=t.id and t.thresholdname=tbc.thresholdname and t.istemplate=1
left join APM_Threshold ovr on c.id=ovr.id and ovr.thresholdname=tbc.thresholdname and ovr.istemplate=0
left join apm_componentalertvariable cav on cav.componentid=c.id
left join APM_ComponentDetails url on c.id=url.ID and url.[Key] = 'URL'
left join APM_ComponentDetails port on c.id=port.ID and port.[Key] = 'portnumber'
left join APM_ComponentDetails serv on c.id=serv.ID and serv.[Key] in ('ServiceName','ProcessName')

where c.id = ${N=SwisEntity;M=ComponentID}
FOR XML PATH('') ),'No issue identified'); }

Thanks for the help but I am in the same data deadzone.

<Reason>To be categorized... </Reason>
<Reason>To be categorized... </Reason>
<Reason>To be categorized... </Reason>
<Reason>To be categorized... </Reason>

There is still some disconnect between the ComponentID and statistic data. 
There must be some connection between these two data points. SW is able to alert on them individually.

So far the only way I have found to get the statistic value from the Component Monitors is

SWQL:SELECT A.MultiValueStatistics
FROM Orion.APM.ComponentAlert A
JOIN Orion.APM.Component C ON A.ComponentID=C.ComponentID
WHERE C.AncestorDisplayNames like '%[component name]%'

This gives me a not so helpful string 'Val1:0.00, Val2:0.05, Val3:0.00, Val4:0.09, Val5:0.00, Val6:0.81, Val7:0.00, Val8:0.12' that I have parsed out using a CASE

WHEN A.MultiValueStatistics LIKE '%Val1:1.00%' THEN 'Flag for value1 set'

This can't be the way SW does things internally. There doesn't seem to be any reference to a threshold ID or statisticwarning in these tables that I can use. There has to be an intermediate table where multi-value statistic data and it's corresponding attributes are stored.  

Or SW is magic.