Hi,
I found this query on Thwack but there is a problem I need to classify per group that group have a customer name with a user
=========SQL=========
SELECT DISTINCT
ahv.Name 'Alert Name'
,ahv.RelatedNodeCaption 'Parent Device'
,ahv.EntityCaption 'Alert Object'
,ISNULL(lastOne.Qty,0) 'Last 1 Day'
,ISNULL(lastSeven.Qty,0) 'Last 7 Days'
,ISNULL(lastThirty.Qty,0) 'Last 30 Days'
FROM AlertHistoryView ahv
FULL OUTER JOIN
(SELECT
Name
,RelatedNodeCaption
,EntityCaption
,COUNT(1) 'Qty'
FROM AlertHistoryView
WHERE EventTypeWord = 'Triggered'
AND TimeStamp > (GetDate()-1)
GROUP BY Name, RelatedNodeCaption, EntityCaption) lastOne ON ahv.Name = lastOne.Name AND ahv.RelatedNodeCaption = lastOne.RelatedNodeCaption AND ahv.EntityCaption = lastOne.EntityCaption
FULL OUTER JOIN
(SELECT
Name
,RelatedNodeCaption
,EntityCaption
,COUNT(1) 'Qty'
FROM AlertHistoryView
WHERE EventTypeWord = 'Triggered'
AND TimeStamp > (GetDate()-7)
GROUP BY Name, RelatedNodeCaption, EntityCaption) lastSeven ON ahv.Name = lastSeven.Name AND ahv.RelatedNodeCaption = lastSeven.RelatedNodeCaption AND ahv.EntityCaption = lastSeven.EntityCaption
FULL OUTER JOIN
(SELECT
Name
,RelatedNodeCaption
,EntityCaption
,COUNT(1) 'Qty'
FROM AlertHistoryView
WHERE EventTypeWord = 'Triggered'
AND TimeStamp > (GetDate()-30)
GROUP BY Name, RelatedNodeCaption, EntityCaption) lastThirty ON ahv.Name = lastThirty.Name AND ahv.RelatedNodeCaption = lastThirty.RelatedNodeCaption AND ahv.EntityCaption = lastThirty.EntityCaption
WHERE ahv.Name IS NOT NULL
AND
(
lastOne.Qty > 0 OR
lastSeven.Qty > 0 OR
lastThirty.Qty > 0
)
-- AND ahv.Name IN ('Alert me when an application goes down','Alert me when a component goes down')
ORDER BY ahv.Name, ISNULL(lastThirty.Qty,0) DESC, ahv.RelatedNodeCaption, ahv.EntityCaption
======================
If a user check his account he see every other node's but I want to see only the group that he belong to
=======IMAGE=========> this is not correct this user see other customers
======================
