PI Services

Le blog des collaborateurs de PI Services

SCOM – MP Powershell par SquaredUp

Tous les admins SCOM vous le diront : créer des nouvelles règles dans la console classique peut s’avérer terriblement frustrant, en particulier par son manque de flexibilité et de liberté.

Un exemple particulièrement récurrent est l’impossibilité d’utiliser autre chose que le Visual Basic pour créer des règles ou moniteurs scriptés, alors qu’il est parfaitement possible d’utiliser Powershell en passant par des outils tiers (MP Author de Silect, voire VisualStudio).

Une première tentative de simplifier les choses avait été réalisée par Wei Lim, avec un management pack qui apportait un wizard permettant la création de moniteurs en powershell directement dans la console SCOM : https://blogs.msdn.microsoft.com/wei_out_there_with_system_center/2015/07/09/opsmgr-new-sample-wizard-to-create-powershell-monitors-in-the-ops-console/

Cela restait cependant assez limité, il n’était par exemple pas possible de passer de paramètres aux scripts au début (ce qui fut corrigé dans une release ultérieure) et seule la création de moniteurs à 2 états était possible.

Depuis peu, SquaredUp (société bien connue pour son excellente console web éponyme, nous en reparlerons surement un jour) propose également un management pack gratuit, qui apporte enfin à la console SCOM les fonctionnalités que nous étions en droit d’attendre : création de moniteurs à 2 et 3 états, de tous types de règles, de tâches… en powershell, à l’aide de wizards fort bien concus !

clip_image002

clip_image004

Le Management Pack est disponible ici : https://squaredup.com/free-powershell-management-pack/

SOPHOS - Mise en place de Sophos Central Cloud

Description

Sophos Central vous permet de gérer les politiques de sécurité et d’administrer plusieurs produits depuis une seule interface Web. Aucune installation ou déploiement de serveurs de gestion ne sont requis. Vos serveurs et appareils se connecteront directement à Sophos Central pour recevoir les nouveaux paramètres, envoyer des alertes et partager l’intelligence contextuelle de sécurité.

Installation

Pour pourvoir manager vos appareils il faut dans un premier temps ajouter les comptes utilisateurs dans la console Sophos Central. Pour ce faire il existe deux méthodes soit :

  • Ajouter les comptes à la manuellement, en remplissant les informations suivantes :

image

  • Synchroniser les comptes Active Directory de votre entreprise avec l’outil « AD Sync Settings/Status ».

Nous allons nous attarder sur la deuxième méthode.

Tout d’abord il faut se rendre dans l’onglet « Global Settings » et télécharger l’outil.

clip_image002

Lancer l’exécutable sur un Contrôleur de Domaine puis un assistant va apparaître pour configurer la synchronisation.

clip_image004

Mettre votre compte Sophos Central.

image

Cocher l’option “SSL” puis remplir les informations suivantes.

image

Note : utiliser un compte de service pour effectuer la synchronisation.

Choisir le ou les domaines puis les “OU” à synchroniser.

image

image

Il ne reste plus qu’a définir quand s’effectuera la synchronisation.

clip_image013

Une fois fini cliquer sur « Finished » et l’utilitaire ce synchronisera à l’heure que vous avez indiquez. Pour lancer une première synchronisation il faut cliquer sur « preview and sync ».

clip_image014

Les comptes sont maintenant ajoutés dans Sophos Central.

clip_image016

La dernière étape est d’installer les agents sur les appareils clients. Une des méthodes est de leurs envoyer un mail d’instruction expliquant les différentes étapes d’installation depuis la console Sophos.

image


Configuration

Dans l’onglet “Endpoint Protection” puis “Policies” vous pouvez configurer les règles de sécurité des utilisateurs.

Il ne reste plus qu’a créer vos règles en fonction de vos besoins.

image

clip_image021


Information :

Pour plus d’information concernant la configuration, je vous invite à consulter le lien suivant : http://docs.sophos.com/sophos-cloud/help/fr-fr/PDF/scloud_hfra.pdf

Agent SCOM 2012 R2 UR12 (et ultérieur) sur Windows 2003

Lors de la finalisation d’une migration side by side SCOM 2007 vers SCOM 2012 R2, j’ai rencontré un problème assez inattendu : il ne restait alors plus que quelques agents Windows 2003 à déployer et quelques autres à mettre à jour avec le dernier UR, et je n’anticipais pas de problème particulier dans cette phase déjà réalisée à maintes reprises sur d’autres serveurs de cet environnement ainsi que sur d’autres environnements.

J’ai donc eu la mauvaise surprise de constater que ces agents s’arrêtaient dès leur démarrage, parfois sans aucun message d’erreur (arrêt « propre » matérialisé par les événements 103 puis 101), parfois avec un message d’erreur assez peu parlant (événement 1000 après l’événement 103) :

clip_image001

clip_image002

Faulting application healthservice.exe, version 7.1.10292.0, stamp 585161d0, faulting module unknown, version 0.0.0.0, stamp 00000000, debug ? 0, fault address 0x000c9ba0.

J’ai alors décidé de prendre une trace de l’agent, afin d’obtenir un diagnostic plus poussé (cf. https://blog.piservices.fr/post/2017/09/30/SCOM-Prendre-une-trace-de-lagent1).

Fort heureusement le problème était très simple à reproduire (un simple démarrage de l’agent…), et m’a permis d’obtenir l’erreur suivante :

clip_image004

Unable to create self-signed certificate : -2146893816(NTE_BAD_ALGID).

L’agent échoue donc à créer un certificat auto-signé lors de son démarrage, et plante dans la foulée.

Mais pourquoi chercherait-il à générer un certificat ? Comme le précise Stefan Roth dans cet article très détaillé (https://stefanroth.net/2016/03/02/scom-how-data-is-encrypted/), ce certificat est utilisé lorsque le Management Server transmet un RunAs Account à un agent afin d’apporter un niveau de chiffrement supplémentaire.

Ce certificat est donc créé lors du premier démarrage de l’agent, ainsi que lorsqu’il expire (sa durée de vie est d’un an) :

clip_image006

Nous constatons sur la capture ci-dessus que le certificat est bel et bien présent dans le magasin.

Pourquoi l’agent cherche-t-il a le régénérer, et pourquoi échoue-t-il ?

La réponse à la première question se trouve (de façon assez peu claire il est vrai) dans les release notes de l’UR12 de SCOM 2012 R2 :

  • SHA2 support for certificates:  SHA1 is deprecated for the System Center 2012 R2 Operations Manager Agent and SHA2 is now supported.

Autrement dit, le certificat auto-signé de l’agent est maintenant signé avec l’algorithme SHA2 (SHA256RSA) et non plus avec SHA1.

Tant mieux pour la sécurité, SHA1 est aujourd’hui considéré comme déprécié et ne devrait plus être utilisé.

La réponse à la seconde question se trouve encore une fois chez Microsoft : Windows 2003 (et par extension Windows XP) ne supporte pas les algorithmes de la famille SHA2 sans un hotfix qui n’a jamais été intégré aux mises à jour régulières, comme l’indique la KB suivante : http://support.microsoft.com/kb/938397

Une fois le hotfix installé et le serveur redémarré, on constate que l’agent démarre sans encombre et qu’un certificat signé en SHA256 est généré :

clip_image008

Et voilà, problème réglé !

SCOM – Prendre une trace de l’agent

Il peut arriver qu’un agent SCOM rencontre un problème (plantage, supervision qui échoue…) sans raison identifiable dans les journaux d’événement.

Dans ce cas, il reste une dernière solution : prendre une trace de debug.

Les outils le permettant sont disponibles nativement sur tous les serveurs où l’agent est installé.

Préparation

Dans un premier temps, il est nécessaire de se connecter au serveur où se trouve l’agent pour lequel vous souhaitez réaliser une trace puis localisez le dossier d’installation de l’agent, normalement C:\Program Files\System Center Operations Manager\Agent\Tools pour un SCOM 2012 ou C:\Program Files\Microsoft Monitoring Agent\Agent\Tools pour un SCOM 2012 R2.

Ouvrez une ligne de commande cmd.exe en mode administrateur, puis rendez vous dans le dossier en question et exécutez la commande StopTracing.cmd afin de vous assurer que toutes les traces préalables sont bien arrêtées.

clip_image002

Profitez-en pour relever le dossier où se trouvent les logs, indiqué à la ligne Log Filename (ici C:\Windows\Logs\OpsMgrTrace ) et supprimez tous les fichiers qu’il contient, afin de faire place nette pour la nouvelle trace à venir.

Prise de la trace

Toujours depuis votre invite de commande, tapez StartTracing.cmd VER (respectez bien les majuscules pour VER) puis attendez que le problème se reproduise ou, mieux, essayez de forcer sa reproduction : plus la trace durera longtemps et plus elle sera longue à traiter et à analyser…

clip_image004

Une fois le problème survenu, arrêtez la trace (toujours avec Stoptracing.cmd).

Traitement et analyse de la trace

Les fichiers ETL de trace sont, à la base, totalement illisibles : il faut donc les convertir en quelque chose que vous puissiez analyser.

Pour ce faire, lancez la commande formattracing.cmd. Elle peut être assez longue à s’exécuter selon la durée de votre trace.

clip_image006

Une fois terminée, vous constaterez que des fichiers .log ont été créés dans le dossier de logs. Ce sont eux que vous allez pouvoir ouvrir pour tenter de comprendre les soucis rencontrés par l’agent.

Mais même s’ils sont lisibles dans un notepad, ils restent assez peu digestes :

clip_image008

Vous pouvez utiliser l’outil CMTrace, issu du toolkit de SCCM ( https://www.microsoft.com/en-us/download/details.aspx?id=50012 ), pour obtenir un résultat un peu plus facilement exploitable :

clip_image010

Ensuite, à vous de jouer et de repérer les lignes avec des erreurs (normalement signalées par une balise [ERROR]) pour identifier l’origine de votre problème.

Agent SCOM 2012 R2 UR12 (et ultérieur) sur Windows 2003

Lors de la finalisation d’une migration side by side SCOM 2007 vers SCOM 2012 R2, j’ai rencontré un problème assez inattendu : il ne restait alors plus que quelques agents Windows 2003 à déployer et quelques autres à mettre à jour avec le dernier UR, et je n’anticipais pas de problème particulier dans cette phase déjà réalisée à maintes reprises sur d’autres serveurs de cet environnement ainsi que sur d’autres environnements.

J’ai donc eu la mauvaise surprise de constater que ces agents s’arrêtaient dès leur démarrage, parfois sans aucun message d’erreur (arrêt « propre » matérialisé par les événements 103 puis 101), parfois avec un message d’erreur assez peu parlant (événement 1000 après l’événement 103) :

clip_image001

clip_image002

Faulting application healthservice.exe, version 7.1.10292.0, stamp 585161d0, faulting module unknown, version 0.0.0.0, stamp 00000000, debug ? 0, fault address 0x000c9ba0.

J’ai alors décidé de prendre une trace de l’agent, afin d’obtenir un diagnostic plus poussé (cf. https://blog.piservices.fr/post/2017/09/30/SCOM-Prendre-une-trace-de-lagent ).

Fort heureusement le problème était très simple à reproduire (un simple démarrage de l’agent…), et m’a permis d’obtenir l’erreur suivante :

clip_image004[5]

Unable to create self-signed certificate : -2146893816(NTE_BAD_ALGID).

L’agent échoue donc à créer un certificat auto-signé lors de son démarrage, et plante dans la foulée.

Mais pourquoi chercherait-il à générer un certificat ? Comme le précise Stefan Roth dans cet article très détaillé (https://stefanroth.net/2016/03/02/scom-how-data-is-encrypted/), ce certificat est utilisé lorsque le Management Server transmet un RunAs Account à un agent afin d’apporter un niveau de chiffrement supplémentaire.

Ce certificat est donc créé lors du premier démarrage de l’agent, ainsi que lorsqu’il expire (sa durée de vie est d’un an) :

clip_image006[5]

Nous constatons sur la capture ci-dessus que le certificat est bel et bien présent dans le magasin.

Pourquoi l’agent cherche-t-il a le régénérer, et pourquoi échoue-t-il ?

La réponse à la première question se trouve (de façon assez peu claire il est vrai) dans les release notes de l’UR12 de SCOM 2012 R2 :

  • SHA2 support for certificates:  SHA1 is deprecated for the System Center 2012 R2 Operations Manager Agent and SHA2 is now supported.

Autrement dit, le certificat auto-signé de l’agent est maintenant signé avec l’algorithme SHA2 (SHA256RSA) et non plus avec SHA1.

Tant mieux pour la sécurité, SHA1 est aujourd’hui considéré comme déprécié et ne devrait plus être utilisé.

La réponse à la seconde question se trouve encore une fois chez Microsoft : Windows 2003 (et par extension Windows XP) ne supporte pas les algorithmes de la famille SHA2 sans un hotfix qui n’a jamais été intégré aux mises à jour régulières, comme l’indique la KB suivante : http://support.microsoft.com/kb/938397

Une fois le hotfix installé et le serveur redémarré, on constate que l’agent démarre sans encombre et qu’un certificat signé en SHA256 est généré :

clip_image008[5]

Et voilà, problème réglé !

Outlook – Dossier en double suite à l’ajout d’une BAL OVH en IMAP

Problématique

Après de l’ajout d’une boite mail OVH en IMAP et lors de la synchronisation de celle ci les dossiers permettant de trier les emails envoyés, supprimés, brouillons et indésirables n'ont pas été automatiquement reconnu.

De ce fait Outlook à crée ses propres dossiers.

image

Solution

Pour remédier au problème il faut dans un premier temps faire le ménage dans vos emails pour les déplacer dans ces nouveaux dossiers.

Il ne reste plus qu’a modifier dans Outlook le chemin d’accès au dossier racine.

Aller dans “Fichier” –> “Paramètres du compte” :

image

Sélectionner le compte OVH puis cliquer sur “Modifier”

image

Cliquer sur “Paramètres supplémentaires”

image

Puis modifier le chemin en écrivant “INBOX”

image

En validant sur “OK” le compte va ce synchroniser correctement sans créer de nouveau dossiers.

image

image

Azure - Gestion des licences par groupe avec des groupes dynamiques

Description

Pour attribuer une licence a un utilisateur de votre annuaire Azure, cela nécessite l’une des étapes suivantes :

  • Attribution de licences directement aux utilisateurs par l’intermédiaire du portail, de PowerShell ou des API.
  • Attribution de licences à des groupes dans le portail Azure.

Quand vous attribuez des licences à un groupe, tous les membres de ce groupe disposent d’une licence. Si des utilisateurs sont ajoutés au groupe ou en sont supprimés, la licence appropriée leur est attribuée ou retirée.

Vous pouvez utiliser l’attribution de licence basée sur le groupe pour configurer des règles telles que les suivantes :

  • Tous les utilisateurs de votre annuaire obtiennent automatiquement une licence
  • Toute personne avec la fonction appropriée obtient une licence

Configuration

Pour créer un groupe il faut utiliser votre compte d’administration Azure et se rendre à l’adresse suivante : https://portal.azure.com.

Une fois connecté vous devez aller dans le menu « Azure Active Directory ».

clip_image001

Créer un groupe dans le sous menu « Utilisateurs et groupes ».

clip_image002

clip_image003[4]

Puis revenir dans l’onglet « Azure Active Directory » et cette fois ci sélectionner « Licences ».

clip_image004[4]

Une fois que vous êtes dans le menu de vos licences sélectionnez le produit sur lequel vous voulez créer un groupe dynamique et cliquez sur « Attribuer ».

clip_image005[4]

Choisissez le groupe que vous avez crée et configuré le les options de la licence. Cette étape est importante car tous les utilisateur qui seront dans le groupe hériterons de ces options.

clip_image006[4]

clip_image008

Ajouter les membres

clip_image010

L’utilisateur ci-dessous hérite maintenant du groupe «DL_O365_E5»

clip_image011

Information :

Pour plus d’information concernant la configuration, je vous invite à consulter le lien suivant :

Manage Azure Active Directory licencing

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

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

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

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