Hi,
Is there any way to change this sql header in the Custom SQL alert ?
I need insert a little different sql.
Thanks
Are you still using the alert report writer on the Orion poller?
completely unsupported, of course, but....***note gets tricky with quotes and double quoting to escape quote b/c writing SQL to column...test in non-prod or on dummy alert first and always BU DB before doing work.
uses alertdefinitions table (pre 11.x)...and this is complex example... I select then update
select * from alertdefinitions where AlertName like '%Shared Volume Groups%'
-- update
alertdefinitions
set ResetQuery='SELECT distinct(Containers_AlertsAndReportsData.GroupID) AS NetObjectID, Containers_AlertsAndReportsData.GroupName AS Name
FROM Containers_AlertsAndReportsData
/*SplitMarker*/
INNER JOIN
( select vc.caption, total, issues, convert(varchar(10),convert(int,100*issues/total)) as perc
FROM ( select v.caption, count(v.caption) as total
from volumes v WHERE caption in
( select distinct(name)
from containermembersnapshots
WHERE containerID in
( select containerid
from containers
WHERE name like ''%dsmounts-%'' --NOTE 2x single qoutes
)
group by caption
) as vc
left join
( select caption, count(caption) as issues
from volumes
WHERE caption in
WHERE name like ''%dsmounts-%''
AND
(
(Critical IS NOT NULL)
(VolumePercentUsed >= Critical)
) as vcwe
ON vc.caption=vcwe.caption
WHERE (ISNULL(convert(varchar(10),convert(int,100*issues/total)),0) <=50)
) as a
ON Containers_AlertsAndReportsData.GroupMemberName=a.caption'
where AlertName like '%Shared Volume Groups%'
