PI Services

Le blog des collaborateurs de PI Services

SCOM – Requête SQL des agents gérés par une gateway spécifique

27. septembre 2017 Christophe-JOURDAN Supervision (SCOM, NAGIOS), Script, SQL (0)

La requête SQL ci-dessous affiche les agents gérés par une Gateway spécifique ainsi que le failover configuré ou non.

Use OperationsManager
 
DECLARE @PrimaryServer VARCHAR(50)
SET @PrimaryServer = 'MyGateway'
 
;
 
WITH 
PrimaryRelation (SourceEntityId,agent,PrimaryServer,TargetEntityId)
AS
(
SELECT R.SourceEntityID,SourceBME.DisplayName as Agent,TargetBME.DisplayName as PrimaryServer, R.TargetEntityID
FROM Relationship R WITH (NOLOCK) 
JOIN BaseManagedEntity SourceBME 
ON R.SourceEntityID = SourceBME.BaseManagedEntityID 
JOIN BaseManagedEntity TargetBME 
ON R.TargetEntityID = TargetBME.BaseManagedEntityID 
WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication() 
AND TargetBME.IsDeleted <> '1'  -- AND THE PRIMARY SERVER IS NOT WAITING TO BE DELETED
AND SourceBME.IsDeleted <> '1' -- AND THE AGENT IS NOT WAITING TO BE DELETED
)
,
FailoverRelation (SourceEntityId,agent,FailoverServer,TargetEntityId)
AS
(
SELECT R.SourceEntityID,SourceBME.DisplayName as Agent,TargetBME.DisplayName as FailoverServer, R.TargetEntityID
FROM Relationship R WITH (NOLOCK) 
JOIN BaseManagedEntity SourceBME 
ON R.SourceEntityID = SourceBME.BaseManagedEntityID 
JOIN BaseManagedEntity TargetBME 
ON R.TargetEntityID = TargetBME.BaseManagedEntityID 
WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceSecondaryCommunication()
AND TargetBME.IsDeleted <> '1'  -- AND THE FAILOVER SERVER IS NOT WAITING TO BE DELETED
AND SourceBME.IsDeleted <> '1' -- AND THE AGENT IS NOT WAITING TO BE DELETED 
)
 
 
SELECT 
       MTV_HS.[DisplayName] as Agent
       
      ,PrimaryRelation.PrimaryServer
      ,FailoverServer =  -- FIELD RELATED TO 'LEFT OUTER JOIN' ON FailoverRelation TABLE
        CASE
        WHEN FailoverRelation.FailoverServer IS NULL THEN 'NO FAILOVER'
        ELSE FailoverRelation.FailoverServer
        END
      ,[Port]
      ,[InstallTime]
      ,[MaximumQueueSize]
      ,Patch = CASE 
        WHEN [PatchList] like '%UR4%' THEN 'RU4'
        WHEN [PatchList] like '%UR8%' THEN 'RU8'
        WHEN [PatchList] like '%UR11%' THEN 'RU11'
        WHEN [PatchList] = '' THEN '[NO_DATA]'
        END
      ,[IsManuallyInstalled]
      ,[Version]
      ,[ActionAccountIdentity]
      ,[ProxyingEnabled]
      ,[HeartbeatInterval]
      ,[NumberOfMissingHeartBeatsToMarkMachineDown_27AD2E30_EFE0_1A73_8C9D_F0A22B073227] as NumberOfMissingHeartBeatsToMarkMachineDown
      ,MTV_OS.DisplayName as OS
      ,MTV_OS.LogicalProcessors_5CAE4847_F75B_01D0_156E_1658D557B739 as Logic_CPU
      ,MTV_OS.CSDVersion_AFE62B62_74FC_2F06_D8A0_DEE31F14CD33 as ServicePack
      
      
  FROM [OperationsManager].[dbo].[MTV_HealthService] as MTV_HS
  INNER JOIN  [dbo].[MTV_Microsoft$Windows$OperatingSystem] as MTV_OS on MTV_HS.DisplayName = MTV_OS.PrincipalName
 
  INNER JOIN PrimaryRelation on PrimaryRelation.SourceEntityId = MTV_HS.BaseManagedEntityId
  -- THE LEFT OUTER JOIN CAN BE COMMENTED (AND RELATED FIELDS) TO DISPLAY ONLY THE PRIMARY SERVER)
  LEFT OUTER JOIN FailoverRelation on FailoverRelation.SourceEntityId = MTV_HS.BaseManagedEntityId
 
  WHERE IsAgent = '1'
  AND PrimaryServer like '%'+@PrimaryServer+'%'
 
 
  order by MTV_HS.[DisplayName]

SCOM – Requête SQL de toutes les instances d’objet pour une machine

27. septembre 2017 Christophe-JOURDAN Supervision (SCOM, NAGIOS) (0)

La requête ci-dessous liste toute les instances d’objets qui se rapporte a une machine donnée, avec leur état ainsi que les infos sur un éventuel mode maintenance en cours sur l’objet.

Use OperationsManager
 
DECLARE @TargetComputer VARCHAR(50)
set @TargetComputer = 'MyServer'
 
SELECT 
MTV.DisplayName as ClassName
,MEGV.Path as Instance_Path
,MEGV.[DisplayName] as 'Entity_DisplayName'
,MEGV.[Name] as 'Entity_Name'
,MEGV.[FullName] as Entity_FullName
,[IsManaged]
,[IsDeleted]
,HealthState =                                                     
    CASE WHEN InMaintenanceMode = '0'
      THEN 
        CASE [HealthState]
        WHEN '0' THEN 'Not Monitored'
        WHEN '1' THEN 'OK'
        WHEN '2' THEN 'Warning'
        WHEN '3' THEN 'Critical'
        END
        WHEN InMaintenanceMode = '1'
        THEN 
        CASE [HealthState]
        WHEN '0' THEN 'In Maintenance Mode'
        WHEN '1' THEN 'OK'
        WHEN '2' THEN 'Warning'
        WHEN '3' THEN 'Critical'
        END
    END
 
,Is_Available = 
    CASE [IsAvailable]
    WHEN '1' THEN 'YES'
    WHEN '2' THEN 'NO'
        END
                                                  
,In_MaintenanceMode = 
CASE [InMaintenanceMode]
WHEN '0' THEN 'NO'
WHEN '1' THEN 'YES'
END
,Start_Of_Maintenance = 
    CASE WHEN InMaintenanceMode = '0' 
        THEN null
        ELSE MMV.StartTime
        END
       ,End_Of_Maintenance = 
        CASE WHEN InMaintenanceMode = '0'
                THEN null
        ELSE MMV.ScheduledEndTime
        END
        ,Maintenance_RootCause = 
            CASE WHEN InMaintenanceMode = '0'
              THEN null
                ELSE 
                CASE MMV.ReasonCode
                WHEN '0' THEN 'Other (Planned)'
                WHEN '1' THEN 'Other (Unplanned)'
                WHEN '2' THEN 'Hardware: Maintenance (Planned)'
                WHEN '3' THEN 'Hardware: Maintenance (Unplanned)'
                WHEN '4' THEN 'Hardware: Installation (Planned)'
                WHEN '5' THEN 'Hardware: Installation (Unplanned)'
                WHEN '6' THEN 'Operating System: Reconfiguration (Planned)'
                WHEN '7' THEN 'Operating System: Reconfiguration (Unplanned)'
                WHEN '8' THEN 'Application: Maintenance (Planned)'
                WHEN '9' THEN 'Application: Maintenance (Unplanned)'
                WHEN '10' THEN 'Application: Installation (Planned)'
                WHEN '11' THEN 'Application: Unresponsive'
                WHEN '12' THEN 'Application:  Unstable'
                WHEN '13' THEN 'Security Issue'
                WHEN '14' THEN 'Loss of network connectivity (Unplanned)'
                END
            END
                                               
,Maintenance_Reason =   
    CASE WHEN InMaintenanceMode = '0' 
    THEN null
    ELSE MMV.Comments
    END
      
FROM [OperationsManager].[dbo].[ManagedEntityGenericView] MEGV
INNER JOIN [dbo].[ManagedTypeView] MTV on MEGV.MonitoringClassId = MTV.Id
INNER JOIN [OperationsManager].[dbo].[MaintenanceModeView] MMV on MEGV.id = MMV.BaseManagedEntityId
WHERE (MEGV.Name  like '%'+@TargetComputer+'%' OR MEGV.DisplayName  like '%'+@TargetComputer+'%' OR MEGV.Path  like '%'+@TargetComputer+'%')
and MTV.LanguageCode = 'ENU'
and MEGV.HealthState is not null
and MEGV.IsDeleted <> '1'
ORDER BY MTV.DisplayName
 

SCOM – Requête des heartbeat failure pour les membres d’un groupe spécifique

27. septembre 2017 Christophe-JOURDAN Supervision (SCOM, NAGIOS) (0)

La requête ci-dessous, a exécuter sur la base de reporting, liste les alertes “Health Service Heartbeat Failure” pour les membres d’un groupe donné (@computergroup1) sur une plage de temps donnée.

-- QUERY TO GET 'Health Service Heartbeat Failure' ALERTS FOR COMPUTERS THAT ARE MEMBERS OF A SPECIFIC GROUP (@computergroup1)
-- MODIFY @computergroup1 AND @startdate/@enddate
-- BEWARE THAT @computergroup1 NAME CHARACTERS NUMBER CAN BE HOLD BY VARCHAR(N) VARIABLE
 
Use OperationsManagerDW
 
DECLARE @startdate    datetime
DECLARE @enddate    datetime
DECLARE @computergroup1    VARCHAR(100)
 
SET @startdate = '2017-09-01 00:00:00'
SET @enddate = '2017-09-30 00:00:00'
SET @computergroup1 = 'MyGroup'
 
 
SELECT 
av.AlertGuid
 
,apv.ParameterValue as SystemName
,av.AlertName
,adv.TicketId
,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.RaisedDateTime)) as DownDateTime 
,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),arsv.StateSetDateTime)) as RestoredDateTime
,adv.CustomField2 as OutageType 
,adv.CustomField3 as RootCause 
,Alert_ResolutionState = CASE
    WHEN arsv.ResolutionState = '255' THEN 'Closed'
    END
,adv.DBLastModifiedByUserId as UserID
FROM Alert.vAlert av
JOIN Alert.vAlertDetail adv on av.AlertGuid = adv.AlertGuid
JOIN Alert.vAlertResolutionState arsv on av.AlertGuid = arsv.AlertGuid
JOIN Alert.vAlertParameter apv on av.AlertGuid = apv.AlertGuid
 
 
-- JOIN ON TEMPORARY TABLE THAT RETRIEVE MAX OF StateSetDateTime (restoretime)
JOIN    (
        select av.AlertGuid 
        ,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.RaisedDateTime)) as DownDateTime 
        ,MAX(arsv.StateSetDateTime) as restoretime
        from Alert.vAlert av
        JOIN Alert.vAlertDetail adv on av.AlertGuid = adv.AlertGuid
        JOIN Alert.vAlertResolutionState arsv on av.AlertGuid = arsv.AlertGuid
        JOIN Alert.vAlertParameter apv on av.AlertGuid = apv.AlertGuid
        GROUP BY av.AlertGuid,av.RaisedDateTime
        ) temp on temp.AlertGuid = av.AlertGuid
 
 
 
WHERE AlertName = 'Health Service Heartbeat Failure'
AND arsv.ResolutionState = '255'
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.RaisedDateTime)) between @startdate and @enddate
 
 
 
and apv.ParameterValue IN (
SELECT vManagedEntity.DisplayName
FROM  vManagedEntity 
INNER JOIN vRelationship ON vManagedEntity.ManagedEntityRowId = vRelationship.TargetManagedEntityRowId 
INNER JOIN vManagedEntity AS ManagedEntity_1 ON vRelationship.SourceManagedEntityRowId = ManagedEntity_1.ManagedEntityRowId
    WHERE (ManagedEntity_1.DisplayName = @computergroup1)
)
 
 
AND arsv.StateSetDateTime = temp.restoretime
AND adv.TicketId is not null
 
GROUP BY apv.ParameterValue,adv.TicketId,av.AlertGuid,av.AlertName,av.RaisedDateTime,arsv.StateSetDateTime,adv.CustomField2,adv.CustomField3,/*adv.CustomField4,*/arsv.ResolutionState,adv.DBLastModifiedByUserId
ORDER BY apv.ParameterValue
 

 

SCOM – SQL – Trois requêtes d’inventaires des overrides par type d’objet.

29. juin 2017 Christophe-JOURDAN Supervision (SCOM, NAGIOS), SQL, Script (0)

Un peu a la manière du précédent post sur l’inventaire des overrides, les trois requêtes suivantes permettent de lister les overrides selon les types d’objet “Rule”, “Monitor” et “Discovery”.

 

 

/* All Overrides for Rules of language code EUN and FRA, with Original MP and Rule, Parameter Name, Override Value, Scope and Containing MP */

Use OperationsManager
SELECT
mpv2.DisplayName as Rule_MP_Name
,rv.DisplayName as Rule_Name
,IsEnabledByDefault = CASE
    WHEN rv.Enabled = '0' THEN 'NO'
    WHEN rv.Enabled <> '0' THEN 'YES'
    END
,op.OverrideableParameterName as Overrideable_Parameter
,mo.Value as Override_Value 
,IsEnforced = CASE 
    WHEN mo.Enforced = '0' THEN 'NO'
    WHEN mo.Enforced = '1' THEN 'YES'
    END
,mt.TypeName as Override_Scope
,bme.DisplayName as Override_InstanceName
,bme.Path as Override_InstancePath 
,mpv.DisplayName as Override_MP_Name
FROM ModuleOverride mo 
INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId 
INNER JOIN ruleview rv on rv.Id = mo.ParentId 
INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext
INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId
INNER JOIN managementpackview mpv2 on mpv2.Id = rv.ManagementPackId
LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext 
WHERE mpv.Sealed = 0
AND rv.LanguageCode in ('ENU','FRA')
AND mpv.LanguageCode in ('ENU','FRA')
AND mpv2.LanguageCode in ('ENU','FRA')

--ORDER BY mpv2.DisplayName

 

/*All Overrides for Monitor of language code EUN and FRA, with Original MP and Monitor, Parameter Name, Override Value, Scope and Containing MP */

Use OperationsManager
SELECT
mpv2.DisplayName as Monitor_MP_Name
,mv.DisplayName as Monitor_Name
,IsEnabledByDefault = CASE
    WHEN mv.Enabled = '0' THEN 'NO'
    WHEN mv.Enabled <> '0' THEN 'YES'
    END
,op.OverrideableParameterName as Overrideable_Parameter
,mo.Value as Override_Value 
,IsEnforced = CASE 
    WHEN mo.Enforced = '0' THEN 'NO'
    WHEN mo.Enforced = '1' THEN 'YES'
    END
,mt.TypeName as Override_Scope
,bme.DisplayName as Override_InstanceName
,bme.Path as Override_InstancePath 
,mpv.DisplayName as Override_MP_Name
FROM MonitorOverride  mo 
INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId 
INNER JOIN monitorview mv on mv.Id = mo.MonitorId
INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext
INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId
INNER JOIN managementpackview mpv2 on mpv2.Id = mv.ManagementPackId
LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext 
WHERE mpv.Sealed = 0
AND mv.LanguageCode in ('ENU','FRA')
AND mpv.LanguageCode in ('ENU','FRA')
AND mpv2.LanguageCode in ('ENU','FRA')
ORDER BY mpv2.DisplayName

 

-- All Overrides for Discoveries of language code EUN and FRA, with Original MP and Discovery, Parameter Name, Override Value, Scope and Containing MP */

Use OperationsManager
SELECT
mpv2.DisplayName as Discovery_MP_Name
,dv.DisplayName as Discovery_Name
,mo.ParentType
,IsEnabledByDefault = CASE
    WHEN dv.Enabled = '0' THEN 'NO'
    WHEN dv.Enabled <> '0' THEN 'YES'
    END
,op.OverrideableParameterName as Overrideable_Parameter
,mo.Value as Override_Value 
,IsEnforced = CASE 
    WHEN mo.Enforced = '0' THEN 'NO'
    WHEN mo.Enforced = '1' THEN 'YES'
    END
,mt.TypeName as Override_Scope
,bme.DisplayName as Override_InstanceName
,bme.Path as Override_InstancePath 
,mpv.DisplayName as Override_MP_Name
FROM ModuleOverride mo 
INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId 
INNER JOIN DiscoveryView dv on dv.Id = mo.ParentId 
INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext
INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId
INNER JOIN managementpackview mpv2 on mpv2.Id = dv.ManagementPackId
LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext 
WHERE mpv.Sealed = 0
AND dv.LanguageCode in ('ENU','FRA')
AND mpv.LanguageCode in ('ENU','FRA')
AND mpv2.LanguageCode in ('ENU','FRA')
AND mo.ParentType = 'Discovery'
ORDER BY mpv2.DisplayName

SCOM - Requête SQL pour afficher toutes les règles ayant au moins un override ou aucun

29. juin 2017 Christophe-JOURDAN Supervision (SCOM, NAGIOS), Script, SQL (0)

 

 

-- QUERY TO DISPLAY ALL RULES WITH TARGET CLASSES AND THEIR OVERRIDE STATE (ANYWAY MORE THAN 1 OVERRIDE PER RULE) - (FILTERED ON 'ENU' AND 'FRA' LANGUAGES)
-- THE FINAL 'GROUP BY' CLAUSE IS TO DISPLAY ONLY ONE RULE OCCURENCE (WE ONLY WANT TO KNOW THAT RULE IS OVERRIDEN AT LEAST ONE TIME.
-- THAT IS EQUIVALENT TO DELETE THE 'ALL' STATEMENT IN UNION CLAUSE.

PRINT 'ALL RULES THAT IS OVERRIDEN AT LEAST 1 TIME OR NOT';

WITH TEMP1 (Rule_Name,Target_Class,Override_State)
AS
(
SELECT rv.DisplayName as Rule_Name
,MTV.DisplayName as Target_Class
,Override_State =  CASE
    WHEN mo.ParentId is null THEN 'NOT OVERRIDEN'
    WHEN mo.ParentId is not null THEN 'OVERRIDEN'
    END
FROM RuleView rv
FULL JOIN ModuleOverride mo on rv.Id = mo.ParentId
INNER JOIN [dbo].[ManagedTypeView] MTV on rv.TargetMonitoringClassId = MTV.Id
WHERE rv.Id  not in (select mo.ParentId from ModuleOverride mo)
AND rv.LanguageCode in ('ENU','FRA')
AND MTV.LanguageCode in ('ENU','FRA')

UNION ALL

SELECT rv.DisplayName as Rule_name
,MTV.DisplayName as Target_Class
,Override_State =  CASE
    WHEN mo.ParentId is null THEN 'NOT OVERRIDEN'
    WHEN mo.ParentId is not null THEN 'OVERRIDEN'
    END
FROM RuleView rv
FULL JOIN ModuleOverride mo on rv.Id = mo.ParentId
JOIN [dbo].[ManagedTypeView] MTV on rv.TargetMonitoringClassId = MTV.Id
WHERE rv.Id  in (select mo.ParentId from ModuleOverride mo)
AND rv.LanguageCode in ('ENU','FRA')
AND MTV.LanguageCode in ('ENU','FRA')
)

SELECT Rule_Name
,Target_Class
,Override_State
 FROM TEMP1
 GROUP BY Rule_Name,Target_Class,Override_State
 ORDER BY Rule_Name

SCOM – SQL – Requête d’inventaire des overrides

29. mars 2017 Christophe-JOURDAN Supervision (SCOM, NAGIOS), Script, SQL (0)

La requête ci-dessous permet de lister tout les overrides de manière ‘clarifiés’ afin d’identifier les objets a la source de ces overrides, les paramètres concernés et les management pack sources et de destination.

Les paramètres @Startdate et @EndDate permettent de spécifier une fenêtre de temps concernant les dates de création et de dernière modification des overrides.

 

 

declare @StartDate  datetime
declare @EndDate    datetime
set @EndDate = GETDATE()
set @StartDate = DATEADD(day,-60,@EndDate)
 
SELECT DISTINCT MP.MPFriendlyName AS 'Management Pack'
, aov.ParentType AS 'Type'
,(CASE 
       WHEN AOV.OverrideType = 'RuleProperty' THEN R.RuleName 
       WHEN AOV.OverrideType = 'MonitorProperty' THEN M.MonitorName 
       WHEN AOV.OverrideType = 'RuleConfiguration' THEN R.RuleName 
       WHEN AOV.OverrideType = 'MonitorConfiguration' THEN M.MonitorName 
 END) AS 'NameType',
mt.DisplayName AS ContextDisplayName
, aov.OverrideableParameterName
, aov.Value,
(CASE Enforced 
       WHEN '0' THEN 'False' 
       WHEN '1' THEN 'True' 
  END) AS 'Enforced'
  , aov.LastModified
  , aov.TimeAdded
  ,(CASE 
  WHEN AOV.OverrideType = 'RuleProperty' THEN 
             (CASE R.RuleEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True' 
             WHEN '4' THEN 'True' 
             END)
 
  WHEN AOV.OverrideType = 'MonitorProperty' THEN 
             (CASE M.MonitorEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True' 
             WHEN '4' THEN 'True' 
             END)
 
  WHEN AOV.OverrideType = 'RuleConfiguration' THEN 
             (CASE R.RuleEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True' 
             WHEN '4' THEN 'True' 
             END)
             
  WHEN AOV.OverrideType = 'MonitorConfiguration' THEN 
             (CASE M.MonitorEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True'
             WHEN '4' THEN 'True' 
             END) 
END) AS 'Enable_by_default', MPSTORE.MPFriendlyName AS 'MP Stored'
 
FROM AllOverrideView AS aov 
LEFT OUTER JOIN Rules AS R WITH (nolock) ON aov.TargetId = R.RuleId AND (aov.OverrideType = 'RuleProperty' OR aov.OverrideType = 'RuleConfiguration') 
LEFT OUTER JOIN Monitor AS M WITH (nolock) ON aov.TargetId = M.MonitorId AND (aov.OverrideType = 'MonitorProperty' OR aov.OverrideType = 'MonitorConfiguration') 
LEFT OUTER JOIN  ManagementPack AS MP WITH (nolock) ON (CASE 
                                                                                              WHEN AOV.OverrideType = 'RuleProperty' THEN R.ManagementPackId 
                                                                                              WHEN AOV.OverrideType = 'RuleConfiguration' THEN R.ManagementPackId
                                                                                              WHEN AOV.OverrideType = 'MonitorProperty' THEN M.ManagementPackId 
                                                                                              WHEN AOV.OverrideType = 'MonitorConfiguration' THEN M.ManagementPackId
                                                                                              END) = MP.ManagementPackId 
INNER JOIN ManagementPack AS MPSTORE WITH (nolock) ON MPSTORE.ManagementPackId = aov.ManagementPackId 
LEFT OUTER JOIN ManagedTypeView AS mt WITH (nolock) ON mt.Id = aov.ContextId 
LEFT OUTER JOIN ManagedTypeView AS mtv WITH (nolock) ON mtv.Id = aov.ContextObjectId
 
WHERE (MP.MPFriendlyName IS NOT NULL) 
AND (aov.LastModified BETWEEN @StartDate AND @EndDate) 
OR (MP.MPFriendlyName IS NOT NULL) 
AND (aov.TimeAdded BETWEEN @StartDate AND @EndDate)
 
ORDER BY aov.TimeAdded DESC