So when you do the custom sql based alert you have to tie it back to some kind of object to have an alert on it, alerts don't exist in orion on their own, only in context with their relationship to an object. Sometimes I have had to just come up with placeholder objects to tie my alerts to, for your query you could do something like this to tie it back to your polling engine as the "object" of the alert.
SELECT Engines.ServerName, Engines.EngineID FROM Engines
join (SELECT sum(isnull(componentstatisticdata,0)) as number, (select top 1 engineid from engines) as EngineID FROM [dbo].[APM_CurrentStatistics] a
where applicationname ='service to count the mdse%'
) t2 on t2.engineid=engines.engineid
where t2.number > 107
Another approach that may be more straight forward since you have SAM is just to make this a component on a SAM template with a SQL user experience monitor, put your query in there and use that SUM as the statistic, set your threshold there in the component monitor. Then just make sure you are alerting on any components that go critical.
Thanks for the input and direction. When using the SQL Server User Experience Monitor should I be testing and running it against the Solar Wind DB? Since my query of data is extracted from there? Just making sure I doing it correctly. Thanks.
Yep, I'd just add the orion db as a node, add a SQL user experience monitor to a new template, set it up with all the credentials and such to access from there.