/* 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

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

###########################################################################################################################################
## SCRIPT TO START/STOP/DISPLAY MAINTENANCE MODE FOR HEALTH SERVICE WATCHER OBJECTS THAT DEPENDS FROM SPECIFIC MANAGEMENT SERVER OR GATEWAY
## Author: CJOURDAN
###########################################################################################################################################
#Parameters
    # $MGroup : Management Group to connect to
    # $MStoConnect : Management server to connect to
    # $PrimMS : Management server or Gateway FQDN from which depend agents.
    # $Reason : Reason for Maintenance Mode (Valid Reason are: PlannedOther
    #                                                          UnPlannedOther
    #                                                          PlannedHardwareMaintenance 
    #                                                          UnplannedHardwareMaintenance 
    #                                                          PlannedHardwareInstallation
    #                                                          UnplannedHardwareInstallation
    #                                                          PlannedOperatingSystemReconfiguration
    #                                                          UnplannedOperatingSystemReconfiguration
    #                                                          PlannedApplicationMaintenance
    #                                                          ApplicationInstallation
    #                                                          ApplicationUnresponsive
    #                                                          ApplicationUnstable
    #                                                          SecurityIssue
    #                                                          LossOfNetworkConnectivity)
    # 
    # $Comment : Comment for Maintenance Mode
    # $MMAction : START or STOP OR DISPLAY Maintenance Mode
    # $MMDuration : Maintenance Mode Duration in minutes (only valuable for START $MMAction)

    # EXAMPLES:
    # Display Health Service Watcher Maintenance Mode Status for agents primary managed by MyPriMaryMS.mydomain.home
        # .\ScomSwitchMM.ps1 -MGroup MyMGROUP -MStoConnect MyMS.mydomain.home -PrimMS MyPriMaryMS.mydomain.home -MMAction display
    # Start Health Service Watcher Maintenance Mode for agents primary managed by MyPriMaryMS.mydomain.home during 60 min
        # .\ScomSwitchMM.ps1 -MGroup MyMGROUP -MStoConnect MyMS.mydomain.home -PrimMS MyPriMaryMS.mydomain.home -MMAction start -MMDuration 60






Param(
[Parameter(Mandatory=$false)]
$ClassName = "Health Service Watcher",
[Parameter(Mandatory=$false)]
$MGroup = "MyMGROUP",
[Parameter(Mandatory=$false)]
$MStoConnect= "MyMS.mydomain.home",
[Parameter(Mandatory=$true)]
$PrimMS= "MyPriMaryMS.mydomain.home",
[Parameter(Mandatory=$false)]
$Reason = "PlannedOther",
[Parameter(Mandatory=$false)]
$Comment = "",
[Parameter(Mandatory=$true)]
$MMAction = $(Read-Host -Prompt "PROVIDE TYPE OF MAINTENANCE MODE ACTION: START or STOP OR DISPLAY"),
[Parameter(Mandatory=$false)]
# $MMDuration in minutes
$MMDuration = 0
)



if ($MMAction -notmatch ".*STOP.*|.*START.*|.*DISPLAY.*")
{
write-host -ForegroundColor Yellow "YOU MUST PROVIDE VALID MAINTENANCE MODE ACTION TYPE: START or STOP or DISPLAY"
EXIT 1
}

$cred = $(Get-Credential -Credential "MyDomain\")

    


#Import du module SCOM
try
{
Import-Module -Name OperationsManager -ErrorAction stop
}
catch
{
write-host -ForegroundColor red "Error during import of SCOM PS Module"
EXIT 1
}


#Connection au management group $MGroup
try
{
New-SCOMManagementGroupConnection -ComputerName $MStoConnect -Credential $cred
}
catch
{
write-host -ForegroundColor red "Error during connection to MS $MStoConnect - CHECK CREDENTIALS USED"
EXIT 1
}



# Get Scom agents that have $PrimMS as Primary Management Server
$agents = get-scomagent | where {$_.PrimaryManagementServerName -eq $PrimMS}

if (!($agents))
    {
    write-host "NO SCOM AGENTS FOUND FOR SERVER $PrimMS - CHECK THAT THE NAME IS CORRECT (YOU MUST PROVIDE FQDN) OR THAT THIS SERVER BELONG TO $MGroup MANAGEMENT GROUP"
    EXIT 1
    }

# Get "Health Service Watcher" Class Instances
$class = Get-SCOMClass -DisplayName $ClassName




switch($MMAction)
    {
    START
                          {


                            # Get instances of "Health Service Watcher" Class
                            $ClassInstances = Get-SCOMClassInstance -Class $class

                            # Class to put in MM Table
                            $ClassInstToMM = @()

                            # Get class instances that match agents
                            foreach ($agent in $agents)
                            { 
                            $ClassInstToMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname})
                            }


                            $starttime = $(get-date).ToUniversalTime()  
                            $endtime = $(get-date).AddMinutes($MMDuration).ToUniversalTime()

                            # PUT IN Maintenance Mode
                            $($ClassInstToMM | foreach {$_.schedulemaintenancemode($starttime,$endtime,$Reason,$Comment)})


                            # Display effective instances in MM
                            write-host "EFFECTIVE INSTANCES OF CLASS '$ClassName' IN MAINTENANCE MODE (Primary Managed by $PrimMS):"

                                # Get instances of "Health Service Watcher" Class
                                $ClassInstances = Get-SCOMClassInstance -Class $class

                                $ClassInstInMM = @()

                                # Get class instances that match agents
                                foreach ($agent in $agents)
                                { 
                                $ClassInstInMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname})
                                }

                                $ClassInstInMM

                            }

    STOP
                                 {


                            # Get instances of "Health Service Watcher" Class
                            $ClassInstances = Get-SCOMClassInstance -Class $class

                            # Class to put out of MM Table
                            $ClassInstOoMM = @()

                            # Get class instances that match agents
                            foreach ($agent in $agents)
                            { 
                            $ClassInstOoMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname})
                            }

                            # Only Endttime in necessary and it is now
                            $endtime = $(get-date).ToUniversalTime()  
                            

                            # PUT IN Maintenance Mode
                            $($ClassInstOoMM | foreach {$_.stopmaintenancemode($endtime)})


                            # Display effective instances in MM
                            write-host "EFFECTIVE INSTANCES OF CLASS '$ClassName' OUT OF MAINTENANCE MODE (Primary Managed by $PrimMS):"

                                # Get instances of "Health Service Watcher" Class
                                $ClassInstances = Get-SCOMClassInstance -Class $class

                                $ClassInstOoMM = @()

                                # Get class instances that match agents
                                foreach ($agent in $agents)
                                { 
                                $ClassInstOoMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname})
                                }

                                $ClassInstOoMM

                            }


    DISPLAY                  
    
                                {

                            # Display effective instances in MM
                            write-host "`n INSTANCES OF CLASS '$ClassName' MAINTENANCE MODE STATE (Primary Managed by $PrimMS):"

                                # Get instances of "Health Service Watcher" Class
                                $ClassInstances = Get-SCOMClassInstance -Class $class

                                $ClassInstMM = @()
                                
                                # Get class instances that match agents
                                foreach ($agent in $agents)
                                { 
                                $ClassInstMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname})
                                }

                                $ClassInstMM

                                }



    }

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

##############################################################################

# SCRIPT DE RECUPERATION DES ALERTES D'UNE APPLIANCE HPONEVIEW ET MISE EN ETAT CLEARED
# author: cjourdan

 

# PARAMETRES:
# $ApplianceName: Nom de l'appliance
# $RessourceName: Nom de la ressource (Server,Enclosure ...).
# $DomainName: Domaine AD.
# $AlertSeverity: Critical, Warning, OK, Unknown, Disabled
# $AlertState: Active, Locked, Cleared, Pending, Running, Completed, Interrupted, Error, Warning, Terminated, Killed.
# $DescInclude: Chaine de caractère dans la champ Description a inclure dans la recherche.
# $DescExclude: Chaine de caractère dans la champ Description a exclure de la recherche.
# $DayOffset = Recuperer les alertes plus recente que J-DayOffset.


# Apres Recuperation des alertes, le script demande validation pour mettre les alertes en état Cleared


Param(

[Parameter(Mandatory=$false)]

$ApplianceName = "MyAppliance",

[Parameter(Mandatory=$false)]

# Server,Enclosure ...

$RessourceName = "MyResource",

[Parameter(Mandatory=$false)]

$DomainName = "MyDomain",

[Parameter(Mandatory=$false)]

$AlertSeverity = "Critical",

[Parameter(Mandatory=$false)]

$AlertState = "Cleared",

$DescInclude = "*",

$DescExclude = "azerty",

$DayOffset = 2

)

 

$user = Read-Host -Prompt "Enter MyDomain Credential without Domain" 

$passwd = Read-Host -Prompt "Password" -AsSecureString

 

if (-not (get-module HPOneview.200)) 

{
 

    Import-Module HPOneView.200
 

}

 

# Connexion a l'appliance OneView

if (-not($global:ConnectedSessions))

{ 
       

       Connect-HPOVMgmt -Hostname $ApplianceName -UserName $user -Password $passwd -AuthLoginDomain $DomainName
 

}

 

write-host -BackgroundColor White -ForegroundColor Blue "APPLIANCE ONEVIEW: $ApplianceName"`n

 

# NB: Preciser au minimum un filtre de recherche avant le '|' afin de reduire le temps de recuperation des alertes

Try

{

    $Alerts = Get-HPOVAlert -ApplianceConnection $ApplianceName -Severity $AlertSeverity | Where-Object {$_.AlertState -eq $AlertState `

    -AND $_.associatedresource -like "*$RessourceName*" `

    -AND $_.description -like "*$DescInclude*" `

    -AND $_.description -notlike "*$DescExclude*"`

    -AND $([datetime]$_.created) -gt $(get-date).AddDays(-$DayOffset)

    } 

 

    $FormAlerts = foreach ($alert in $Alerts)

        {

        Write-output "DESCRIPTION: "$alert.description"" ;

        Write-Output "SEVERITY: "$alert.severity"" ;

        Write-Output "STATE: "$alert.alertState"" ;

        Write-Output "CREATED: "(get-date -format G $alert.Created)"" ;

        Write-Output "CLEARED: "(get-date -format G $alert.clearedtime)"" ;

        Write-Output "RESSOURCE_NAME: "$alert.associatedresource.resourcename"" ; 

        Write-Output "APPLIANCE: "$alert.ApplianceConnection.Name"" ;

        Write-Output "" ; 

        Write-Output "*******************" ;

        Write-Output ""

        } 

 

    write-host -BackgroundColor White -ForegroundColor Blue "CRITERES DE RECHERCHE: Resource: $RessourceName / Severity: $AlertSeverity / Status: $AlertState /  a inclure: $DescInclude / a exclure: $DescExclude / Plus recentes que: J-$DayOffset"`n

    $count=$Alerts | Measure-Object | select count -ExpandProperty count

    write-host -BackgroundColor White -ForegroundColor Blue "NOMBRE D ALERTES: $count" `n

    $FormAlerts

 

}

 

Catch

{

       Write-Error -ErrorRecord $_ -ErrorAction Stop

}


$answer = Read-Host -Prompt "VOULEZ VOUS MODIFIER L'ETAT DES CES ALERTES EN ETAT 'CLEARED' ?: Y/N"
 

switch($answer)

    {

        "Y" {"MISE EN ETAT CLEARED"}

        "N" {"AUCUNE ACTION" ; exit}

        default {"MAUVAISE REPONSE - AUCUNE ACTION" ; exit}

    }

 

# Clear Alerts


         Try

         {

          $updatedAlert = $Alerts | set-HPOVAlert -Cleared
         
         }

             Catch

             {
             Write-Error -ErrorRecord $_ -ErrorAction Stop
             }


    $updatedAlert

 

    Write-Host -ForegroundColor Green "MISE EN ETAT CLEARED TERMINEE SANS ERREUR !"