Home
  •  

AK80677

Monitor_AK80677 (UnitMonitor)

SQL Server installer cache files are missing

Knowledge Base article:

External

http://go.microsoft.com/fwlink/?LinkId=199776

Element properties:

TargetMicrosoft.KnowledgeServices.Windows.OperatingSystem
Parent MonitorSystem.Health.ConfigurationState
CategoryAlert
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityHigh
Alert Auto ResolveTrue
Monitor TypeMicrosoft.KnowledgeServices.Library.PowerShellMonitorEx
RemotableTrue
AccessibilityPublic
Alert Message
SQL Server installer cache files are missing
<Details>
<Content>One or more installer cache files for SQL Server are missing. Installer cache files must be present in order to successfully service or update an existing product component of SQL Server. In order to ensure the next service pack or cumulative update for SQL Server installs successfully, you need to restore the missing installer cache files. Follow the instructions in the article attached to this alert to resolve the problem.</Content>
<CollectedInformation>
<Info>
<Name>Missing installer cache files</Name>
<Value>{0}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

<UnitMonitor ID="Monitor_AK80677" Comment="SupportTopic=TBD;VersionNumber=1.0.0.0;" Accessibility="Public" Enabled="true" Target="MicrosoftKnowledgeServicesWindowsLibrary!Microsoft.KnowledgeServices.Windows.OperatingSystem" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="High" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessagef76197d0136b4cf3a84ec3da066d8588">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='StrMissingInstallerCacheFilesXMLOutput']$</AlertParameter1>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Success" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>AK80677.ps1</ScriptName>

    <Parameters/>

    <ScriptBody><Script>



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject



# Set up the output

$global:scriptoutput = new-object psobject

$scriptoutput | add-member NoteProperty "HasIssue" $false

$scriptoutput | add-member NoteProperty "StrMissingInstallerCacheFilesXMLOutput" ""

#----------------------------------------------------- 

# MAIN CODE SECTION

#----------------------------------------------------- 



# Environment



$scriptenv = New-Object System.Management.Automation.PSObject

$scriptenv | Add-Member NoteProperty "RuntimeError" $false

$scriptenv | Add-Member NoteProperty "ProductRegPaths" @()

$scriptenv | Add-Member NoteProperty "MissingInstallerCacheFiles" @()

$scriptenv | Add-Member NoteProperty "MissingInstallerCacheFilesXMLEnv" ""



$scriptServerComEnv = New-Object System.Management.Automation.PSObject

$scriptServerComEnv | Add-Member NoteProperty "HKEY_LOCAL_MACHINE" "HKLM"

$scriptServerComEnv | Add-Member NoteProperty "ServerComponents" @("SQL","RS","OLAP")

$scriptServerComEnv | Add-Member NoteProperty "SoftwareRegPathx86" "SOFTWARE\Microsoft\Microsoft SQL Server\"

$scriptServerComEnv | Add-Member NoteProperty "SoftwareRegPathx64" "SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\"

$scriptServerComEnv | Add-Member NoteProperty "ProductsRegPath" "SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\"

$scriptServerComEnv | Add-Member NoteProperty "PatchesRegPath" "SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Patches\"





#region Common private functions



Function IsExistRegKey($keyPath)

{

	$isExistRegKeyValue = $false

	

	if($keyPath)

	{

		if(Test-Path -Path ("{0}:\{1}" -f $scriptServerComEnv.HKEY_LOCAL_MACHINE,$keyPath))

		{

			$isExistRegKeyValue = $true

		}

	}

	

	return $isExistRegKeyValue

}



Function IsExistRegKeyValue($keyPath,$valuName)

{

	$isExistRegKeyValue = $false

	if($keyPath -and $valueName)

	{

		if(IsExistRegKey $keyPath)

		{

			$key = "{0}:\{1}" -f $scriptServerComEnv.HKEY_LOCAL_MACHINE,$keyPath

			if(Get-ItemProperty $key $valueName -ErrorAction SilentlyContinue)

			{

				$isExistRegKeyValue = $true

			}

		}

	}

	

	$isExistRegKeyValue

}



Function GetRegKeyNames($keyPath)

{

	if(IsExistRegKey $keyPath)

	{

		Get-ChildItem -Path ("{0}:\{1}" -f $scriptServerComEnv.HKEY_LOCAL_MACHINE,$keyPath) | ForEach-Object{

			$_.Name.Substring($_.Name.LastIndexOf("\") + 1,$_.Name.Length - $_.Name.LastIndexOf("\") - 1)

		}

	}

}



Function GetRegKeyValueNames($keyPath)

{

	if(IsExistRegKey $keyPath)

	{

		(Get-Item -Path ("{0}:\{1}" -f $scriptServerComEnv.HKEY_LOCAL_MACHINE,$keyPath)).GetValueNames() 

	}

}



Function GetRegKeyValueData($keyPath,$valueName)

{

	if(IsExistRegKeyValue $keyPath $valueName)

	{

		$key = "{0}:\{1}" -f $scriptServerComEnv.HKEY_LOCAL_MACHINE,$keyPath

		(Get-ItemProperty $key $valueName).$valueName

	}

}



Function ModifyString($strInput)

{

	if($strInput)

	{

		$result = ""

		for($i = 1; $i -le $strInput.Length/2; $i++)

		{

			$result += (ReverseString ($strInput.Substring($i*2-2,2)))

		}

		

		$result

	}

}



Function ReverseString($string)

{

	if($string)

	{

		$charArr = $string.ToCharArray()

		[array]::Reverse($charArr)

		[string]::Join("",$charArr)

	}

}



Function ConvertCodeToGuid($productCode)

{

	if($productCode)

	{

		$arrTokens = @()

		$arrTokens += $productCode.Substring($productCode.IndexOf("{")+1,$productCode.LastIndexOf("}")-1).Split("-")

		$subGuid = (ReverseString $arrTokens[0]) + (ReverseString $arrTokens[1]) + (ReverseString $arrTokens[2])

		$subGuid += (ModifyString $arrTokens[3]) + (ModifyString $arrTokens[4])

		

		$subGuid

	}

}



Function IsSqlAssociatedProduct([string]$productName)

{

	if(-not $productName)

	{

		return $null

	}

	

	$isSqlAssociatedProduct = $false

	

	$prodList = @("sql server 2008","sql server 2012","vss writer","sql server compact 3.5","sql server system clr","report viewer redistributable 2008","report viewer 2012","sql server data tools","prerequisites for ssdt")

	$prodTypeList = @("bpa","database engine services","analysis services","reporting services")

	

	foreach($prod in $prodList)

	{

		if($productName -like "*{0}*" -f $prod)

		{

			$flag = 0

			for($i = 0; $i -ne $prodTypeList.Length; $i++)

			{

				if($productName -notlike "*{0}*" -f $prodTypeList[$i])

				{

					$flag++

				}

			}

			

			if($flag -eq $prodTypeList.Length)

			{

				$isSqlAssociatedProduct = $true

				break

			}

		}

	}

	

	$isSqlAssociatedProduct

}



Function IsExistLocalPackage($packagePathInput)

{

	$isExistLocalPackage = $false

	

	if($packagePathInput)

	{

		$query = "select * from CIM_DataFile where name='{0}'" -f $packagePathInput.Replace("\","\\").Replace("'","\'")

		$localPackageObj = Get-WmiObject -Query $query

		if($localPackageObj -ne $null -and $localPackageObj.Count -ne 0)

		{

			$isExistLocalPackage = $true

		}

	}

	

	$isExistLocalPackage

}



Function IsEqual(

[System.Management.Automation.PSObject]$param1, `

[System.Management.Automation.PSObject]$param2)

{

	$isEaual = $false

	

	if($param1 -and $param2)

	{

		if($param1.DisplayNameCI.ToString().Trim() -eq $param2.DisplayNameCI.ToString().Trim() -and `

		$param1.LocalPackageCI.ToString().Trim() -eq $param2.LocalPackageCI.ToString().Trim())

		{

			$isEaual = $true

		}

	}

	

	$isEaual

}



Function NewMissingFileRecord($displayName,$localPackage)

{

	$missingFileInfo = New-Object System.Management.Automation.PSObject

	$missingFileInfo | Add-Member NoteProperty "DisplayNameCI" ""

	$missingFileInfo | Add-Member NoteProperty "LocalPackageCI" ""

	

	$missingFileInfo.DisplayNameCI = $displayName

	$missingFileInfo.LocalPackageCI = $localPackage

	

	return $missingFileInfo

}



Function FormatXMLData([array]$arrayList)

{

	$xmlTemplate = "&lt;row&gt;&lt;DisplayName&gt;{0}&lt;/DisplayName&gt;&lt;LocalPackage&gt;{1}&lt;/LocalPackage&gt;&lt;/row&gt;"



	if($arrayList)

	{

		$arrayList | ForEach-Object{

			$scriptenv.MissingInstallerCacheFilesXMLEnv += ($xmlTemplate -f $_.DisplayNameCI,$_.LocalPackageCI)

		}

	}

}



Function GetProductGuids($prodOrPatchRegPath)

{

	$productGuidList = @()

	if(IsExistRegKey $prodOrPatchRegPath)

	{

		foreach($serverComponent in $scriptServerComEnv.ServerComponents)

		{

			$componentPath = "{0}Instance Names\{1}" -f $prodOrPatchRegPath,$serverComponent

			foreach($instanceId in (GetRegKeyValueNames $componentPath))

			{

				$instanceSetupRegPath = "{0}{1}\Setup\" -f $prodOrPatchRegPath,(GetRegKeyValueData $componentPath $instanceId)

				$productCode = GetRegKeyValueData $instanceSetupRegPath "ProductCode"

				if($productCode)

				{

					$productGuidList += ConvertCodeToGuid $productCode

				}

			}

		}

	}

	

	$productGuidList

}



Function CheckInstallPropertiesKey($productRegPath)

{

	if($productRegPath)

	{

		$localPackageRegPath = "{0}\InstallProperties\" -f $productRegPath

		$localPackage = GetRegKeyValueData $localPackageRegPath "LocalPackage"

		$displayName = GetRegKeyValueData $localPackageRegPath "DisplayName"

		

		if($localPackage)

		{

			if(-not (IsExistLocalPackage $localPackage))

			{

				$scriptenv.MissingInstallerCacheFiles += NewMissingFileRecord $displayName $localPackage

			}

		}

	}

}



Function CheckInstallPatchesKey($productRegPath)

{

	if($productRegPath)

	{

		$patchesRegPath = "{0}\Patches\" -f $productRegPath

		$patchGuids = @()

		$processedPatchGuids = @()

		$patchGuids += GetRegKeyNames $patchesRegPath

		

		if($patchGuids)

		{

			foreach($patchGuid in $patchGuids)

			{

				if($processedPatchGuids -notcontains $patchGuid)

				{

					$processedPatchGuids += $patchGuid

					$patchPathRegPath = ("{0}{1}" -f $scriptServerComEnv.PatchesRegPath,$patchGuid)

					$localPackage = GetRegKeyValueData $patchPathRegPath "LocalPackage"



					if($localPackage -and -not (IsExistLocalPackage $localPackage))

					{

						$patchRegPath = "{0}{1}" -f $patchesRegPath,$patchGuid

						$displayName = GetRegKeyValueData $patchRegPath "DisplayName"

						$scriptenv.MissingInstallerCacheFiles += NewMissingFileRecord $displayName $localPackage

					}

				}

			}

		}

	}

}





#endregion Common private functions



#Step 1:

#    Get the sql components registry key and add the keys to dictionary

#    The registry keys to get in format of:

#    SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\xxxxxxxx,

#    where xxxxxxxx is the product GUID.

#    To get the product GUID:

#    a) Get the instance id from registry value "SQL_ROOT\Instance Names\[InstanceName]"

#    b) Get the setup registry key: SQL_ROOT\[InstanceID]\Setup\

#    c) Get ProductCode from registry value SQL_ROOT\[InstanceID]\Setup\ProductCode

#    d) Calculate product GUID from [ProductCode]

#    e) Add the product GUID to the dictionary.

Function GetProductGuidsWithPath()

{

	$productGuids = @()

	$productGuids += GetProductGuids $scriptServerComEnv.SoftwareRegPathx86

	$productGuids += GetProductGuids $scriptServerComEnv.SoftwareRegPathx64

	

	$productGuids | ForEach-Object{

		if($_)

		{

			$prodGuidWithPath = "{0}{1}" -f $scriptServerComEnv.ProductsRegPath,$_

			if($scriptenv.ProductRegPaths -notcontains $prodGuidWithPath)

			{

				$scriptenv.ProductRegPaths += $prodGuidWithPath

			}

		}

	}

}



#Step 2:

#    Query product registry key for components associated with SQL Server that

#    are not part of the service.

#    a) Enum all the sub keys under 

#		 SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products

#    b) For each sub key, get the registry value of PRODUCTS_ROOT\\InstallProperties\DisplayName

#    c) Check [DisplayName] to see if it is associated to SQL. If yes, add the sub key to dictionary.

Function GetProductGuidsWithPathAndDisplayName()

{

	GetRegKeyNames $scriptServerComEnv.ProductsRegPath | ForEach-Object{

		$displayName = GetRegKeyValueData ("{0}{1}\InstallProperties\" -f $scriptServerComEnv.ProductsRegPath,$_) "DisplayName"

		if(IsSqlAssociatedProduct $displayName)

		{

			if($scriptenv.ProductRegPaths -notcontains $_)

			{

				$scriptenv.ProductRegPaths += "{0}{1}" -f $scriptServerComEnv.ProductsRegPath,$_

			}

		}

	}

}



#Step 3: Enum each registry key PRODUCTS_ROOT\[ProductGuid] in the dictionary, do

#    a) Get value LocalPackage and DisplayName under PRODUCTS_ROOT\[ProductGuid]\InstallProperties\

#    b) [LocalPackage] is a local file path. Check if the file exist.

#    c) If not exist, add to the missed installer list

#    d) Enum sub keys under PRODUCTS_ROOT\[ProductGuid]\Patches\, do

#		d.1) Get LocalPackage and DisplayName value under PRODUCTS_ROOT\[ProductGuid]\Patches\[PatchGuid]\

#		d.2) [LocalPackage] is a local file path. Check if the file exist.

#		d.3) If not exist, add to the missed installer list

Function GetMissingFilesByProductGuid()

{

	$scriptenv.ProductRegPaths | ForEach-Object{

		CheckInstallPropertiesKey $_

		CheckInstallPatchesKey $_

	}

}



#Step 4: add the missed installer list to the property bag and return.

Function GenerateXMLReport()

{

	if($scriptenv.MissingInstallerCacheFiles)

	{

		$tempMICFs = @()

		

		#Remove duplicate records

		foreach($micf in $scriptenv.MissingInstallerCacheFiles)

		{

			if(-not $tempMICFs)

			{

				$tempMICFs += $micf

			}

			

			if(-not ($tempMICFs | Where-Object{IsEqual $micf $_}))

			{

				$tempMICFs += $micf

			}

		}

		

		FormatXMLData $tempMICFs

	}

}



# Main function



function AdvisorRule($scriptargs, $scriptoutput)

{ 

	# All parameters should be populated outside of the main function. 

	# The main function should only include the detection logic so that it can be easily reused by the Atlanta authoring tool.



	trap [Exception] {

		$scriptenv.RuntimeError = $true

      	continue; 

   	}

	

	# Initialize parameters

	$scriptoutput.HasIssue = $false

	$scriptoutput.StrMissingInstallerCacheFilesXMLOutput = ""

	

	# Set parameter values

	

	GetProductGuidsWithPath

	GetProductGuidsWithPathAndDisplayName

	GetMissingFilesByProductGuid

	GenerateXMLReport

	

	if($scriptenv.MissingInstallerCacheFiles)

	{

		if($scriptenv.RuntimeError -eq $false)

		{

			$scriptoutput.HasIssue = $true

			$scriptoutput.StrMissingInstallerCacheFilesXMLOutput = $scriptenv.MissingInstallerCacheFilesXMLEnv

		}

	}

}

AdvisorRule $scriptargs $scriptoutput



# set the output

$mom = new-object -comobject "MOM.ScriptAPI"

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.HasIssue -ne $null)

{

	$bag.AddValue("HasIssue", $scriptoutput.HasIssue)

}



if ($scriptoutput.StrMissingInstallerCacheFilesXMLOutput -ne $null)

{

	$bag.AddValue("StrMissingInstallerCacheFilesXMLOutput", $scriptoutput.StrMissingInstallerCacheFilesXMLOutput)

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86393</Schedule>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="Boolean">true</Value>

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Boolean">true</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>