MyWiki

$getHelp = """
********************************
* Script de supervision Oracle *
********************************

Auteur : TLE - [MGI] (2019/04/25)

Description : Script qui retourne l'état du service Oracle

Configuration :
    Le fichier de configuration se trouve par défaut dans 
	Voici un exemple de configuration
<?xml version=`"1.0`"?>
<configuration>
	<oracle>
		<!-- Configuration de l'accès Oracle -->
		<driver>E:\Oracle\12c\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll</driver>
		<user>zabbix</user>
		<password>voir KeePass</password>
		<datasource>TESTRH</datasource>
	</oracle>

	<zabbix>
		<!-- Configuration de l'accès Zabbix -->
		<server>epmo-ux-supervision.epmo.local</server>
	</zabbix>
</configuration>

Usage : 
	powershell -ExecutionPolicy Bypass -File `"C:\Program Files\Zabbix Agent 2\scripts\check_oracle.ps1`" `"taskname`" `"Information`"
		- taskname : Nom de la tâche
		- Information : Type d'information que l'on recherche (status, return, lastrun, nextrun)
		
Syntaxe :
	show_tablespaces : Liste les tablespaces et retourne le résultat en JSON
	show_tablespaces_temp : Liste les tablespaces temporaires et retourne le résultat en JSON
	show_asm_volumes : Liste les volumes ASM et retourne le résultat en JSON
    show_users : Liste les utilisateurs et retourne le résultat en JSON
	check_active : Check Intance is active and open
	rcachehit : Read Cache hit ratio
	dsksortratio : Disk sorts ratio
	activeusercount : Retourne le nombre d'utilisateur actifs
	dbsize : Affiche la taille de la base de données	
	dbfilesize : Size of all datafiles
	version : Version d'Oracle	
	uptime : Retourne l'uptime d'Oracle
	commits : User commits
	rollbacks : Userrollback
	deadlocks : Deadlocks
	redowrites : Redo Writes
	tblscans : Table scans (long tables)
	tblrowsscans : Table scan rows gotten
	indexffs : Index fast full scans
	hparsratio : Hard parse ratio
	netsent : Bytes sent via SQL*Net to client
	netresv : Bytes received via SQL*Net from client
	netroundtrips : SQL*Net roundtrips to/from client
	logonscurrent : Logons current
	lastarclog : Last archived log sequence
	lastapplarclog : Last applied archive log (at standby).Next items requires [timed_statistics = true]
	freebufwaits : Free buffer waits
	bufbusywaits : Buffer busy waits
	logswcompletion : log file switch completion
	logfilesync : Log file sync
	logprllwrite : Log file parallel write
	enqueue : Enqueue waits
	dbseqread : DB file sequential read waits
	dbscattread : DB file scattered read
	dbsnglwrite : DB file single write
	dbprllwrite : DB file parallel write
	directread : Direct path read
	directwrite : Direct path write
	latchfree : latch free
	tablespace tablespacename: Get tablespace usage
	tablespace_abs tablespacename: Get tablespace in use (percent)
	check_archive : List archive used
	asm_volume_use : Get ASM volume usage		
	asm_volume_size : Get ASM volume size
	asm_volume_free : Get ASM volume free
	query_lock : Query lock
	query_lock_list : Query lock list
	query_lock_list2 : Query lock list 2
	query_redologs : Redo logs
	query_rollbacks : Query Rollback
	query_sessions : Query Sessions
	tablespace_temp : Query temporary tablespaces
	query_sysmetrics : Query v$sysmetric parameters
	fra_use : Query the Fast Recovery Area usage
	user_status : Determines whether a user is locked or not
	user_open_cursors : Show open cursors count
		
Exemples :
	check_oracle.ps1 `"show_users`"
"""

#### Déclaration des variables
$configFile = "C:\Program Files\Zabbix Agent 2\scripts\check_oracle.xml"

#### Déclaration des fonctions
Function Convert-ToUnixDate ($PSdate) {
   $epoch = [timezone]::CurrentTimeZone.ToLocalTime([datetime]'1/1/1970')
   (New-TimeSpan -Start $epoch -End $PSdate).TotalSeconds
	}

Function getWindowsVersion {
    # Retourne la version de l'OS
    $WinVersion = gwmi win32_operatingsystem|select Version
	return $WinVersion
	}

Function oracleOpenCnx ($_driver, $_username, $_password, $_datasource) {
    # Ouvre une connexion à Oracle et retourne un object de connexion
	Add-Type -Path $_driver
	$connectionString = "User Id=$_username;Password=$_password;Data Source=$_datasource"
	$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
	$connection.open()
    return $connection
	}

Function execSQL ($_connexion, $_request) {
    # Retourne le résultat d'une requête SQL
	if ($_connexion.State -eq 'Open') {
	    try {
			$command = $_connexion.CreateCommand()
			$command.CommandText = $_request
			$result=$command.ExecuteReader()
			}
		catch {
			$result = $False
			}
	    }
	else {
		$result = $False
		}
	return $result
	}
	
Function OracleCloseCnx ($_connexion) {
	# Ferme une connexion Oracle
	if ($_connexion.State -eq 'Open') {
	    $_connexion.Close()
		}
	}

# Sondes discoveries
Function show_tablespaces ($_connexion) {
	# Liste les tablespaces et retourne le résultat en JSON
	$result = @{}
	$result["data"] = @()
	$request = execSQL $_connexion "SELECT tablespace_name FROM dba_tablespaces ORDER BY 1"
	foreach ($value in $request) {
	    $result["data"] += @{"{#TABLESPACE}" = $value.getvalue(0)}
		}	
	return $result|ConvertTo-Json
	}
	
Function show_tablespaces_temp ($_connexion) {
	# Liste les tablespaces temporaires et retourne le résultat en JSON
	$result = @{}
	$result["data"] = @()
	$request = execSQL $_connexion "SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='TEMPORARY'"
	foreach ($value in $request) {
	    $result["data"] += @{"{#TABLESPACE_TEMP}" = $value.getvalue(0)}
		}
	return $result|ConvertTo-Json
	}

Function show_asm_volumes ($_connexion) {
	# Liste les volumes ASM et retourne le résultat en JSON
	$result = @{}
	$result["data"] = @()
	$request = execSQL $_connexion "select NAME from v`$asm_diskgroup_stat ORDER BY 1"
	foreach ($value in $request) {
	    $result["data"] += @{"{#ASMVOLUME}" = $value.getvalue(0)}
		}
	return $result|ConvertTo-Json
	}
	
Function show_users ($_connexion) {
	# Liste les utilisateurs et retourne le résultat en JSON
	$result = @{}
	$result["data"] = @()
	$request = execSQL $_connexion "SELECT username FROM dba_users ORDER BY 1"
	foreach ($value in $request) {
	    $result["data"] += @{"{#DBUSER}" = $value.getvalue(0)}
		}
	return $result|ConvertTo-Json
	}

# Sondes récup informations
Function check_active ($_connexion) {
    # Check Intance is active and open
	$result = execSQL $_connexion "select to_char(case when inst_cnt > 0 then 1 else 0 end, 'FM99999999999999990') retvalue from (select count(*) inst_cnt from v`$instance where status = 'OPEN' and logins = 'ALLOWED' and database_status = 'ACTIVE')"
    return $result.getvalue(0)
    }
	
Function rcachehit ($_connexion) {
    # Read Cache hit ratio
	$result = execSQL $_connexion "SELECT to_char((1 - (phy.value - lob.value - dir.value) / ses.value) * 100, 'FM99999990.9999') retvalue FROM v`$sysstat ses, v`$sysstat lob, v`$sysstat dir, v`$sysstat phy WHERE ses.name = 'session logical reads' AND dir.name = 'physical reads direct' AND lob.name = 'physical reads direct (lob)' AND phy.name = 'physical reads'"
    return $result.getvalue(0)
    }
	
Function dsksortratio ($_connexion) {
    # Disk sorts ratio
	$result = execSQL $_connexion "SELECT to_char(d.value/(d.value + m.value)*100, 'FM99999990.9999') retvalue FROM  v`$sysstat m, v`$sysstat d WHERE m.name = 'sorts (memory)' AND d.name = 'sorts (disk)'"
    return $result.getvalue(0)
    }

Function activeusercount ($_connexion) {
    # Retourne le nombre d'utilisateur actifs
	$result = execSQL $_connexion "SELECT to_char(count(*)-1, 'FM99999999999999990') retvalue FROM v`$session WHERE USERNAME is not null AND status='ACTIVE'"
	return $result.getvalue(0)
    }
	
Function dbsize ($_connexion) {
	# Affiche la taille de la base de données
	$result = execSQL $_connexion "SELECT to_char(sum(  NVL(a.bytes - NVL(f.bytes, 0), 0)), 'FM99999999999999990') retvalue FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')"
	return $result.getvalue(0)
	}
	
Function dbfilesize ($_connexion) {
	# Size of all datafiles
	$result = execSQL $_connexion "select to_char(sum(bytes), 'FM99999999999999990') retvalue from dba_data_files"
	return $result.getvalue(0)
	}
	
Function version ($_connexion) {
	# Version d'Oracle
	$result = execSQL $_connexion "select banner from v`$version where rownum=1"
	return $result.getvalue(0)
	}
	
Function uptime ($_connexion) {
	# Retourne l'uptime d'Oracle
	$result = execSQL $_connexion "select to_char((sysdate-startup_time)*86400, 'FM99999999999999990') retvalue from v`$instance"
	return $result.getvalue(0)
	}
	
Function commits ($_connexion) {
	# User commits
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'user commits'"
	return $result.getvalue(0)
	}
	
Function rollbacks ($_connexion) {
	# Userrollback
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'user rollbacks'"
	return $result.getvalue(0)
	}
	
Function deadlocks ($_connexion) {
	# Deadlocks
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'enqueue deadlocks'"
	return $result.getvalue(0)
	}
	
Function redowrites ($_connexion) {
	# Redo Writes
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'redo writes'"
	return $result.getvalue(0)
	}
	
Function tblscans ($_connexion) {
	# Table scans (long tables)
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'table scans (long tables)'"
	return $result.getvalue(0)
	}
	
Function tblrowsscans ($_connexion) {
	# Table scan rows gotten
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'table scan rows gotten'"
	return $result.getvalue(0)
	}
	
Function indexffs ($_connexion) {
	# Index fast full scans
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'index fast full scans (full)'"
	return $result.getvalue(0)
	}
	
Function hparsratio ($_connexion) {
	# Hard parse ratio
	$result = execSQL $_connexion "SELECT to_char(h.value/t.value*100,'FM99999990.9999') retvalue FROM  v`$sysstat h, v`$sysstat t WHERE h.name = 'parse count (hard)' AND t.name = 'parse count (total)'"
	return $result.getvalue(0)
	}
	
Function netsent ($_connexion) {
	# Bytes sent via SQL*Net to client
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'bytes sent via SQL*Net to client'"
	return $result.getvalue(0)
	}
		
Function netresv ($_connexion) {
	# Bytes received via SQL*Net from client
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'bytes received via SQL*Net from client'"
	return $result.getvalue(0)
	}
	
Function netroundtrips ($_connexion) {
	# SQL*Net roundtrips to/from client
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'SQL*Net roundtrips to/from client'"
	return $result.getvalue(0)
	}
		
Function logonscurrent ($_connexion) {
	# Logons current
	$result = execSQL $_connexion "select to_char(value, 'FM99999999999999990') retvalue from v`$sysstat where name = 'logons current'"
	return $result.getvalue(0)
	}
	
Function lastarclog ($_connexion) {
	# Last archived log sequence
	$result = execSQL $_connexion "select to_char(max(SEQUENCE`#), 'FM99999999999999990') retvalue from v`$log where archived = 'YES'"
	if ($result.getvalue(0) -notlike "") {
		return $result.getvalue(0)
		}
	else {
		return 0
		}
	}
		
Function lastapplarclog ($_connexion) {
	# Last applied archive log (at standby).Next items requires [timed_statistics = true]
	$result = execSQL $_connexion "select to_char(max(lh.SEQUENCE#), 'FM99999999999999990') retvalue from v`$loghist lh, v`$archived_log al where lh.SEQUENCE`# = al.SEQUENCE`# and applied='YES'"
	return $result.getvalue(0)
	}
	
Function freebufwaits ($_connexion) {
	# Free buffer waits
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'free buffer waits'"
	return $result.getvalue(0)
	}
		
Function bufbusywaits ($_connexion) {
	# Buffer busy waits
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'buffer busy waits'"
	return $result.getvalue(0)
	}
	
Function logswcompletion ($_connexion) {
	# log file switch completion
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'log file switch completion'"
	return $result.getvalue(0)
	}
		
Function logfilesync ($_connexion) {
	# Log file sync
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'log file sync'"
	return $result.getvalue(0)
	}
	
Function logprllwrite ($_connexion) {
	# Log file parallel write
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'log file parallel write'"
	return $result.getvalue(0)
	}
		
Function enqueue ($_connexion) {
	# Enqueue waits
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'enqueue'"
	if ($result -notlike "") {
		return $result.getvalue(0)
		}
	else {
		return 0
		}
	}
	
Function dbseqread ($_connexion) {
	# DB file sequential read waits
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'db file sequential read'"
	return $result.getvalue(0)
	}
		
Function dbscattread ($_connexion) {
	# DB file scattered read
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'db file scattered read'"
	return $result.getvalue(0)
	}
	
Function dbsnglwrite ($_connexion) {
	# DB file single write
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'db file single write'"
	if ($result.getvalue(0) -notlike "") {
		return $result.getvalue(0)
		}
	else {
		return 0
		}
	}
		
Function dbprllwrite ($_connexion) {
	# DB file parallel write
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'db file parallel write'"
	return $result.getvalue(0)
	}
	
Function directread ($_connexion) {
	# Direct path read
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'direct path read'"
	return $result.getvalue(0)
	}
		
Function directwrite ($_connexion) {
	# Direct path write
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'direct path write'"
	return $result.getvalue(0)
	}
	
Function latchfree ($_connexion) {
	# latch free
	$result = execSQL $_connexion "select to_char(time_waited, 'FM99999999999999990') retvalue from v`$system_event se, v`$event_name en where se.event(+) = en.name and en.name = 'latch free'"
	return $result.getvalue(0)
	}
		
Function tablespace ($_connexion, $_tablespace) {
	# Get tablespace usage
	$result = execSQL $_connexion "SELECT tablespace_name `"TABLESPACE`", round(used_percent,8) `"USED`" FROM dba_tablespace_usage_metrics WHERE tablespace_name = '$_tablespace'"
	if ($result) {
		return [string]$result.getvalue(1)
		}
	else {
	    return 0
		}
	}
	
Function tablespace_abs ($_connexion, $_tablespace) {
	# Get tablespace in use (percent)
	$result = execSQL $_connexion "SELECT tablespace_name `"TABLESPACE`", round(used_space * 8192, 2) `"BYTES`" FROM dba_tablespace_usage_metrics  WHERE  tablespace_name = '$_tablespace'"
	if ($result) {
		return [string]$result.getvalue(1)
		}
	else {
		return 0
		}
	}
		
Function check_archive ($_connexion, $_archive) {
	# List archive used
	$result = execSQL $_connexion "select trunc((total_mb-free_mb)*100/(total_mb)) PCT from v`$asm_diskgroup_stat where name='$_archive' ORDER BY 1"
	return $result.getvalue(1)
	}
	
Function asm_volume_use ($_connexion, $_volume) {
	# Get ASM volume usage
	$result = execSQL $_connexion "select round(((TOTAL_MB-FREE_MB)/TOTAL_MB*100),2) from v`$asm_diskgroup_stat where name = '$_volume'"
	return $result.getvalue(1)
	}
		
Function asm_volume_size ($_connexion, $_volume) {
	# Get ASM volume size
	$result = execSQL $_connexion "select TOTAL_MB from v`$asm_diskgroup_stat where name = '$_volume'"
	return $result.getvalue(1)
	}
	
Function asm_volume_free ($_connexion, $_volume) {
	# Get ASM volume free
	$result = execSQL $_connexion "select FREE_MB from v`$asm_diskgroup_stat where name = '$_volume'"
	return $result.getvalue(1)
	}
		
Function query_lock ($_connexion) {
	# Query lock
	$result = execSQL $_connexion "SELECT count(*) FROM gv`$lock l WHERE  block=1"
	return $result.getvalue(0)
	}
	
Function query_lock_list ($_connexion) {
	# Query lock list
	$result = @()
	$request = execSQL $_connexion "SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode, request, type FROM V`$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V`$LOCK WHERE request>0) ORDER BY id1, request"
	foreach ($value in $request) {
	    $result += $value.getvalue(0)
		}
	$request = execSQL $_connexion "select sb.BLOCKER_SID HOLDER, sb.SID WAITER, v.USERNAME, v.CLIENT_IDENTIFIER, v.PROGRAM, v.STATUS, do.object_name, sq.SQL_TEXT from v`$session_blockers sb, v`$session v, v`$sql sq, v`$locked_object lo, dba_objects do where sb.sid = v.sid and v.SQL_ID = sq.SQL_ID and lo.SESSION_ID = v.SID and do.object_id = lo.OBJECT_ID"
	foreach ($value in $request) {
	    $result += $value.getvalue(0)
		}
	return $result
	}
		
Function query_lock_list2 ($_connexion) {
	# Query lock list 2
	$result = @()
	$request = execSQL $_connexion "SELECT 'Host '|| s1.CLIENT_IDENTIFIER || ', User ' ||s1.username || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text ||' |is blocking ' || s2.username || ' ( SID=' || s2.sid || ' ) SQL -> ' ||sqlt1.sql_text AS blocking_status FROM v`$lock l1, v`$session s1 ,v`$lock l2 ,v`$session s2 ,v`$sql sqlt1 ,v`$sql sqlt2 WHERE s1.sid =l1.sid AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2"
	foreach ($value in $request) {
	    $result += $value.getvalue(0).replace("|", "\n\t")
		}
	$request = execSQL $_connexion "select sb.BLOCKER_SID HOLDER, sb.SID WAITER, v.USERNAME, v.CLIENT_IDENTIFIER, v.PROGRAM, v.STATUS, do.object_name,sb.WAIT_EVENT_TEXT, sq.SQL_TEXT from v`$session_blockers sb, v`$session v, v`$sql sq, v`$locked_object lo, dba_objects do where sb.sid = v.sid and v.SQL_ID = sq.SQL_ID and lo.SESSION_ID = v.SID and do.object_id = lo.OBJECT_ID"
	foreach ($value in $request) {
	    $result += $value.getvalue(0)
		}
	return $result
	}
	
Function query_redologs ($_connexion) {
	# Redo logs
	$result = execSQL $_connexion "select COUNT(*) from v`$LOG WHERE STATUS='ACTIVE'"
	return $result.getvalue(0)
	}
		
Function query_rollbacks ($_connexion) {
	# Query Rollback
	$result = execSQL $_connexion "select nvl(trunc(sum(used_ublk*4096)/1024/1024),0) from gv`$transaction t,gv`$session s where ses_addr = saddr"
	return $result.getvalue(0)
	}
	
Function query_sessions ($_connexion) {
	# Query Sessions
	$result = execSQL $_connexion "select count(*) from gv`$session where username is not null and status='ACTIVE'"
	return $result.getvalue(0)
	}
		
Function tablespace_temp ($_connexion, $_tablespace) {
	# Query temporary tablespaces
	$result = execSQL $_connexion "SELECT round(((TABLESPACE_SIZE-FREE_SPACE)/TABLESPACE_SIZE)*100,2) PERCENTUAL FROM dba_temp_free_space where tablespace_name='$_tablespace'"
	return [string]$result.getvalue(0)
	}
	
Function query_sysmetrics ($_connexion, $_query) {
	# Query v$sysmetric parameters
	$result = execSQL $_connexion "select value from v`$sysmetric where METRIC_NAME ='`$_query' and rownum <=1 order by INTSIZE_CSEC"
	if ($result) {
		return $result.getvalue(1).replace('-', ' ')
		}
	else {
		return 0
		}
	}
		
Function fra_use ($_connexion) {
	# Query the Fast Recovery Area usage
	$result = execSQL $_connexion "select round((SPACE_LIMIT-(SPACE_LIMIT-SPACE_USED))/ SPACE_LIMIT*100,2) FROM V`$RECOVERY_FILE_DEST"
	return $result.getvalue(0)
	}
	
Function user_status ($_connexion, $_user) {
	# Determines whether a user is locked or not
	$result = execSQL $_connexion "SELECT account_status FROM dba_users WHERE username='$_user'"
	return $result.getvalue(0)
	}
		
Function user_open_cursors ($_connexion) {
	# Show open cursors count
	$result = execSQL $_connexion "select count(*) from v`$open_cursor where sid in (select sys_context('userenv','sid') from dual)"
	return $result.getvalue(0)
	}
	
#### Début du code
# On charge le fichier de configuration
Try {
	[xml]$config = Get-Content $configFile
	$driver = $config.configuration.oracle.driver
	$user = $config.configuration.oracle.user
	$password = $config.configuration.oracle.password
	$datasource = $config.configuration.oracle.datasource
	}
Catch {
    write-host "[Erreur] - Erreur lors du chargement du $configFile"
	write-host $getHelp
	exit 1
	}

# On se connecte à la base
Try {
    $cnxoracle = oracleOpenCnx $driver $user $password $datasource
	}
Catch {
	write-host """[Erreur] - Impossible de se connecter à la base avec les informations suivantes :
	- Driver : $driver
	- Username : $user
	- Password : $password
	- DSN : $datasource
	"""
	exit 1
	}

# Switch des arguments disponibles
switch ([string]$args[0]) {
	"show_tablespaces" {
		write-host $(show_tablespaces $cnxoracle)
		exit 0
		}
	"show_tablespaces_temp" {
		write-host $(show_tablespaces_temp $cnxoracle)
		exit 0
		}
	"show_asm_volumes" {
		write-host $(show_asm_volumes $cnxoracle)
		exit 0
		}
	"show_users" {
		write-host $(show_users $cnxoracle)
		exit 0
		}
	"check_active" {
		write-host $(check_active $cnxoracle)
		exit 0
		}
	"rcachehit" {
		write-host $(rcachehit $cnxoracle)
		exit 0
		}
	"dsksortratio" {
		write-host $(dsksortratio $cnxoracle)
		exit 0
		}
	"activeusercount" {
		write-host $(activeusercount $cnxoracle)
		exit 0
		}
	"dbsize" {
		write-host $(dbsize $cnxoracle)
		exit 0
		}
	"dbfilesize" {
		write-host $(dbfilesize $cnxoracle)
		exit 0
		}
	"version" {
		write-host $(version $cnxoracle)
		exit 0
		}
	"uptime" {
		write-host $(uptime $cnxoracle)
		exit 0
		}
	"commits" {
		write-host $(commits $cnxoracle)
		exit 0
		}
	"rollbacks" {
		write-host $(rollbacks $cnxoracle)
		exit 0
		}
	"deadlocks" {
		write-host $(deadlocks $cnxoracle)
		exit 0
		}
	"redowrites" {
		write-host $(redowrites $cnxoracle)
		exit 0
		}
	"tblscans" {
		write-host $(tblscans $cnxoracle)
		exit 0
		}
	"tblrowsscans" {
		write-host $(tblrowsscans $cnxoracle)
		exit 0
		}
	"indexffs" {
		write-host $(indexffs $cnxoracle)
		exit 0
		}
	"hparsratio" {
		write-host $(hparsratio $cnxoracle)
		exit 0
		}
	"netsent" {
		write-host $(netsent $cnxoracle)
		exit 0
		}
	"netresv" {
		write-host $(netresv $cnxoracle)
		exit 0
		}
	"netroundtrips" {
		write-host $(netroundtrips $cnxoracle)
		exit 0
		}
	"logonscurrent" {
		write-host $(logonscurrent $cnxoracle)
		exit 0
		}
	"lastarclog" {
		write-host $(lastarclog $cnxoracle)
		exit 0
		}
	"lastapplarclog" {
		write-host $(lastapplarclog $cnxoracle)
		exit 0
		}
	"freebufwaits" {
		write-host $(freebufwaits $cnxoracle)
		exit 0
		}
	"bufbusywaits" {
		write-host $(bufbusywaits $cnxoracle)
		exit 0
		}
	"logswcompletion" {
		write-host $(logswcompletion $cnxoracle)
		exit 0
		}
	"logfilesync" {
		write-host $(logfilesync $cnxoracle)
		exit 0
		}
	"logprllwrite" {
		write-host $(logprllwrite $cnxoracle)
		exit 0
		}
	"enqueue" {
		write-host $(enqueue $cnxoracle)
		exit 0
		}
	"dbseqread" {
		write-host $(dbseqread $cnxoracle)
		exit 0
		}
	"dbscattread" {
		write-host $(dbscattread $cnxoracle)
		exit 0
		}
	"dbsnglwrite" {
		write-host $(dbsnglwrite $cnxoracle)
		exit 0
		}
	"dbprllwrite" {
		write-host $(dbprllwrite $cnxoracle)
		exit 0
		}
	"directread" {
		write-host $(directread $cnxoracle)
		exit 0
		}
	"directwrite" {
		write-host $(directwrite $cnxoracle)
		exit 0
		}
	"latchfree" {
		write-host $(latchfree $cnxoracle)
		exit 0
		}
	"tablespace" {
		write-host $(tablespace $cnxoracle $args[1])
		exit 0
		}
	"tablespace_abs" {
		write-host $(tablespace_abs $cnxoracle $args[1])
		exit 0
		}
	"check_archive" {
		write-host $(check_archive $cnxoracle $args[1])
		exit 0
		}
	"asm_volume_use" {
		write-host $(asm_volume_use $cnxoracle $args[1])
		exit 0
		}
	"asm_volume_size" {
		write-host $(asm_volume_size $cnxoracle $args[1])
		exit 0
		}
	"asm_volume_free" {
		write-host $(asm_volume_free $cnxoracle $args[1])
		exit 0
		}
	"query_lock" {
		write-host $(query_lock $cnxoracle)
		exit 0
		}
	"query_lock_list" {
		write-host $(query_lock_list $cnxoracle)
		exit 0
		}
	"query_lock_list2" {
		write-host $(query_lock_list2 $cnxoracle)
		exit 0
		}
	"query_redologs" {
		write-host $(query_redologs $cnxoracle)
		exit 0
		}
	"query_rollbacks" {
		write-host $(query_rollbacks $cnxoracle)
		exit 0
		}
	"query_sessions" {
		write-host $(query_sessions $cnxoracle)
		exit 0
		}
	"tablespace_temp" {
		write-host $(tablespace_temp $cnxoracle $args[1])
		exit 0
		}
	"query_sysmetrics" {
		write-host $(query_sysmetrics $cnxoracle $args[1])
		exit 0
		}
	"fra_use" {
		write-host $(fra_use $cnxoracle)
		exit 0
		}
	"user_status" {
		write-host $(user_status $cnxoracle $args[1])
		exit 0
		}
	"user_open_cursors" {
		write-host $(user_open_cursors $cnxoracle)
		exit 0
		}
	}

OracleCloseCnx $cnxoracle

write-host $getHelp

<?xml version="1.0"?>
<configuration>
	<oracle>
		<!-- Configuration de l'accès Oracle -->
		<driver>E:\Oracle\12c\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll</driver>
		<user>zabbix</user>
		<password>BPh46jHy6Cq2MGMSn9XW</password>
		<datasource>RHTEST</datasource>
	</oracle>

	<zabbix>
		<!-- Configuration de l'accès Zabbix -->
		<server>epmo-ux-supervision.epmo.local</server>
	</zabbix>
</configuration>

$getHelp = """
*****************************************************
* Script de verification du fonctionnement de MSSQL *
*****************************************************

Auteur : TLE - [MGI] (2019/03/19)

Description : Script qui retourne les informations d'une bases MSSQL :
 - La liste des bases en format JSON
 - La taille des bases
 - Un check si la base est consultable (SELECT 2+2)

Usage : 
	powershell -ExecutionPolicy Bypass -File `"C:\Program Files\Zabbix\scripts\checkMSSQL.ps1`" `"taskname`" `"Information`"
		- taskname : Nom de la tâche
		- Information : Type d'information que l'on recherche (status, return, lastrun, nextrun)
	
Exemples :
	schedultaskstatus.ps1 `"databaseslist`"					: Liste les bases de données
	schedultaskstatus.ps1 `"dbsize`" `"IREC`"				: Retourne la taille de la base
	schedultaskstatus.ps1 `"logsize`" `"IREC`"				: Retourne la taille des logs de la base
	schedultaskstatus.ps1 `"dbcnx`" `"IREC`"				: Retourne le nombre de connexions sur la base
	schedultaskstatus.ps1 `"dbccheck`" `"IREC`"				: Retourne 1 si les requêtes SQL fonctionnenent
	schedultaskstatus.ps1 `"taskslist`"						: Liste les tâches planifiées
	schedultaskstatus.ps1 `"task`" `"Exports_Edeal`"		: Affiche l'état d'une tâche planifiées
	schedultaskstatus.ps1 `"request`" `"select`" `"IREC`"			: Retourne le nombre de requêtes SELECT
	schedultaskstatus.ps1 `"request`" `"insert`" `"IREC`"			: Retourne le nombre de requêtes INSERT
	schedultaskstatus.ps1 `"request`" `"update`" `"IREC`"			: Retourne le nombre de requêtes UPDATE
	schedultaskstatus.ps1 `"request`" `"delete`" `"IREC`"			: Retourne le nombre de requêtes DELETE
	
Sources :
	https://docs.microsoft.com/fr-fr/sql/relational-databases/system-tables/dbo-sysjobsteps-transact-sql?view=sql-server-2017
	
Modification :
    * TLE - 20190521 - Modification de la requête SQL de la fonction getTasksList pour ne pas lister les tâches non planifiées :
		- SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 1
		+ SELECT msdb.dbo.sysjobs.name FROM msdb.dbo.sysjobs, msdb.dbo.sysschedules WHERE msdb.dbo.sysjobs.enabled = 1 AND msdb.dbo.sysjobs.name = msdb.dbo.sysschedules.name AND msdb.dbo.sysschedules.enabled = 1
"""


#### Déclaration des variables



#### Déclaration des fonctions
Function Convert-ToUnixDate ($PSdate) {
   $epoch = [timezone]::CurrentTimeZone.ToLocalTime([datetime]'1/1/1970')
   (New-TimeSpan -Start $epoch -End $PSdate).TotalSeconds
	}

Function getDatabasesList {
    # Retourne la liste des bases en format json
    $databases = SQLCMD.EXE -Q "SELECT NAME FROM sys.sysdatabases"
	foreach ($line in $databases) {
		if ($line -notlike "NAME*" -And $line -notlike "-------*" -And $line -notlike "(*" -And $line.replace(' ','') -notlike "") {
			if (-not $result) {
				$result = "{`"data`":[{`"{`#DBNAME}`":`"$($line.replace(' ',''))`"}"
				}
			else {
				$result = "$($result),{`"{`#DBNAME}`":`"$($line.replace(' ',''))`"}"
				}
			}
		}
	$result = "$($result)]}"
	return $result
    }

Function getTasksList {
	# Retourne la liste des tâches en format Json
	$tasks = SQLCMD.EXE -Q "SELECT msdb.dbo.sysjobs.name FROM msdb.dbo.sysjobs, msdb.dbo.sysschedules WHERE msdb.dbo.sysjobs.enabled = 1 AND msdb.dbo.sysjobs.name = msdb.dbo.sysschedules.name AND msdb.dbo.sysschedules.enabled = 1;"
	foreach ($line in $tasks) {
		if ($line -notlike "NAME*" -And $line -notlike "-------*" -And $line -notlike "(*" -And $line.replace(' ','') -notlike "") {
			if (-not $result) {
				$result = "{`"data`":[{`"{`#TASKNAME}`":`"$($line.replace(' ',''))`"}"
				}
			else {
				$result = "$($result),{`"{`#TASKNAME}`":`"$($line.replace(' ',''))`"}"
				}
			}
		}
	$result = "$($result)]}"
	return $result
    }

Function getTasksStatus ($_taskname) {
	# Retourne l'état d'une tâche
	#0 = Échec
	#1 = a réussi
	#2 = nouvelle tentative
	#3 = annulée
	#5 = inconnu
	$result = ""
	$request = SQLCMD.EXE -Q "SELECT last_run_outcome FROM msdb.dbo.sysjobsteps WHERE job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = '$_taskname' AND enabled = 1);"
	foreach ($line in $request) {
	    if ($line -notlike "Name*" -And $line -notlike "------*" -And $line -notlike "(*" -And $line.replace(' ','') -notlike "") {
		    $result = $line.replace(" ","")
			}
		}
	return $result
	}

Function getDatabaseSize ($_database) {
	# Retourne la taille d'une base
	$result = @{}
	$request = SQLCMD.EXE -Q "SELECT Name, size FROM sys.master_files WHERE DB_NAME(database_id) = '$_database'"
	foreach ($line in $request) {
	    if ($line -notlike "Name*" -And $line -notlike "------*" -And $line -notlike "(*" -And $line.replace(' ','') -notlike "") {
		    $base = $line.split(' ')[0]
			$size = $line.replace($base, '').replace(' ','')
		    $result[$base] = $size
			}
		}
	if (-not $result[$_database]) {
		$result[$_database] = 0
		}
	return $result
	}

Function getDatabaseCnx ($_database) {
    # Retourne le nombre de connexion pour une base
	$result = @{}
	$request = SQLCMD.EXE -Q "SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections FROM sys.sysprocesses WHERE dbid > 0 AND DB_NAME(dbid) = '$_database' GROUP BY dbid, loginame"
    foreach ($line in $request) {
	    if ($line -notlike "DBName*" -And $line -notlike "------*" -And $line -notlike "(*" -And $line.replace(' ','') -notlike "") {
		    $base = $line.split(' ')[0]
			$number = $line.replace($base, '').replace(' ','')
		    $result[$base] = $number
			}
		}
	if (-not $result[$_database]) {
		$result[$_database] = 0
		}
	return $result
	}


Function countRequest ($_database, $_request) {
	# Retourne le nombre de requête de type $_request (SELECT, UPDATE, INSERT)
	#countRequest 'master' 'SELECT'
	#1
	$request = SQLCMD.EXE -Q "SELECT count(request_id) FROM sys.dm_exec_requests WHERE command like '%$_request%' AND DB_NAME(database_id) like '%$_database%';"
	foreach ($line in $request) {
	    if ($line -notlike "DBName*" -And $line -notlike "------*" -And $line -notlike "(*" -And $line.replace(' ','') -notlike "") {
		    $result = $line.replace(' ','')
			}
		}
	return $result
	}

Function checkDatabase ($_database) {
	# On lance une requête SELECT 2+2 pour valider que la base est bien attaquable
	# Si la requête fonctionne, on retourne 1 sinon, on retourne 0
    $req = SQLCMD.EXE -Q "USE $_database;SELECT 2+2;"
    if ($req -like "*4*") {
	    return 1
	    }
	else {
		return 0
		}
	}

#### Début du code

if (-not [string]$args[0]) {
	write-host $getHelp
    }
else {
	switch ([string]$args[0]) {
		"databaseslist" {
			write-host $(getDatabasesList)
			}
		"dbsize" {
			if (-not [string]$args[1]) {
				write-host "Erreur : Il manque le nom de la base de données en paramètre"
				write-host $getHelp
				}
			else {
				$result = getDatabaseSize $args[1]
				write-host $result[$args[1]]
				}
			}
		"dbcnx" {
			if (-not [string]$args[1]) {
				write-host "Erreur : Il manque le nom de la base de données en paramètre"
				write-host $getHelp
				}
			else {
				$result = getDatabaseCnx $args[1]
				write-host $result[$args[1]]
				}
			}
		"logsize" {
			if (-not [string]$args[1]) {
				write-host "Erreur : Il manque le nom de la base de données en paramètre"
				write-host $getHelp
				}
			else {
				$result = getDatabaseSize $args[1]
				write-host $result["$($args[1])_log"]
				}
			}
		"dbcheck" {
			if (-not [string]$args[1]) {
				write-host "Erreur : Il manque le nom de la base de données en paramètre"
				write-host $getHelp
				}
			else {
				$result = checkDatabase $args[1]
				write-host $result
				}
			}
		"taskslist" {
			write-host $(getTasksList)
			}
		"task" {
			if (-not [string]$args[1]) {
				write-host "Erreur : Il manque le nom de la tâche en paramètre"
				write-host $getHelp
				}
			else {
				$result = getTasksStatus $args[1]
				write-host $result
				}
			}
		"request" {
			if (-not [string]$args[1] -And -not [string]$args[2]) {
				write-host "Erreur : Il faut indiquer le nom de la base ainsi que de la requête en paramètre"
				write-host $getHelp
				}
			else {
				$result = countRequest $args[2] $args[1].ToUpper()
				write-host $result
				}
			}
		}
	}

# Script: DiscoverSchelduledTasks
# Author: Romain Si
# Revision: Isaac de Moraes
# This script is intended for use with Zabbix > 3.x
#
#
# Add to Zabbix Agent
#   UserParameter=TaskSchedulerMonitoring[*],powershell -NoProfile -ExecutionPolicy Bypass -File "C:\Program Files\Zabbix Agent\DiscoverScheduledTasks.ps1" "$1" "$2"
#
## Modifier la variable $path pour indiquer les sous dossiers de Tâches Planifiées à traiter sous la forme "\nomDossier\","\nomdossier2\sousdossier\" voir (Get-ScheduledTask -TaskPath )
## Change the $path variable to indicate the Scheduled Tasks subfolder to be processed as "\nameFolder\","\nameFolder2\subfolder\" see (Get-ScheduledTask -TaskPath )



$path = "\"




Function Convert-ToUnixDate ($PSdate) {
   $epoch = [timezone]::CurrentTimeZone.ToLocalTime([datetime]'1/1/1970')
   (New-TimeSpan -Start $epoch -End $PSdate).TotalSeconds
}

$ITEM = [string]$args[0]
$ID = [string]$args[1]

switch ($ITEM) {
  "DiscoverTasks" {
$apptasks = Get-ScheduledTask -TaskPath $path | where {$_.state -like "Ready" -and "Running"}
$apptasksok1 = $apptasks.TaskName
$apptasksok = $apptasksok1.replace('â','â').replace('à','à').replace('ç','ç').replace('é','é').replace('è','è').replace('ê','ê')
$idx = 1
write-host "{"
write-host " `"data`":[`n"
foreach ($currentapptasks in $apptasksok)
{
    if ($idx -lt $apptasksok.count)
    {
     
        $line= "{ `"{#APPTASKS}`" : `"" + $currentapptasks + "`" },"
        write-host $line
    }
    elseif ($idx -ge $apptasksok.count)
    {
    $line= "{ `"{#APPTASKS}`" : `"" + $currentapptasks + "`" }"
    write-host $line
    }
    $idx++;
} 
write-host
write-host " ]"
write-host "}"}}


switch ($ITEM) {
  "TaskLastResult" {
[string] $name = $ID
$name1 = $name.replace('â','â').replace('à','à').replace('ç','ç').replace('é','é').replace('è','è').replace('ê','ê')
$pathtask = Get-ScheduledTask -TaskPath "*" -TaskName "$name1"
$pathtask1 = $pathtask.Taskpath
$taskResult = Get-ScheduledTaskInfo -TaskPath "$pathtask1" -TaskName "$name1"
Write-Output ($taskResult.LastTaskResult)
}}

switch ($ITEM) {
  "TaskLastRunTime" {
[string] $name = $ID
$name1 = $name.replace('â','â').replace('à','à').replace('ç','ç').replace('é','é').replace('è','è').replace('ê','ê')
$pathtask = Get-ScheduledTask -TaskPath "*" -TaskName "$name1"
$pathtask1 = $pathtask.Taskpath
$taskResult = Get-ScheduledTaskInfo -TaskPath "$pathtask1" -TaskName "$name1"
$taskResult1 = $taskResult.LastRunTime
$date = get-date -date "01/01/1970"
$taskResult2 = Convert-ToUnixDate($taskResult1)
Write-Output ($taskResult2)
}}

switch ($ITEM) {
  "TaskNextRunTime" {
[string] $name = $ID
$name1 = $name.replace('â','â').replace('à','à').replace('ç','ç').replace('é','é').replace('è','è').replace('ê','ê')
$pathtask = Get-ScheduledTask -TaskPath "*" -TaskName "$name1"
$pathtask1 = $pathtask.Taskpath
$taskResult = Get-ScheduledTaskInfo -TaskPath "$pathtask1" -TaskName "$name1"
$taskResult1 = $taskResult.NextRunTime
$date = get-date -date "01/01/1970"
$taskResult2 = Convert-ToUnixDate($taskResult1)
Write-Output ($taskResult2)
}}

switch ($ITEM) {
  "TaskState" {
[string] $name = $ID
$name1 = $name.replace('â','â').replace('à','à').replace('ç','ç').replace('é','é').replace('è','è').replace('ê','ê')
$pathtask = Get-ScheduledTask -TaskPath "*" -TaskName "$name1"
$pathtask1 = $pathtask.State
Write-Output ($pathtask1)
}}

$getHelp = """
*************************************************
* Script indiquant l'état d'une tache planifiée *
*************************************************

Auteur : TLE - [MGI] (2019/03/19)

Description : Script qui retourne l'état d'une tache planifiée :
 - L'état de la tache (démarrée, en cours, ...)
 - Le retour du dernier lancement (0: OK, X: NOK)
 - La date du dernier lancement
 - La date du prochain lancement

Usage : 
	powershell -ExecutionPolicy Bypass -File `"C:\Program Files\Zabbix\scripts\SchedulTaskStatus.ps1`" `"taskname`" `"Information`"
		- taskname : Nom de la tâche
		- Information : Type d'information que l'on recherche (status, return, lastrun, nextrun)
	
Exemples :
	schedultaskstatus.ps1 `"Exports_Edeal`" `"status`"
	schedultaskstatus.ps1 `"Exports_Edeal`" `"return`"
	schedultaskstatus.ps1 `"Exports_Edeal`" `"lastrun`"
	schedultaskstatus.ps1 `"Exports_Edeal`" `"nextrun`"

"""


#### Déclaration des variables
$TaskName = [string]$args[0]
$TaskRequest = [string]$args[1]
$TaskInfo = @{}

if ($TaskName -eq "") {
    echo $getHelp
	exit
	}


#### Déclaration des fonctions
Function Convert-ToUnixDate ($PSdate) {
   $epoch = [timezone]::CurrentTimeZone.ToLocalTime([datetime]'1/1/1970')
   (New-TimeSpan -Start $epoch -End $PSdate).TotalSeconds
	}

Function getWindowsVersion {
    # Retourne la version de l'OS
    $WinVersion = gwmi win32_operatingsystem|select Version
	return $WinVersion
	}

Function getTaskInfo ($_TaskName) {
	# Retourne les informations d'une tâche
	$cmdResult = schtasks /tn \$_TaskName /query /fo LIST /v
	foreach ($line in $cmdResult) {
		if ($line -like "Statut:*") {
		    $status = $line.split(':')[1]
			if ($status -like "*Prêt") {
				$TaskInfo["status"] = 0
				}
			else {
				$TaskInfo["status"] = 1
				}
			}
		if ($line -like "Prochaine exécution:*") {
			$date = $line.Replace("Prochaine","").replace("exécution","").replace("  ","").replace(" : ","")
		    $TaskInfo["nextrun"] = Convert-ToUnixDate($date)
			}
		if ($line -like "Dernier résultat:*") {
		    $TaskInfo["result"] = $line.split(':')[1].Replace(" ","")
			}
		if ($line -like "*derni*cution:*") {
			$date = $line.replace("Heure de la dernière exécution:","").replace("  ","")
		    $TaskInfo["lastrun"] = Convert-ToUnixDate($date)
			}
		}
	}

#### Début du code
getTaskInfo($TaskName)

Try {
	echo $TaskInfo[$TaskRequest]
	}
Catch {
    echo "Erreur"
	echo $getHelp
	exit
	}

Import-Module WebAdministration
$apppool = Get-ChildItem -Path IIS:\Apppools -Name
$idx = 1
write-host "{"
write-host " `"data`":[`n"
foreach ($currentapppool in $apppool)
{
    if ($idx -lt $apppool.count)
    {
     
        $line= "{ `"{#APPPOOL}`" : `"" + $currentapppool + "`" },"
        write-host $line
    }
    elseif ($idx -ge $apppool.count)
    {
    $line= "{ `"{#APPPOOL}`" : `"" + $currentapppool + "`" }"
    write-host $line
    }
    $idx++;
}
write-host
write-host " ]"
write-host "}"

Import-Module WebAdministration
$sites = Get-ChildItem -Path IIS:\Sites -Name
$idx = 1
write-host "{"
write-host " `"data`":[`n"
foreach ($currentsite in $sites)
{
    if ($idx -lt $sites.count)
    {
     
        $line= "{ `"{#SITE}`" : `"" + $currentsite + "`" },"
        write-host $line
    }
    elseif ($idx -ge $site.count)
    {
    $line= "{ `"{#SITE}`" : `"" + $currentsite + "`" }"
    write-host $line
    }
    $idx++;
}
write-host
write-host " ]"
write-host "}"

param ([string] $name = 0)
Import-Module WebAdministration
$siteState = Get-WebsiteState -name "$name"
Write-Output ($siteState.value)