• State Suggested Answer
  • Replies 8 replies
  • Answers 3 answers
  • Subscribers 20 subscribers
  • Views 614 views
  • Users 0 members are here
Options
Related

send help please i have a query that creates table for acknowledge user and "minutes until acknowledged" i would like to have it break down to peruser

nmoura38

here is the query 

--report on alerts triggered

select AccountID as [user],EntityCaption as [Trigger Object]
,case
WHEN RelatedNodeCaption=EntityCaption THEN 'Self'
When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
End as [Parent Node]
,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]
,case when ack.timestamp is null then 'N/A'
else tostring(minutediff(ah.TimeStamp,ack.timestamp))
end as [Minutes Until Acknowledged]
,case when reset.timestamp is null then 'N/A'
else tostring(minutediff(ah.TimeStamp,reset.timestamp))
end as [Minutes Until Reset]

FROM Orion.AlertHistory ah
left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
left join Orion.Actions a on a.actionid=ah.actionid
left join Orion.Nodes p on p.nodeid=RelatedNodeID
left join (select timestamp, AlertActiveID, AlertObjectID,message,AccountID from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID
left join (select timestamp, AlertActiveID, AlertObjectID,AccountID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID

WHERE
daydiff(ah.timestamp,GETUTCDATE())<30
and ah.eventtype=0

--and (AccountID like '%cade%') --or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')

order by ah.timestamp desc

this outputs this  

im trying to make this compile the each user with the avg (minutes until acknowledged) to produce this 

here is the query that should work but its not. been stuck on this for hours. --report on alerts triggered
SELECT AccountID,
AVG(Minutes Until Acknowledged) AS MinsAVG,
MIN(Minutes Until Acknowledged) AS MinsMin,
MAX(Minutes Until Acknowledged) AS MinsMax
FROM (
select AccountID as [user],EntityCaption as [Trigger Object]
,case
WHEN RelatedNodeCaption=EntityCaption THEN 'Self'
When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
End as [Parent Node]
,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]
,case when ack.timestamp is null then 'N/A'
else tostring(minutediff(ah.TimeStamp,ack.timestamp))
end as [Minutes Until Acknowledged]
,case when reset.timestamp is null then 'N/A'
else tostring(minutediff(ah.TimeStamp,reset.timestamp))
end as [Minutes Until Reset]

FROM Orion.AlertHistory ah
left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
left join Orion.Actions a on a.actionid=ah.actionid
left join Orion.Nodes p on p.nodeid=RelatedNodeID
left join (select timestamp, AlertActiveID, AlertObjectID,message,AccountID from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID
left join (select timestamp, AlertActiveID, AlertObjectID,AccountID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID

WHERE
daydiff(ah.timestamp,GETUTCDATE())<30
and ah.eventtype=0) peruser

--and (AccountID like '%cade%') --or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')

order by ah.timestamp desc

error is  so silly

  • 0

    Hi  

    I had some spare minutes, so I was able to take a look. First, when you have spaces in column names, those need to be defined as:

    AVG(peruser.[Minutes Until Acknowledged]) AS MinsAVG,
    MIN(peruser.[Minutes Until Acknowledged]) AS MinsMin,
    MAX(peruser.[Minutes Until Acknowledged]) AS MinsMax,

    Also, you have tried to calculate AVG/MIN/MAX using integers and "N/A" string, which would not work. You need to either ignore this row or change it to 0.

    Since I do not have a lot of alerts, I expanded your query a bit, in order to see the results better. Here you go:

    SELECT

        peruser.[user],
        peruser.[Trigger Object],
        AVG(peruser.[Minutes Until Acknowledged]) AS MinsAVGAcknowledged,
        MIN(peruser.[Minutes Until Acknowledged]) AS MinsMinAcknowledged,
        MAX(peruser.[Minutes Until Acknowledged]) AS MinsMaxAcknowledged,

        AVG(peruser.[Minutes Until Reset]) AS MinsAVGReset,
        MIN(peruser.[Minutes Until Reset]) AS MinsMinReset,
        MAX(peruser.[Minutes Until Reset]) AS MinsMaxReset


    FROM (
        select
            AccountID as [user],EntityCaption as [Trigger Object]
            ,case
                WHEN RelatedNodeCaption=EntityCaption THEN 'Self'
                When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
            End as [Parent Node]
            ,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]
            ,case
                when ack.timestamp is null then 0
                else tostring(minutediff(ah.TimeStamp,ack.timestamp))
            end as [Minutes Until Acknowledged]
            ,case
                when reset.timestamp is null then 0
                else tostring(minutediff(ah.TimeStamp,reset.timestamp))
            end as [Minutes Until Reset]

        FROM Orion.AlertHistory ah
        
        left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
        left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
        left join Orion.Actions a on a.actionid=ah.actionid
        left join Orion.Nodes p on p.nodeid=RelatedNodeID
        left join (select timestamp, AlertActiveID, AlertObjectID,message,AccountID from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID
        left join (select timestamp, AlertActiveID, AlertObjectID,AccountID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID

        WHERE
        daydiff(ah.timestamp,GETUTCDATE())<30
        and ah.eventtype=0
    ) peruser

    GROUP BY peruser.[user], peruser.[Trigger Object]
    --and (AccountID like '%cade%') --or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')

    This is how it looks in my lab:

    Marcin.

  • 0

    Hi  

    I had some spare minutes, so I was able to take a look. First, when you have spaces in column names, those need to be defined as:

    AVG(peruser.[Minutes Until Acknowledged]) AS MinsAVG,
    MIN(peruser.[Minutes Until Acknowledged]) AS MinsMin,
    MAX(peruser.[Minutes Until Acknowledged]) AS MinsMax,

    Also when calculating AVG/MIN/MAX you have tried to combine integers with "N/A" string, which would not work. You need to either ignore those rows or change them to 0.

    Since I do not have a lot of alerts, I expanded your query a bit, in order to see the results better. Here you go:

    SELECT

        peruser.[user],
        peruser.[Trigger Object],
        AVG(peruser.[Minutes Until Acknowledged]) AS MinsAVGAcknowledged,
        MIN(peruser.[Minutes Until Acknowledged]) AS MinsMinAcknowledged,
        MAX(peruser.[Minutes Until Acknowledged]) AS MinsMaxAcknowledged,

        AVG(peruser.[Minutes Until Reset]) AS MinsAVGReset,
        MIN(peruser.[Minutes Until Reset]) AS MinsMinReset,
        MAX(peruser.[Minutes Until Reset]) AS MinsMaxReset


    FROM (
        select
            AccountID as [user],EntityCaption as [Trigger Object]
            ,case
                WHEN RelatedNodeCaption=EntityCaption THEN 'Self'
                When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
            End as [Parent Node]
            ,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]
            ,case
                when ack.timestamp is null then 0
                else tostring(minutediff(ah.TimeStamp,ack.timestamp))
            end as [Minutes Until Acknowledged]
            ,case
                when reset.timestamp is null then 0
                else tostring(minutediff(ah.TimeStamp,reset.timestamp))
            end as [Minutes Until Reset]

        FROM Orion.AlertHistory ah
        
        left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
        left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
        left join Orion.Actions a on a.actionid=ah.actionid
        left join Orion.Nodes p on p.nodeid=RelatedNodeID
        left join (select timestamp, AlertActiveID, AlertObjectID,message,AccountID from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID
        left join (select timestamp, AlertActiveID, AlertObjectID,AccountID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID

        WHERE
        daydiff(ah.timestamp,GETUTCDATE())<30
        and ah.eventtype=0
    ) peruser

    GROUP BY peruser.[user], peruser.[Trigger Object]
    --and (AccountID like '%cade%') --or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')

    This is how it looks in my lab:

    Marcin.

  • 0

    Hi  

    I had some spare minutes, so I was able to take a look. First, when you have spaces in column names, those need to be defined as:

    AVG(peruser.[Minutes Until Acknowledged]) AS MinsAVG,
    MIN(peruser.[Minutes Until Acknowledged]) AS MinsMin,
    MAX(peruser.[Minutes Until Acknowledged]) AS MinsMax,

    Also when calculating AVG/MIN/MAX you have tried to combine integers with "N/A" string, which would not work. You need to either ignore those rows or change them to 0.

    Since I do not have a lot of alerts, I expanded your query a bit, in order to see the results better. Here you go:

    SELECT

        peruser.[user],
        peruser.[Trigger Object],
        AVG(peruser.[Minutes Until Acknowledged]) AS MinsAVGAcknowledged,
        MIN(peruser.[Minutes Until Acknowledged]) AS MinsMinAcknowledged,
        MAX(peruser.[Minutes Until Acknowledged]) AS MinsMaxAcknowledged,

        AVG(peruser.[Minutes Until Reset]) AS MinsAVGReset,
        MIN(peruser.[Minutes Until Reset]) AS MinsMinReset,
        MAX(peruser.[Minutes Until Reset]) AS MinsMaxReset


    FROM (
        select
            AccountID as [user],EntityCaption as [Trigger Object]
            ,case
                WHEN RelatedNodeCaption=EntityCaption THEN 'Self'
                When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
            End as [Parent Node]
            ,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]
            ,case
                when ack.timestamp is null then 0
                else tostring(minutediff(ah.TimeStamp,ack.timestamp))
            end as [Minutes Until Acknowledged]
            ,case
                when reset.timestamp is null then 0
                else tostring(minutediff(ah.TimeStamp,reset.timestamp))
            end as [Minutes Until Reset]

        FROM Orion.AlertHistory ah
        
        left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
        left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
        left join Orion.Actions a on a.actionid=ah.actionid
        left join Orion.Nodes p on p.nodeid=RelatedNodeID
        left join (select timestamp, AlertActiveID, AlertObjectID,message,AccountID from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID
        left join (select timestamp, AlertActiveID, AlertObjectID,AccountID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID

        WHERE
        daydiff(ah.timestamp,GETUTCDATE())<30
        and ah.eventtype=0
    ) peruser

    GROUP BY peruser.[user], peruser.[Trigger Object]
    --and (AccountID like '%cade%') --or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')

    This is how it looks in my lab:

    Marcin.

  • 0

    Hi  

    I had some spare minutes, so I was able to take a look. First, when you have spaces in column names, those need to be defined as:

    AVG(peruser.[Minutes Until Acknowledged]) AS MinsAVG,
    MIN(peruser.[Minutes Until Acknowledged]) AS MinsMin,
    MAX(peruser.[Minutes Until Acknowledged]) AS MinsMax,

    Also when calculating AVG/MIN/MAX you have tried to combine integers with "N/A" string, which would not work. You need to either ignore those rows or change them to 0.

    Since I do not have a lot of alerts, I expanded your query a bit, in order to see the results better. Here you go:

    SELECT

        peruser.[user],
        peruser.[Trigger Object],
        AVG(peruser.[Minutes Until Acknowledged]) AS MinsAVGAcknowledged,
        MIN(peruser.[Minutes Until Acknowledged]) AS MinsMinAcknowledged,
        MAX(peruser.[Minutes Until Acknowledged]) AS MinsMaxAcknowledged,

        AVG(peruser.[Minutes Until Reset]) AS MinsAVGReset,
        MIN(peruser.[Minutes Until Reset]) AS MinsMinReset,
        MAX(peruser.[Minutes Until Reset]) AS MinsMaxReset


    FROM (
        select
            AccountID as [user],EntityCaption as [Trigger Object]
            ,case
                WHEN RelatedNodeCaption=EntityCaption THEN 'Self'
                When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
            End as [Parent Node]
            ,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]
            ,case
                when ack.timestamp is null then 0
                else tostring(minutediff(ah.TimeStamp,ack.timestamp))
            end as [Minutes Until Acknowledged]
            ,case
                when reset.timestamp is null then 0
                else tostring(minutediff(ah.TimeStamp,reset.timestamp))
            end as [Minutes Until Reset]

        FROM Orion.AlertHistory ah
        
        left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
        left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
        left join Orion.Actions a on a.actionid=ah.actionid
        left join Orion.Nodes p on p.nodeid=RelatedNodeID
        left join (select timestamp, AlertActiveID, AlertObjectID,message,AccountID from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID
        left join (select timestamp, AlertActiveID, AlertObjectID,AccountID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID

        WHERE
        daydiff(ah.timestamp,GETUTCDATE())<30
        and ah.eventtype=0
    ) peruser

    GROUP BY peruser.[user], peruser.[Trigger Object]
    --and (AccountID like '%cade%') --or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')

    This is how it looks in my lab:

    Marcin.

  • 0

    Hi  

    I had some spare minutes, so I was able to take a look. First, when you have spaces in column names, those need to be defined as:

    AVG(peruser.[Minutes Until Acknowledged]) AS MinsAVG,
    MIN(peruser.[Minutes Until Acknowledged]) AS MinsMin,
    MAX(peruser.[Minutes Until Acknowledged]) AS MinsMax,

    Also when calculating AVG/MIN/MAX you have tried to combine integers with "N/A" string, which would not work. You need to either ignore those rows or change them to 0.

    Since I do not have a lot of alerts, I expanded your query a bit, in order to see the results better. Here you go:

    SELECT

        peruser.[user],
        peruser.[Trigger Object],
        AVG(peruser.[Minutes Until Acknowledged]) AS MinsAVGAcknowledged,
        MIN(peruser.[Minutes Until Acknowledged]) AS MinsMinAcknowledged,
        MAX(peruser.[Minutes Until Acknowledged]) AS MinsMaxAcknowledged,

        AVG(peruser.[Minutes Until Reset]) AS MinsAVGReset,
        MIN(peruser.[Minutes Until Reset]) AS MinsMinReset,
        MAX(peruser.[Minutes Until Reset]) AS MinsMaxReset


    FROM (
        select
            AccountID as [user],EntityCaption as [Trigger Object]
            ,case
                WHEN RelatedNodeCaption=EntityCaption THEN 'Self'
                When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
            End as [Parent Node]
            ,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]
            ,case
                when ack.timestamp is null then 0
                else tostring(minutediff(ah.TimeStamp,ack.timestamp))
            end as [Minutes Until Acknowledged]
            ,case
                when reset.timestamp is null then 0
                else tostring(minutediff(ah.TimeStamp,reset.timestamp))
            end as [Minutes Until Reset]

        FROM Orion.AlertHistory ah
        
        left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
        left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
        left join Orion.Actions a on a.actionid=ah.actionid
        left join Orion.Nodes p on p.nodeid=RelatedNodeID
        left join (select timestamp, AlertActiveID, AlertObjectID,message,AccountID from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID
        left join (select timestamp, AlertActiveID, AlertObjectID,AccountID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID

        WHERE
        daydiff(ah.timestamp,GETUTCDATE())<30
        and ah.eventtype=0
    ) peruser

    GROUP BY peruser.[user], peruser.[Trigger Object]
    --and (AccountID like '%cade%') --or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')

    This is how it looks in my lab:

    Marcin.

  • 0

    so i got the query to work thank you, but i was looking to get it to for example user1 combine all of their acknowledged alerts and display avg,min,max,total alerts acknowledged(count) on per user base table(all user on e table).   like this 

  • 0 in reply to nmoura38

    Hi  

    Share your answer with us, so others can be benefited.

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 200,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification SolarWinds Lab Link Customer Portal
About THWACK SolarWinds Blog Federal & Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2021 SolarWinds Worldwide, LLC. All Rights Reserved.