Le blog technique

Toutes les astuces #tech des collaborateurs de PI Services.

#openblogPI

Voir tous les articles

Retrouvez les articles à la une

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

par | 27 Sep 2017 |

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

par | 27 Sep 2017 |

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

 

 

Gestion de WSUS en Powershell – Partie 3

par | 26 Sep 2017 | ,

Dans cette partie nous allons voir comment utiliser la fonction Cleanup via Powershell

1 – Les Variables

La fonction « Cleanup » permet de nettoyer la base du serveur WSUS.

Il est possible de supprimer :

  • Les Superseded Updates  ou mises à jour remplacées en Français
  • Les Expired Updates ou mises à jour expirées en Français
  • Les Obsolete Updates ou mises à jour obsolètes en Français
  • Les Compress Updates ou mises à jour inutiles en Français
  • Les Obsolete Computers ou les ordinateurs obsolètes en Français
  • Les Unneeded ContentFiles ou Fichiers de mise jour inutiles en Français

Dans l’exemple ci-dessous les variables seront par défaut à « $False » il suffit de mettre « $True » pour valider la fonction.

    # Variables de Cleanup: 
# Decline updates that have not been approved for 30 days or more, are not currently needed by any clients, and are superseded by an aproved update. 
[Boolean]$SupersededUpdates = $false 
# Decline updates that aren't approved and have been expired my Microsoft. 
[Boolean]$ExpiredUpdates = $false 
# Delete updates that are expired and have not been approved for 30 days or more. 
[Boolean]$ObsoleteUpdates = $false 
# Delete older update revisions that have not been approved for 30 days or more. 
[Boolean]$CompressUpdates = $false 
# Delete computers that have not contacted the server in 30 days or more. 
[Boolean]$ObsoleteComputers = $True 
# Delete update files that aren't needed by updates or downstream servers. 
[Boolean]$UnneededContentFiles = $false

 

2 – La commande

Une fois les variables définies, il faut indiquer le « Cleanup Scope » qui permet d’établir quels paramètres seront nettoyer, pour cela nous utiliserons la commande suivante :

$CleanupScope = New-Object Microsoft.UpdateServices.Administration.CleanupScope($supersededUpdates,$expiredUpdates,$obsoleteUpdates,$compressUpdates,$obsoleteComputers,$unneededContentFiles)

Une fois le « Cleanup Scope » définit, il ne reste plus qu’a exécuter la commande de nettoyage ci-dessous : 

($Wsus.GetCleanupManager()).PerformCleanup($CleanupScope)

Ou

$Cleanup = $Wsus.GetCleanupManager()
$Cleanup.PerformCleanup($CleanupScope)

 

3 – Bonus

Si vous possédez plusieurs serveurs WSUS, il est possible d’exécuter ce script (dans cet exemple nous ciblons uniquement les Ordinateurs obsolètes, remplacez les « $False » par « $True » pour valider les autres paramètres) :

<pre class="wp-block-syntaxhighlighter-code"># Script de Cleanup

$LogCatch = "$env:USERPROFILE\Desktop\LogCatch.txt"

# Détection des WSUS
Get-ADComputer -Filter { (Name -like "*WSUS*") -and (Enabled -eq $true)} | Select-Object -Property DNSHostName | Sort-Object -Property DNSHostName | ForEach-Object {
    $DNSHostName = $_."DNSHostName"
    
#region - Connexion au WSUS
        # Varibles de connexions
            $WsusServer = $DNSHostName
            $WsusPort = "8530"
        # Valeur max de prise en compte d'une machine (ici 30 jours sans connexion au serveur WSUS)
            $thirtydaysago = (get-date).adddays(-30)
            $DaysComputerStale = "30"

        #region - Ouverture de la connexion au serveur 
        $ErrorActionPreference = 'SilentlyContinue'
        Try {
            [void][reflection.assembly]::loadwithpartialname("microsoft.updateservices.administration")
            $Wsus = [microsoft.updateservices.administration.adminproxy]::getupdateserver($WsusServer,$false,$WsusPort)
            $Wsus.Name
            $Log = $Wsus.Name
            }
        Catch {
            Write-Warning "$($WsusServer)<$($WsusPort)>: $($_)" | Add-Content -Path $LogCatch
            $Connection = "Failed"
            $finalWorkSheet.Cells.Item($FinalExcelRow,9) = $Connection
            }
            If ($Log -eq $null){
                Try {
                    $WsusPort2 = "80"
                    [void][reflection.assembly]::loadwithpartialname("microsoft.updateservices.administration")
                    $Wsus = [microsoft.updateservices.administration.adminproxy]::getupdateserver($WsusServer,$false,$WsusPort2)
                    $Wsus.Name
                    }
                Catch {
            Write-Warning "$($WsusServer)<$($WsusPort2)>: $($_)" | Add-Content -Path $LogCatch
                        }
                }
        $ErrorActionPreference = 'SilentlyContinue'
        #endregion - Ouverture de la connexion au serveur
#endregion - Connexion au WSUS

#region - Cleanup
    # Variables de Cleanup: 
    # Decline updates that have not been approved for 30 days or more, are not currently needed by any clients, and are superseded by an aproved update. 
    [Boolean]$supersededUpdates = $false 
    # Decline updates that aren't approved and have been expired my Microsoft. 
    [Boolean]$expiredUpdates = $false 
    # Delete updates that are expired and have not been approved for 30 days or more. 
    [Boolean]$obsoleteUpdates = $false 
    # Delete older update revisions that have not been approved for 30 days or more. 
    [Boolean]$compressUpdates = $false 
    # Delete computers that have not contacted the server in 30 days or more. 
    [Boolean]$obsoleteComputers = $True 
    # Delete update files that aren't needed by updates or downstream servers. 
    [Boolean]$unneededContentFiles = $false 

    $CleanupScope = New-Object Microsoft.UpdateServices.Administration.CleanupScope($supersededUpdates,$expiredUpdates,$obsoleteUpdates,$compressUpdates,$obsoleteComputers,$unneededContentFiles) 

    ($Wsus.GetCleanupManager()).PerformCleanup($CleanupScope)

#endregion - Cleanup

#region - Release des Variables
$Name = $null
$WsusPort = $null
$Wsus = $null
$Log = $null
#endregion - Release des Variables
}</pre>

La nomenclature de mes serveurs comporte « WSUS » dans le nom, je passe donc par un « Get-Adcomputer », mais vous pouvez très bien remplacer cela par un « Import-CSV ».

Derniers articles

Catégories

Archives

Auteurs/Autrices