Need a SQL query to pull alert information with alert generation date, last modifiedby, closing date, for specific server and alert


  • I need to pull specific alert for a specific server from DW with the specific date and time slot.

    Can anyone help me with it to get it done with SQL query??

    Sunday, February 10, 2019 6:14 AM

All replies

  • Please use this SQL statement

    select a.AlertName,c.DisplayName source , a.ResolutionState,a.RaisedDateTime,
       when a.StateSetDateTime>=b.DBLastModifiedDateTime then a.StateSetDateTime
       else b.DBLastModifiedDateTime
    end lastModifieddateTime,
     when a.ResolutionState=255 then a.StateSetDateTime
     else null
    end closeddate
    (select a.AlertGuid, a.AlertName,a.ManagedEntityRowId, a.RaisedDateTime,max(b.ResolutionState) ResolutionState, max(b.StateSetDateTime) StateSetDateTime from Alert.vAlert a
    inner join alert.vAlertResolutionState b on a.AlertGuid=b.AlertGuid
    group by a.AlertGuid, a.AlertName,a.ManagedEntityRowId,a.RaisedDateTime) a
    inner join alert.vAlertDetail b on a.AlertGuid=b.AlertGuid
    inner join vManagedEntity c on c.ManagedEntityRowId=a.ManagedEntityRowId
    where c.DisplayName='XXX' and a.AlertName='XXXX'

    Pay attention to c.DisplayName='XXX' and a.AlertName='XXXX' where XXX refer to your server name and alert name respectively.


    • Proposed as answer by GouravIN Tuesday, February 12, 2019 5:06 PM
    Monday, February 11, 2019 7:06 AM
  • Use this on DW server

    select Distinct B.DisplayName, B.Path, B.Name, A.AlertName, A.AlertDescription, A.Severity, A.RaisedDateTime, C.TicketId, A.RepeatCount

    from Alert.vAlert As A

    INNER JOIN ManagedEntity As B
    ON A.ManagedEntityRowId = B.ManagedEntityRowId

    JOIN Alert.vAlertDetail As C
    ON A.AlertGuid = C.AlertGuid

    where (A.RaisedDateTime BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD')

    Order by A.RaisedDateTime

    • Proposed as answer by GouravIN Tuesday, February 12, 2019 5:06 PM
    Monday, February 11, 2019 1:54 PM
  • thanks, <g class="gr_ gr_25 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="25" id="25">i</g> was able to raise time.

    can you also help with pulling closing time in the same script??

    13 hours 39 minutes ago
  • My previous script include closing time as column name as closeddate


    13 hours 21 minutes ago