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

    Question

  • 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,
    case
       when a.StateSetDateTime>=b.DBLastModifiedDateTime then a.StateSetDateTime
       else b.DBLastModifiedDateTime
    end lastModifieddateTime,
    case
     when a.ResolutionState=255 then a.StateSetDateTime
     else null
    end closeddate
     from
    (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.

    Roger

    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??

    Friday, February 15, 2019 9:42 AM
  • My previous script include closing time as column name as closeddate

    Roger

    Friday, February 15, 2019 10:00 AM
  • Hi,

    did you able to figure it out? Roger's suggestion was close indeed. Appreciate the feedback and thanks in advance!

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Tuesday, April 2, 2019 12:33 PM
    Moderator