PI Services

Le blog des collaborateurs de PI Services

SCOM - Script - Comparaison Agents et Liste Machines

Le script ci-dessous propose de comparer un extract des agents scom avec une liste de machine, pour un suivi de déploiement par exemple.

Compare_SCOMAgent_ComputerList.ps1 (1,67 kb)

 

### COMPARE SCOM AGENTS AND COMPUTER LIST IN TXT FILE

#Parameters
Param(
[Parameter(Mandatory=$false)]
$MS="MyMS.MyDomain",
[Parameter(Mandatory=$false)]
$Cred = $(Get-Credential "MyDomain\Me"),
[Parameter(Mandatory=$false)]
$FilePath = "C:\ServerList.txt "
)


#Import du module SCOM
try
{
Import-Module -Name OperationsManager -ErrorAction stop
}
catch
{
write-host -ForegroundColor red "Erreur lors de l'import du module SCOM"
}

#Connection au management group $MGroup
New-SCOMManagementGroupConnection -ComputerName $MS -Credential $Cred


# Recuperation de la liste des agents (netbios name
$agents = Get-SCOMAgent | select computername -ExpandProperty computername


# Import du fichier $FilePath
try
{
$ComputerList = Get-Content -Path $FilePath
}
catch
{
write-host -ForegroundColor red "Error during import of `"$FilePath`""
}




# Machines dans Scom ET dans la liste
$Both = $(Compare-Object -ReferenceObject $agents -DifferenceObject $ComputerList -IncludeEqual -ExcludeDifferent).inputobject

# Machines uniquement dans SCOM
$InScom = $(Compare-Object -ReferenceObject $agents -DifferenceObject $ComputerList | Where-Object {$_.sideindicator -eq "<="}).inputobject

# Machines uniquement dans la liste
$InFile = $(Compare-Object -ReferenceObject $agents -DifferenceObject $ComputerList | Where-Object {$_.sideindicator -eq "=>"}).inputobject




write-host -B White -F Blue "`n $($both.Count) COMPUTERS THAT ARE IN SCOM AND IN FILE:"
$Both

write-host -B White -F Blue "`n $($InScom.Count) COMPUTERS THAT ARE ONLY IN SCOM:"
$InScom

write-host -B White -F Blue "`n $($InList.Count) COMPUTERS THAT ARE ONLY IN FILE:"
$InFile



 

SCOM - Script - Supprimer un agent de la console en fonction de la valeur d'une ou plusieurs propriété d'une classe

Le script ci-dessous propose le scenario ou l'on doit automatiser la suppression d'un agent de la console (pas une desinstallation) en fonction de la valeur d'une ou plusieurs propriétés de l'instance d'une classe pour ce/ces agents.

Ceci peut permettre par exemple dans la cas de la présence d'une classe étendue (avec vos propres propriétés issues par exemple d'un outil tiers) de decommissionner un ou plusieurs agents automatiquement, selon la valeur de ces propriétés (Dans l'exemple inscrit par defaut dans les paramètres, on cherche la valeur 'Useless' ou 'Deprecated' de la propriété 'Usage' de la classe 'MyClass')

 

RemoveAgentUponPropertyVal.ps1 (10,67 kb)

 

## RemoveAgentUponPropertyVal.ps1
## SCOM - REMOVE AGENT(S) FROM CONSOLE THAT HAVE SPECIFIC VALUES IN ONE OR MORE CLASS PROPERTY.
## AUTHOR: C.JOURDAN
## Version: 1.0

##  PARAMETERS
##  $MS: Target Management Server
##  $ObjectClass: Display Name of Target Class
##  $FirstProperty: name of class property
##  $FirstPropVal: multi value possible of $FirstProperty
##  $ThreshNotDelete: Nb of found computers to delete over which we only warn (NO AUTOMATIC DELETE)

## NOTES: $ThreshNotDelete PARAMETER IS A SECURITY OPTION TO AVOID DELETION OF TWO MANY AGENTS. BE SURE OF THE APPLIED CRITERIAS BEFORE UNLOCK THAT! 


#PARAMETERS
Param(
[Parameter(Mandatory=$false)]
$MGroup,
[Parameter(Mandatory=$false)]
$MS='localhost',
[Parameter(Mandatory=$false)]
$ObjectClass = 'MyClass',
[Parameter(Mandatory=$false)]
$FirstProperty='Usage',
[Parameter(Mandatory=$false)]
$FirstPropVal="^.*(Useless|Deprecated).*$",
<# -- ADDITIONAL PROPERTIES FROM $ObjectClass INSTANCE -- SEE CLASS INSTANCE RETRIEVE SECTION
[Parameter(Mandatory=$false)]
$SecondProperty='Prop2',
[Parameter(Mandatory=$false)]
$SecondPropVal='Value2'
#>
[Parameter(Mandatory=$false)]
$ThreshNotDelete = 10
)

#ScriptName
$ScriptName = "RemoveAgentUponPropertyVal.ps1"


#FUNCTIONS

# NewEventSource
# Check of a source existance  in the 'operation manager' eventlog that match the script name, to log some events.
         Function NewEventSource 
         { 
         if(!(Test-Path "HKLM:\SYSTEM\CurrentControlSet\services\eventlog\Operations Manager\$ScriptName")) 
         { 
         New-EventLog -LogName "Operations Manager" -Source $ScriptName 
         }
         } 


# DeleteSCOMAgent
# Remove agent from SCOM Console.
Function DeleteSCOMAgent 
{
Param(
  [string[]]$AgentComputerName,
  [string]$MSServer
)

[System.Reflection.Assembly]::Load("Microsoft.EnterpriseManagement.Core, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35")
[System.Reflection.Assembly]::Load("Microsoft.EnterpriseManagement.OperationsManager, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35")



function New-Collection ( [type] $type ) 
{
	$typeAssemblyName = $type.AssemblyQualifiedName;
	$collection = new-object "System.Collections.ObjectModel.Collection``1[[$typeAssemblyName]]";
	return ,($collection);
}




# Connect to management group
Write-output "Connecting to management group"

$ConnectionSetting = New-Object Microsoft.EnterpriseManagement.ManagementGroup($MSServer)
$admin = $ConnectionSetting.GetAdministration()


Write-output "Getting agent managed computers"
$agentManagedComputers = $admin.GetAllAgentManagedComputers()

# Get list of agents to delete
foreach ($name in $AgentComputerName) 
{
    Write-output "Checking for $name"
    foreach ($agent in $agentManagedComputers)
    {
        if ($deleteCollection -eq $null) 
        {
            $deleteCollection = new-collection $agent.GetType()
        }

        
        if (@($agent.PrincipalName -eq $name))
        {
	    Write-output "Matched $name"
            $deleteCollection.Add($agent)
            break
        }
    }
}

if ($deleteCollection.Count -gt 0) 
{
    Write-output "Deleting agents"
    $admin.DeleteAgentManagedComputers($deleteCollection)
    if($?){
           $Script:result="Agents deleted"
           Write-Output $result
          }
    Else  {
           $result="Error during deletion of one ore more agent"
           Write-Output $result
          }
}

Else
    {
    $result="No Agent found to delete"
    Write-Output $result
    }


}




#END FUNCTIONS


#Log of script execution 
NewEventSource 
write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1000 -Message "Execution du script $ScriptName" -EntryType Information 



#Import of SCOM Powershell module
try
{
Import-Module -Name OperationsManager -ErrorAction stop
}
catch
{
write-host -ForegroundColor red "Error during import of SCOM PS Module"
NewEventSource 
write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1001 -Message "Error during import of SCOM PS Module" -EntryType Error 
exit 1 
}



#Connection to management group $MGroup
try
{
New-SCOMManagementGroupConnection -ComputerName $MS
}
catch
{
write-host -ForegroundColor red "Error during connection to MS $MS"
NewEventSource 
write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1002 -Message "Error during connection to MS $MS" -EntryType Error 
exit 1 
}


# Get $ObjectClass Class
$Class = Get-SCOMClass -displayname $ObjectClass | Where-Object {$_.PropertyCollection -match "^.*($FirstProperty|$SecondProperty).*$"} -ErrorAction stop    ## -- WE CHECK THAT THE TARGET CLASS REALLY HOLD THE WANTED PROPERTIES
if (!($Class))
{
$message = "ERROR DURING RETRIEVE OF '$ObjectClass' CLASS. CHECK THAT CLASS EXIST OR THAT THE PROPERTIES YOU WANT EXIST IN THIS CLASS"
write-host -ForegroundColor red $message
NewEventSource 
write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1003 -Message $message -EntryType Error 
exit 1 
}




# Get Computers that have $FirstProperty value as: $FirstPropVal 
try
{
$TargetComp = $Class | Get-SCOMClassInstance | Where-Object {
$_."[$($Class.Name)].$FirstProperty".value -match $FirstPropVal -OR $_.$FirstProperty -match $FirstPropVal`               ##-- DIFFERENT COMBINATION (THE "[$($Class.Name)]" SYNTAX IS TO INCLUDE CASE OF A NOTE PROPERTY
#-AND $_."[$($Class.Name)].$SecondProperty".value -eq $SecondPropVal -OR $_.$SecondProperty -eq $SecondPropVal`     ##-- ADDITIONAL POTENTIAL PROPERTIES (SEE SCRIPT PARAMS)
} -ErrorAction Stop

#$TargetComp = $Class | Get-SCOMClassInstance | Where-Object {                                                       ## -- TO TEST UNFOUND COMPUTER SCENARIO
#$_."[$($Class.Name)].$FirstProperty".value -eq "azerty" -OR $_.$FirstProperty -eq "azerty"`                        ## -- TO TEST UNFOUND COMPUTER SCENARIO
#} -ErrorAction Stop                                                                                                ## -- TO TEST UNFOUND COMPUTER SCENARIO

}
catch
{
$message = "Error during retrieve of '$ObjectClass' instances that have $FirstProperty : $FirstPropVal"
write-host -ForegroundColor red $message
NewEventSource 
write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1004 -Message $message -EntryType Error 
exit 1 
}


# Analyse the content of $TargetComp
switch($TargetComp.Count)
{
{$_ -lt 1}                  {            
                            $message = "NO AGENT TO REMOVE"
                            Write-Host -F Blue -B White $message
                            NewEventSource 
                            write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1005 -Message $message -EntryType Information 
                            }


{$_ -ge 1 -AND $_ -le $ThreshNotDelete} ## -- SECURITY OPTION TO AVOID DELETION OF TWO MANY AGENTS. BE SURE OF THE APPLIED CRITERIAS BEFORE UNLOCK THAT!
                            {
                            $message = "FOLLOWING AGENTS WILL BE REMOVED:"
                            Write-Host -F Yellow $message
                            $TargetComp.displayname
                                                            
                                DeleteSCOMAgent -AgentComputerName $TargetComp.displayname -MSServer $MS
                                #$result
                                switch($result)
                                    {
                                    "Agents deleted" 
                                                                                        {
                                                                                            $message = "FOLLOWING AGENTS HAS BEEN REMOVED FROM SCOM CONSOLE SINCE THEIR $FirstProperty IS EQUAL: $FirstPropVal . $($TargetComp.displayname)"
                                                                                            NewEventSource 
                                                                                            write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1006 -Message $message -EntryType Information 
                                                                                        }
                                    
                                    "Error during deletion of one ore more agent" 
                                                                                        {
                                                                                            $message = "ERROR DURING DELETION OF ONE OR MORE AGENT! MANUAL CHECK REQUIRED"
                                                                                            NewEventSource 
                                                                                            write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1007 -Message $message -EntryType Warning
                                                                                        } 

                                    "No Agent found to delete" 
                                                                                        {
                                                                                            $message = "NO AGENT FOUND TO DELETE BY DeleteSCOMAgent FUNCTION! MANUAL CHECK REQUIRED"
                                                                                            NewEventSource 
                                                                                            write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1008 -Message $message -EntryType Warning
                                                                                        } 


                                    
                                
                                    
                                
                            
                                }

                            }

{$_ -gt $ThreshNotDelete}                 {
                            $message = "WARNING: NUMBER OF AGENTS TO REMOVE IS GREATER THAN 10 ! MANUAL CHECK REQUIRED"  ## -- SECURITY OPTION TO AVOID DELETION OF TWO MANY AGENTS. BE SURE OF THE APPLIED CRITERIAS BEFORE UNLOCK THAT!
                            Write-Host -F red $message
                            $TargetComp.displayname
                            NewEventSource 
                            write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1009 -Message $message -EntryType Warning
                            }


}



 

 

 

 

Zabbix - Script Bash de sauvegarde de la base

Ci-dessous, un script bash de sauvegarde complète de la base Zabbix.

BackupZabbixDBFull.sh (1,17 kb)

 

#! /bin/bash
#Def de variables
user="<user>" #Utilisateur de la base de donnees
passwd="<password>" #Mot de passe de l'utilisateur de la base
db="zabbixdb" #Nom de la base
dest="/etc/backup/mysql-full-backup" #Chemin de destination de la sauvegarde (Attention, pas de slash a la fin)
nbsav=5 #Nombre de sauvegardes a conserver

#On fabrique les variables systeme
dte=$(date +"%Y-%m-%d--%H-%M-%S")
fic="$db/$db-$dte.sql"

#### SCRIPT ####
if [ -d $dest/$db ]
then
echo "On sauvegarde dans $db"
else
echo "On cree le dossier de sauvegarde $dest/$db"
mkdir $dest/$db
fi
echo "sauvegarde de la base $db dans $dest/$fic"
ionice -c3 nice -n19 mysqldump -u $user -p$passwd $db > "$dest/$fic"
echo "On compresse "$dest/$fic" avec gzip : $fic.gz"
ionice -c3 nice -n19 gzip "$dest/$fic"

nbfic=$(ls -C1X $dest/$db/$db* | wc -l)
diff=$(echo $(($nbfic-$nbsav)))
echo "On calcule le nombre de fichiers a supprimer pour ne garder que les $nbsav derniers : $diff"
i=1 #Compteur du for
for f in $(ls -C1X "$dest/$db")
do
if [ $i -le $diff ]
then
oldsav="$dest/$db/$f"
echo "On supprime $dest/$db/$f"
rm -f "$dest/$db/$f"
let i++
fi
done
 

 

 

Script - Suivi des machines ayant l'agent Scom et l'agent Zabbix

Pour faire suite a l'article recent "SCOM - Zabbix - Management Pack pour l'agent Zabbix sur Windows" qui propose de créer et decouvrir une classe 'ZabbixAgent', voici un script permettant de faire un suivi des machines ayant l'agent Scom et l'agent Zabbix.

 

ScomAndZabbix.ps1 (1,20 kb)

 

## RETRIEVE COMPUTERS THAT HAVE SCOM AGENT AND ZABBIX AGENT


#Variables
$MGroup = "MyMG"
$MS= "MyMGServer.mydomain"
$cred = Get-Credential "ME\Myself"


#Import du module SCOM
try
{
Import-Module -Name OperationsManager -ErrorAction stop
}
catch
{
write-host -ForegroundColor red "Erreur lors de l'import du module SCOM"
}

#Connection au management group $MGroup
New-SCOMManagementGroupConnection -ComputerName $MS -Credential $cred



# Classe des "Health Service"
$AgentClass = Get-SCOMClass -DisplayName "Health Service"

# Classe des "Zabbix Agent"
$ZabbAgClass = Get-SCOMClass -DisplayName "ZabbixAgent"

# Instances de la classe $AgentClass
$AgentInst = $AgentClass | Get-SCOMClassInstance | select -Property *

# Instances de la classe $ZabbAgClass
$ZabbAgInst = $ZabbAgClass | Get-SCOMClassInstance | select -Property *


# COMPARAISON (MACHINE AYANT L'AGENT ZABBIX ET L'AGENT SCOM)
$ScomAndZab =  Compare-Object -ReferenceObject $AgentInst.displayname -DifferenceObject $ZabbAgInst.path -IncludeEqual -ExcludeDifferent
Write-Host "--- MANAGEMENT GROUP SCOM: $MS ---"
write-host "--- "$ScomAndZab.count" COMPUTERS THAT HAVE SCOM AND ZABBIX ---"
$ScomAndZab.inputobject | sort

 

SCOM - Script - Fonction de suppression d'agent a distance

La fonction ci-dessous permet de supprimer un agent scom en se connectant a distance a un management server donné.

Function Remove-SCOMAgent
{

 [CmdletBinding()]
 Param
    (
        [Parameter(Mandatory=$true, Position=0)]
        [string]$MServer,
        [Parameter(Mandatory=$true, Position=1)]
        [string]$AgentName,
        [Parameter(Mandatory=$true, Position=2)]
        [string]$User,
        [Parameter(Mandatory=$true, Position=3)]
        [string]$Domain

    )

$Cred = Get-Credential -Message "password" -Username "$Domain\$User"
	


#Import-Module OperationsManager            
New-SCOMManagementGroupConnection -ComputerName $MServer -Credential $Cred
Start-Sleep -s 3

$administration = (Get-SCOMManagementGroup).GetAdministration();
Start-Sleep -Milliseconds 500
$agentManagedComputerType = [Microsoft.EnterpriseManagement.Administration.AgentManagedComputer];
$genericListType = [System.Collections.Generic.List``1]
$genericList = $genericListType.MakeGenericType($agentManagedComputerType)
$agentList = new-object $genericList.FullName
$agent = Get-SCOMAgent *$AgentName*

If(!($agent))
{
$message = "No agent found with name `"$AgentName`""
$message
exit
}

Start-Sleep -S 5
$agentList.Add($agent);

$genericReadOnlyCollectionType = [System.Collections.ObjectModel.ReadOnlyCollection``1]
$genericReadOnlyCollection = $genericReadOnlyCollectionType.MakeGenericType($agentManagedComputerType)
$agentReadOnlyCollection = new-object $genericReadOnlyCollection.FullName @(,$agentList);    
$administration.DeleteAgentManagedComputers($agentReadOnlyCollection);
}

Remove-SCOMAgent -MServer MyMS -AgentName serv1 -User Myaccount -Domain MyDomain



 

Supervision - Zabbix - Script - Utilisation des Regex pour rechercher des données numeriques dans une chaine de caractère

Le cas présenté ici, peut être indépendant de l'outil de monitoring. Il s'agit de devoir alerter et donc définir des seuil sur une donnée qui est présenté par défaut sous forme de texte.

Pour être un peu plus clair, dans le contexte, un équipement snmp renvoi l'information d'utilisation de son file system sous la forme suivante:

"/tmp=0, /var=26, /var/log=0, /=22"

il s'agit en effet de la valeur de l'OID atpDiskUsage dans la MIB ATP-SNMP-MIB (Symantec Advanced Threat Protection)

Pour pouvoir traiter les valeurs d'utilisation du file system comme des nombre, les expressions régulières sont d'un grand secours.

Supposons que l'on veuille alerter sur le fait que "/var" soit au dessus de 80.

Dans ce cas l'expression proposé est la suivante régulière sera la suivante:

^.*(\/var=)[8-9][0-9]|100,.*$

Explication --> ^ : début de chaine

                      .*(\/var=) : n caractère n fois, suivi de "/var="  (on doit échapper le caractère '/' avec '\')

                      [8-9][0-9] : le range de chiffre est de 80 a 99

                      |100 : ou bien sur 100.

                       ,.* : la virgule présente, suivi de n caractère n fois

                       $: fin de chaine

Ce qui, pour nos 4 points de montage du file system donnera:

^.*(\/var=)[8-9][0-9]|100,.*$

^.*(\/tmp=)[8-9][0-9]|100,.*$

^.*(\/var\/log=)[8-9][0-9]|100,.*$

^.*(\/=)[8-9][0-9]|100.*$

Dans le contexte de Zabbix, Les expressions régulières ci-dessus ont donc été intégrées chacune dans un trigger utilisant la fonction "regexp() - Regular expression matching last value in period (1- match, 0 - not match)".

Indépendamment de l'outil de supervision, n'hésitez pas a user et abuser d'un outil de construction de regex tel que https://regex101.com/

 

SCOM - SCRIPT - Fonction powershell pour lister toutes les instances d'une machine

Le script ci-dessous intègre la requête SQL de récupération des instances d'une machine, sous forme de fonction.

All_InstancesForOneAgent_vSQLQuery.ps1

 

# SCRIPT THAT QUERY SCOM DATABASE TO GET ALL INSTANCES OF ALL CLASSES FOR A GIVEN COMPUTER ($TargetAgent)
# $TargetComputer must be Short name of computer because we look for this specific string in name, displayname and path of instances.

param(
#Short name of computer
$TargetComputer = "MyComputer"
)


# Function  Invoke-InstancesFromSQL

Function Invoke-InstancesFromSQL
                            {
                                param(
                                    [string]$dataSource = "MyScomSQLServer\OPSMGR",
                                    [string]$database = "OperationsManager",
                                    [string]$TargetComputer,
                                    [string]$sqlCommand = 
                                            $("Use $Database
                                                SELECT 
                                                   MTV.DisplayName as ClassName
	                                               ,MEGV.Path as Instance_Path
	                                               ,MEGV.Id as Instance_Id
	                                               ,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
                                            ")
                                           )


                                    $connectionString = "Data Source=$dataSource; " +
                                    "Integrated Security=SSPI; " +
                                    "Initial Catalog=$database"

                                $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
                                $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
                                
                                try
                                {
                                $connection.Open()
                                }
                                catch
                                {
                                write-host -F Red $("Error during sql connection - check the credentials used").ToUpper()
                                exit 1
                                }

                                $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
                                $dataset = New-Object System.Data.DataSet
                                $adapter.Fill($dataSet) | Out-Null

                                $connection.Close()
                                $dataSet.Tables

                              }


[array]$table = Invoke-InstancesFromSQL -TargetComputer $TargetComputer


write-host "`nCOMPUTER: $TargetComputer"

Write-Host "`nNb Of Objects:"$table.count""

$table | ft -Property ClassName,Instance_Path,Entity_DisplayName,Entity_Name,Entity_FullName,HealthState,Is_Available,In_MaintenanceMode,Start_Of_Maintenance,End_Of_Maintenance,Maintenance_RootCause,Maintenance_Reason



# TO FILTER ON SPECIFIC CLASS
# $table | Where-Object {$_.CLASS_NAME -in ('Windows computer','Health Service Watcher','VMware vSphere Host','VMWare Virtual Machine','HPE ProLiant Server')} | ft -AutoSize



# SIMULATE START OF MAINTENANCE MODE FOR THE INSTANCES
#$table | foreach {Get-SCOMClassInstance -id $_.instance_id} | foreach {start-SCOMMaintenanceMode -instance $_ -WhatIf -EndTime $((Get-Date).AddHours(1))}

 

 

 

 

 

 

Script Powershell pour Zabbix - Gerer les items d'un host

L'exemple ci-dessous utilise le module PowerShell PSBBIX qui encapsule les commandes de l'API Zabbix.

Parmi les nombreuses commandes, pour exemple, le script propose de désactiver ou activer les items (points de supervision) d'un host spécifique.

N'hésitez pas pour plus de détails a aller voir le github de PSBBIX (https://github.com/yubu/psbbix-zabbix-api)

ZabbixManageHostItems.ps1

#########################################################
### ENABLE/DISABLED ALL SPECIFIC ITEMS FOR A ZABBIX HOST
### Params:
### $ZabbixSrv: Target Zabbix Server
### $TargetHost: Target host to treat
### $ItemPattern: Pattern of Items to check
### $Action: Disable or Enable
#########################################################


Param(
[Parameter(Mandatory=$true)] $ZabbixSrv,
[Parameter(Mandatory=$true)] $TargetHost,
[Parameter(Mandatory=$true)] $ItemPattern,
[Parameter(Mandatory=$true)] $Action
)


if ($PSBoundParameters.Count -lt 4)
    {
    write-host -F Red "Too few arguments!"
    write-host -F Yellow "ZabbixSrv: Target Zabbix Server`nTargetHost: Target host to treat`nItemPattern: Pattern of Items to check`nAction: Disable or Enable"
    exit 1
    } 

switch($Action)
{
"disable" {$status = 1}
"enable" {$status = 0}
default {Write-Host -F Red "Possible value for Action is 'enable' or 'disable' - END OF SCRIPT" ; exit 0}
}



# Check if psbbix module is loaded
if (!(get-module psbbix))
    {
    write-host -F Yellow "psbbix module is not loaded...search module to load it..."
    $modulepath = $env:PSModulePath -split ‘;’ | foreach {Get-ChildItem -Recurse -Path $_ | Where-Object {$_.name -eq "psbbix.psm1"}}
    if (!$modulepath)
        {
        write-host -F Yellow "unable to find psbbix.psm1...get it on https://github.com/yubu/psbbix-zabbix-api"
        exit 1
        }
    Else
        {
            try
            {
            Import-Module $($modulepath | select -Unique).fullname
            }
            catch
            {
            write-host -F error "Error during import of psbbix module"
            exit 1
            }
        }
    Write-Host -F Green "...Module loaded"

    }


$cred = Get-Credential -Credential "myaccount"


# Connect to zabbix server
try
{
Connect-Zabbix $ZabbixSrv -PSCredential $cred
}
catch
{
Write-Host -F Red "Error during connection to $ZabbixSrv"
exit 1
}


# Get all items that we want to disable
$items = Get-ZabbixItem -hostid $(Get-ZabbixHost -HostName $TargetHost).hostid | Where-Object {$_.name -like $ItemPattern}

if ($items.Count -eq 0)
    {
    Write-Host -F Yellow "No items found with '$ItemPattern' pattern for $TargetHost host"
    }

Else
    {
        try
        {
        $items | foreach {Set-ZabbixItem -itemid $_.itemid -status $status}
        }
        catch
        {
        write-host -F Red "Error during modification of Items status"
        exit 1
        }

    }


Write-host "`nFollowing items have been treated for $TargetHost`:`n"
$items = Get-ZabbixItem -hostid $(Get-ZabbixHost -HostName $TargetHost).hostid | Where-Object {$_.name -like $ItemPattern}
$items | foreach {$_.name +" -- "+  $(switch($_.status){0{"enabled"}1{"disabled"}})}


 

Script Powershell - Fonction de test de ports reseaux

Ci-dessous une fonction dont le besoin est récurrent, pour tester la disponibilité d'un ou plusieurs ports réseaux sur une ou plusieurs machines.

 test-port.ps1 (10,05 kb)

# TEST-PORT
<#
.SYNOPSIS
         CHECK AVAILABILITY OF NETWORK PORT CONNECTION ON REMOTE COMPUTERS
.OUTPUTS 
         Return Open Status and details
.PARAMETER 
        $computer: One or more target computers
        $port: One or more port to test
        $TCPtimeout: Timeout for TCP, in ms
        $UDPtimeout: Timeout for UDP, in ms
        $TCP: Precise TCP connection
        $UDP: Precise UDP connection
                 

.USAGE:
#Test-Port -computer (get-content .\servers.txt) -port 3389
#Test-Port -computer (get-content .\servers.txt) -port 80,443,23000,17990 -TCP -TCPtimeout 1000
#Test-Port -computer myserver -port 8080,8443,10443,3389,443,80,22 -TCP -UDP
#>



function Test-Port{  




 
[cmdletbinding(  
    DefaultParameterSetName = '',  
    ConfirmImpact = 'low'  
)]  
    Param(  
        [Parameter(  
            Mandatory = $True,  
            Position = 0,  
            ParameterSetName = '',  
            ValueFromPipeline = $True)]  
            [array]$computer,  
        [Parameter(  
            Position = 1,  
            Mandatory = $True,  
            ParameterSetName = '')]  
            [array]$port,  
        [Parameter(  
            Mandatory = $False,  
            ParameterSetName = '')]  
            [int]$TCPtimeout=500,  
        [Parameter(  
            Mandatory = $False,  
            ParameterSetName = '')]  
            [int]$UDPtimeout=500,             
        [Parameter(  
            Mandatory = $False,  
            ParameterSetName = '')]  
            [switch]$TCP,  
        [Parameter(  
            Mandatory = $False,  
            ParameterSetName = '')]  
            [switch]$UDP                                    
        )  
    Begin {  
        If (!$tcp -AND !$udp) {$tcp = $True}  
        
        $ErrorActionPreference = "SilentlyContinue"  
        $report = @()  
    }  
    Process {     
        ForEach ($c in $computer) {  
            ForEach ($p in $port) {  
                If ($tcp) {    
                    #Create temporary holder   
                    $temp = "" | Select hostname,Server, Port, TypePort, Open, Notes  
                    #Create object for connecting to port on computer  
                    $tcpobject = new-Object system.Net.Sockets.TcpClient  
                    #Connect to remote machine's port                
                    $connect = $tcpobject.BeginConnect($c,$p,$null,$null)  
                    #Configure a timeout before quitting  
                    $wait = $connect.AsyncWaitHandle.WaitOne($TCPtimeout,$false)  
                    #If timeout  
                    If(!$wait) {  
                        #Close connection  
                        $tcpobject.Close()  
                        Write-Verbose "Connection Timeout"  
                        #Build report  
                        $temp.hostname= [System.Net.dns]::Resolve($c).hostname
                        $temp.Server = $c  
                        $temp.Port = $p  
                        $temp.TypePort = "TCP"  
                        $temp.Open = "False"  
                        $temp.Notes = "Connection to Port Timed Out"  
                    } Else {  
                        $error.Clear()  
                        $tcpobject.EndConnect($connect) | out-Null  
                        #If error  
                        If($error[0]){  
                            #Begin making error more readable in report  
                            [string]$string = ($error[0].exception).message  
                            $message = (($string.split(":")[1]).replace('"',"")).TrimStart()  
                            $failed = $true  
                        }  
                        #Close connection      
                        $tcpobject.Close()  
                        #If unable to query port to due failure  
                        If($failed){  
                            #Build report 
                            $temp.hostname= [System.Net.dns]::Resolve($c).hostname 
                            $temp.Server = $c  
                            $temp.Port = $p  
                            $temp.TypePort = "TCP"  
                            $temp.Open = "False"  
                            $temp.Notes = "$message"  
                        } Else{  
                            #Build report
                            $temp.hostname= [System.Net.dns]::Resolve($c).hostname  
                            $temp.Server = $c  
                            $temp.Port = $p  
                            $temp.TypePort = "TCP"  
                            $temp.Open = "True"    
                            $temp.Notes = ""  
                        }  
                    }     
                    #Reset failed value  
                    $failed = $Null      
                    #Merge temp array with report              
                    $report += $temp  
                }      
                If ($udp) {  
                    #Create temporary holder   
                    $temp = "" | Select Server, Port, TypePort, Open, Notes                                     
                    #Create object for connecting to port on computer  
                    $udpobject = new-Object system.Net.Sockets.Udpclient
                    #Set a timeout on receiving message 
                    $udpobject.client.ReceiveTimeout = $UDPTimeout 
                    #Connect to remote machine's port                
                    Write-Verbose "Making UDP connection to remote server" 
                    $udpobject.Connect("$c",$p) 
                    #Sends a message to the host to which you have connected. 
                    Write-Verbose "Sending message to remote host" 
                    $a = new-object system.text.asciiencoding 
                    $byte = $a.GetBytes("$(Get-Date)") 
                    [void]$udpobject.Send($byte,$byte.length) 
                    #IPEndPoint object will allow us to read datagrams sent from any source.  
                    Write-Verbose "Creating remote endpoint" 
                    $remoteendpoint = New-Object system.net.ipendpoint([system.net.ipaddress]::Any,0) 
                    Try { 
                        #Blocks until a message returns on this socket from a remote host. 
                        Write-Verbose "Waiting for message return" 
                        $receivebytes = $udpobject.Receive([ref]$remoteendpoint) 
                        [string]$returndata = $a.GetString($receivebytes)
                        If ($returndata) {
                           Write-Verbose "Connection Successful"  
                            #Build report
                            $temp.hostname= [System.Net.dns]::Resolve($c).hostname  
                            $temp.Server = $c  
                            $temp.Port = $p  
                            $temp.TypePort = "UDP"  
                            $temp.Open = "True"  
                            $temp.Notes = $returndata   
                            $udpobject.close()   
                        }                       
                    } Catch { 
                        If ($Error[0].ToString() -match "\bRespond after a period of time\b") { 
                            #Close connection  
                            $udpobject.Close()  
                            #Make sure that the host is online and not a false positive that it is open 
                            If (Test-Connection -comp $c -count 1 -quiet) { 
                                Write-Verbose "Connection Open"  
                                #Build report
                                $temp.hostname= [System.Net.dns]::Resolve($c).hostname  
                                $temp.Server = $c  
                                $temp.Port = $p  
                                $temp.TypePort = "UDP"  
                                $temp.Open = "True"  
                                $temp.Notes = "" 
                            } Else { 
                                <# 
                                It is possible that the host is not online or that the host is online,  
                                but ICMP is blocked by a firewall and this port is actually open. 
                                #> 
                                Write-Verbose "Host maybe unavailable"  
                                #Build report
                                $temp.hostname= [System.Net.dns]::Resolve($c).hostname  
                                $temp.Server = $c  
                                $temp.Port = $p  
                                $temp.TypePort = "UDP"  
                                $temp.Open = "False"  
                                $temp.Notes = "Unable to verify if port is open or if host is unavailable."                                 
                            }                         
                        } ElseIf ($Error[0].ToString() -match "forcibly closed by the remote host" ) { 
                            #Close connection  
                            $udpobject.Close()  
                            Write-Verbose "Connection Timeout"  
                            #Build report 
                            $temp.hostname= [System.Net.dns]::Resolve($c).hostname 
                            $temp.Server = $c  
                            $temp.Port = $p  
                            $temp.TypePort = "UDP"  
                            $temp.Open = "False"  
                            $temp.Notes = "Connection to Port Timed Out"                         
                        } Else {                      
                            $udpobject.close() 
                        } 
                    }     
                    #Merge temp array with report              
                    $report += $temp 
					$temp
                }                                  
            }  
        }                  
    }  
    End {  
        #Generate Report  
        $report |ft -AutoSize 
    }
}





 

PowerShell/SQL–Script générant un .csv contenant le résultat d’une requête

Introduction

Le script suivant permet :

  • de récupérer le résultat d’une requête SQL dans un fichier .csv,
  • de renseigner les actions réalisées dans l’EventViewer,
  • de retourner un code d’erreur (0 ou 1) suite à l’exécution du script.

Prérequis

  • Powershell
  • SQL

Présentation du script

Variables :

[string] $server = "localhost" : Serveur SQL
[string] $database = "base" : Base SQL
[string] $query = "SELCT * FROM Table" : Requête ou procédure stockée
[string] $extractFilePath = "C:\test.csv" : Emplacemnt de génération du fichier .csv
[string] $delimiter = ";" : Délimiteur à utiliser pour la génération du fichier .csv
[int] $skipline = 1 : Nombre de ligne à tronquer (utile si l’on souhaite retirer la ligne d’entête.

Script

 

# Variables d'entrée
param
    (
    [string] $server = "localhost", 
    [string] $database = "base",
    [string] $query = "SELECT * FROM Table",
    [string] $extractFilePath = "C:\test.csv",
    [string] $delimiter = ";",
    [int] $skipline = 1

    )

#MAIN
function main
{

#Variables calculées
$full_date = Get-Date;
$custom_date = $full_date.ToString("yyyyMMdd");
$DataSet = New-Object System.Data.DataSet;
$eventsource ="CsvFromSQL_Powershell";
$eventlogname = "Application";
[int] $errorcode = 1;


#Génération de la source d'evenements si inexistante
if ([System.Diagnostics.EventLog]::SourceExists($eventsource) -eq $false) 
    {
        #write-host "Creating event source $eventsource on event log $eventlogname"
        [System.Diagnostics.EventLog]::CreateEventSource($eventsource, $eventlogname)
        #write-host -foregroundcolor green "Event source $eventsource created"
    }
        else
    {
        #write-host -foregroundcolor yellow "Warning: Event source $eventsource already exists. Cannot create this source on Event log $eventlogname"
    }

<#
$logFileExists = Get-EventLog -list | Where-Object {$_.logdisplayname -eq $eventlogname} 
if (! $logFileExists) 
    {
        New-EventLog -LogName $eventlogname -Source $eventsource
    }
#>

#Execution des fonctions et récupération du code d'erreur
$errorcode = Test-SQLConn -_server $server -_database $database;
$errorcode = sql_to_dataset -_server $server -_database $database -_query $query -_dataset $DataSet;
$errorcode = dataset_to_csv -_dataset $DataSet -_extractFilePath $extractFilePath;
return $errorcode;
}

#Test connection SQL
Function Test-SQLConn ($_server, $_database)
{
    $errorcode=0;
    $connectionString = "Data Source=$_server;Integrated Security=true;Initial Catalog=$_database;Connect Timeout=3;";
    $sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString;
    trap
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "Cannot connect to server $_server or database $_database.";
        $errorcode=1;
        Write-Host $errorcode;
        exit
        }
    $sqlConn.Open()
    if ($sqlConn.State -eq 'Open')
        {
        $sqlConn.Close();
        Start-Sleep -s 1;
        Write-EventLog  -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "Connected to server $_server on database $_database.";
        }
}

#Rempli un dataset à partir d'une requête SQL
function sql_to_dataset
    {
    param($_server, $_database, $_query, $_dataset);
    $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};";
    $connectionString = [string]::Format($connectionTemplate, $_server, $_database);
    $errorcode=0;
    try
        {
        $connection = New-Object System.Data.SqlClient.SqlConnection -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL connection has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL connection. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }

    $connection.ConnectionString = $connectionString;

    try
        {
        $command = New-Object System.Data.SqlClient.SqlCommand -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL command has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL command. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }
    
    $command.CommandText = $_query;
    $command.Connection = $connection;

    try
        {
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL DataMapper has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL DataMapper. Error Message : $_.Exception.Message"; 
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }
    
    $SqlAdapter.SelectCommand = $command;

    try
        {
        $SqlAdapter.Fill($_dataSet) | Out-Null -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The Dataset has been filled successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when filling the Dataset. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }

    $connection.Close();
    return $errorcode;
    }

#Rempli un csv avec un dataset
function dataset_to_csv
{
param($_dataset, $_extractFilePath);
$errorcode=0;
    try
        {
        $_dataSet.Tables[0] | ConvertTo-Csv -NoTypeInformation -Delimiter $delimiter | select -Skip $skipline  | Set-Content $_extractFilePath -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The CSV file has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when generating the CSV file. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }
        return $errorcode;
}

main