$returnValue = sqlDiscoveryAvailabilityGroup ${computer_system.primaryHostname};

$returnValue;



sqlDiscoveryAvailabilityGroup ${computer_system.primaryHostname};
function sqlDiscoveryAvailabilityGroup ([string] $myInstanceName)
{
####################################################################################################################
# 1. Function Name: sqlDiscoveryAvailabilityGroup
#    Description: Retrieves Always On Availability Group properties from SQL Server instance
#    Input Parameter(s): 1. [string] $myInstancName: The SQL Server instane in which you wish to retrieve Always On properties from
#    Output/Return: Either a Hash table or JSON depending upon $returnType variable value
#############################

   ######################
   # Load SMO Assemblies
   ######################
   [reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')|out-null

   ###########################
   # Local Function Variables
   ###########################
   $returnType = 'HASH'			# HASH|JSON - The output parameter
   $hashReturnTable = @{}
   $countAvailabilityGroup = 0
   $countAvailabilityGroupReplica = 0
   $verboseLogging = 0

   try
   {
      $objSmoSqlInstance = new-object ('Microsoft.SqlServer.Management.Smo.Server') $myInstanceName

      $outVarInstanceName = $objSmoSqlInstance.Name

      foreach ($classAvailabilityGroup in $objSmoSqlInstance.AvailabilityGroups)
      {
         $countAvailabilityGroup++

         ###########################################################
         # Retrieve General Always On Availability Group Properties
         ###########################################################
         $outVarAvailabilityGroupName = $classAvailabilityGroup.Name
         $outVarAvailabilityGroupPrimaryReplica = $classAvailabilityGroup.PrimaryReplicaServerName
         $outVarAvailabilityGroupLocalReplicaRole = $classAvailabilityGroup.LocalReplicaRole
         $outVarAvailabilityGroupAutomatedBackupPreference = $classAvailabilityGroup.AutomatedBackupPreference
         $outVarAvailabilityGroupClusterTypeWithDefault = $classAvailabilityGroup.ClusterTypeWithDefault

         ###########################################################
         # Retrieve Always On Listener Properties for this AG Group
         ###########################################################
         $objAvailabilityGroupListener = @($classAvailabilityGroup.AvailabilityGroupListeners)[0]

         $outVarAvailabilityGroupListenerName = $objAvailabilityGroupListener.Name;
         $outVarAvailabilityGroupListenerGuid = $objAvailabilityGroupListener.UniqueId;
         $outVarAvailabilityGroupListenerIPAddresses = $objAvailabilityGroupListener.AvailabilityGroupListenerIPAddresses;
         $outVarAvailabilityGroupListenerPortNumber = $objAvailabilityGroupListener.PortNumber;

         ##################################################################
         # Retrieve Always On Replica Properties for Local/Current Replica
         ##################################################################
         foreach ($classAvailabilityReplica in $classAvailabilityGroup.AvailabilityReplicas)
         {
            $countAvailabilityGroupReplica++

            $replicaName = $classAvailabilityReplica.Name

            ###############################################################################################
            # Iterate Over Each Replica, but Only Retrieve Always On Properties from Local/Current Replica
            ###############################################################################################
            if ($replicaName -eq $outVarInstanceName)
            {              
               $outVarAvailabilityGroupReplicaName = $replicaName
               $outVarAvailabilityGroupReplicaAvailabilityMode = $classAvailabilityReplica.AvailabilityMode
               $outVarAvailabilityGroupReplicaConnectionModeInPrimary = $classAvailabilityReplica.ConnectionModeInPrimaryRole
               $outVarAvailabilityGroupReplicaConnectionModeInSecondary = $classAvailabilityReplica.ConnectionModeInSecondaryRole
               $outVarAvailabilityGroupReplicaEndpointUrl = $classAvailabilityReplica.EndpointUrl
               $outVarAvailabilityGroupReplicaFailoverMode = $classAvailabilityReplica.FailoverMode
            }
         }

         $outVarAvailabilityGroupReplicaCount = $countAvailabilityGroupReplica

         ###########################################
         # Display Debug/Verbose Details if Enabled
         ###########################################
         if ($verboseLogging -eq 1)
         {
            write-host 'Availability Group #${countAvailabilityGroup}. ${outVarAvailabilityGroupName}   Total Replica Count: ${outVarAvailabilityGroupReplicaCount}' -foregroundcolor 'yellow'
            write-host '	LocalReplicaRole: ${outVarAvailabilityGroupLocalReplicaRole}   PrimaryReplica: ${outVarAvailabilityGroupPrimaryReplica}  BackupPreference: ${outVarAvailabilityGroupAutomatedBackupPreference}   ClusterType: ${outVarAvailabilityGroupClusterTypeWithDefault}' -foregroundcolor 'yellow'
            write-host '	Listener Name: ${outVarAvailabilityGroupListenerName} Listener Port: $outVarAvailabilityGroupListenerPortNumber} Listener IP(s): $outVarAvailabilityGroupListenerIPAddresses' -foregroundcolor 'yellow'
            write-host '	Replica Name: ${outVarAvailabilityGroupReplicaName}   Availability Mode: ${outVarAvailabilityGroupReplicaAvailabilityMode}   Failover Mode: ${outVarAvailabilityGroupReplicaFailoverMode}  Endpoint Url: ${outVarAvailabilityGroupReplicaEndpointUrl}   ConnectionModeInPrimaryRole: ${outVarAvailabilityGroupReplicaConnectionModeInPrimary}   ConnectionModeInSecondaryRole: $outVarAvailabilityGroupReplicaConnectionModeInSecondary' -foregroundcolor 'yellow'
         }
      }
 
      ##########################################
      # Determine Return Type and Return Output
      ##########################################
      $hashReturnTable['AvailabilityGroupName'] = $outVarAvailabilityGroupName
      $hashReturnTable['TotalReplicaCount'] = $outVarAvailabilityGroupReplicaCount
      $hashReturnTable['LocalReplicaRole'] = $outVarAvailabilityGroupLocalReplicaRole
      $hashReturnTable['PrimaryReplica'] = $outVarAvailabilityGroupPrimaryReplica
      $hashReturnTable['BackupPreference'] = $outVarAvailabilityGroupAutomatedBackupPreference
      $hashReturnTable['PrimaryReplica'] = $outVarAvailabilityGroupPrimaryReplica
      $hashReturnTable['ClusterType'] = $outVarAvailabilityGroupClusterTypeWithDefault
      $hashReturnTable['ListenerName'] = $outVarAvailabilityGroupListenerName
      $hashReturnTable['ListenerPort'] = $outVarAvailabilityGroupListenerPortNumber
      $hashReturnTable['ListenerIP'] = $outVarAvailabilityGroupListenerIPAddresses
      $hashReturnTable['ReplicaName'] = $outVarAvailabilityGroupReplicaName
      $hashReturnTable['AvailabilityMode'] = $outVarAvailabilityGroupReplicaAvailabilityMode
      $hashReturnTable['FailoverMode'] = $outVarAvailabilityGroupReplicaFailoverMode
      $hashReturnTable['EndpointUrl'] = $outVarAvailabilityGroupReplicaEndpointUrl
      $hashReturnTable['ConnectionModeInPrimaryRole'] = $outVarAvailabilityGroupReplicaConnectionModeInPrimary
      $hashReturnTable['ConnectionModeInSecondaryRole'] = $outVarAvailabilityGroupReplicaConnectionModeInSecondary
 
      ##########################################
      # Return Hash Table with Group Properties
      ##########################################
      if ($countAvailabilityGroup -gt 0 -And $returnType -eq 'HASH')
      {
         return $hashReturnTable
      }
      elseif ($countAvailabilityGroup -gt 0 -And $returnType -eq 'JSON')
      {
         ####################################
         # Return JSON with Group Properties
         ####################################
         $jsonReturn = $hashReturnTable | ConvertTo-Json

         return $jsonReturn
      }
      else
      {
         return 0
      }
   }
   catch
   {
      ################################################################################################
      # Exception - An Error Was Encountered While Retrieving Always On Availability Group Properties
      ################################################################################################
      $ErrorMessage = $_.Exception.Message
      return -1
   }
}

#######
# MAIN
#######