so here’s our situation. first I’ll premise this by saying I’m not a SQL expert, and I’m still wrapping my head around the Relevance, PowerShell and xml needed here - I know enough to know I don’t know enough.
We have several SQL always-on clusters, where at the end of the process we need to have the DB residing back on the primary node - I know clusters shouldn’t need that but for reasons not relevant here, it’s the way they are for now.
our deployment engineer gave us two scripts he’d built for our clusters, one for the DR and Secondary nodes, and one for the Primary nodes. The first script seems to work fine, as it doesn’t need to do anything other than run the baseline and patch, etc etc.
the second script is - using fixlets, supposed to fail the db over from primary to secondary, patch from the baseline and then take the db back to itself, however it’s seeming to fail on the last step.
could anyone take a peek through this sanitized version and let me know what you all think?
--- CONFIG ---
$RootServer = "https://PLACEHOLDER.DOMAIN.COM:52311" # example: https://mybfserver.domain.local:52311
$Username = "PLACEHOLDER" # must have REST API access, master operators are implicitly allowed (real account does have access.)
$Password = "PLACEHOLDER"
$CustomSiteName = "Cluster SQL Patches" # an existing CUSTOM site you can write to
$BaselineTitle = "Auto - Patches for Windows Test/Dev Clustered Primary- $(Get-Date -Format yyyy-MM-dd)" # change title for secondary and primary
$MaxComponents = 150 # cap to keep the baseline lean; adjust as needed
=== SCHEDULING CONFIG ===
When should the action start? (LOCAL time on this box)
Example: tomorrow at 2am local
$ScheduleStartLocal = (Get-Date).Date.AddHours(18) # +17 for secondary +18 for Primaries
For how long should the action be active after it starts?
(Keeps the action open until Start + RunDuration; clients can run within this window.)
$RunDuration = New-TimeSpan -Hours 2
--- Helpers ---
Add-Type -AssemblyName System.Web
[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]'Tls12'
Optional (self-signed root) - view BigFix docs on creating a self-signed cert to beef up security
[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }
[System.Net.ServicePointManager]::Expect100Continue = $false
$pair = "{0}:{1}" -f $Username, $Password
$basicAuth = "Basic " + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes($pair))
$headers = @{ Authorization = $basicAuth }
function Invoke-BESQuery([string]$relevance) {
$encoded = [System.Net.WebUtility]::UrlEncode($relevance)
$uri = "$RootServer/api/query?relevance=$encoded"
[xml](Invoke-RestMethod -Uri $uri -Headers $headers -Method GET -SkipCertificateCheck)
}
--- 1) Get the Patches for Windows site gather URL ---
$relSiteUrl = @'
url of bes site whose (name of it as lowercase starts with "enterprise security")
'@
$siteXml = Invoke-BESQuery $relSiteUrl
$SiteUrl = $siteXml.BESAPI.Query.Result.Answer.'#text'
if (-not $SiteUrl) { throw "Could not resolve Patches for Windows site URL." }
--- 2) Get relevant Fixlets (+ default action id + applicable count) ---
$relFixlets = @'
(
id of it as string
& "|" & (
if (exists default action of it)
then content id of default action of it
else "Action1"
)
& "|" & (applicable computer count of it as string)
)
of bes fixlets
whose (
fixlet flag of it
and (
exists default action of it
or exists action whose (content id of it = "Action2") of it
)
and name of site of it = "Enterprise Security"
and (name of it as lowercase contains "sql" or name of it as lowercase contains "cumulative" or name of it as lowercase contains "security" or name of it as lowercase contains "update" )
and category of it as lowercase contains "update"
and category of it as lowercase does not contain "unspecified"
and name of it as lowercase does not contain "superseded"
and applicable computer count of it > 0
)
'@
$fixXml = Invoke-BESQuery $relFixlets
and (name of it does not include "office" and name of it does not include "outlook" and name of it does not include "onenote" and name of it does not include "word" and name of it does not include "excel" and name of it does not include "powerpoint")
Parse tuples from /api/query XML
$raw = @()
if ($fixXml.BESAPI.Query.Result.Tuple) {
foreach ($t in $fixXml.BESAPI.Query.Result.Tuple) {
$raw += $t.Answer.'#text'
}
} elseif ($fixXml.BESAPI.Query.Result.Answer) {
$raw += $fixXml.BESAPI.Query.Result.Answer.'#text'
}
$items = $raw |
Where-Object { $_ -and ($_ -match '|') } |
ForEach-Object {
$parts = $_ -split '|',3
[pscustomobject]@{
FixletID = [int]$parts[0]
ActionName = $parts[1] # typically "Action1"
AppCount = [int]$parts[2] # applicable computer count
}
} |
Sort-Object -Property AppCount -Descending
if (-not $items) { throw "No relevant Fixlets found." }
Optional: limit baseline size (can cause performance issues)
$items = $items | Select-Object -First $MaxComponents
--- 3) Build baseline XML (components from source) ---
$components = $items | ForEach-Object {
' ' -f $SiteUrl, $.FixletID, $.ActionName
} | Out-String
$baselineXml = @"
$BaselineTitle
Auto-generated baseline of currently relevant Fixlets from Patches for Windows.
true
<!-- ===== PRE (single hardcoded fixlet) ===== -->
<BaselineComponentGroup>
<!-- Fill these three placeholders -->
<BaselineComponent IncludeInRelevance="true"
SourceSiteURL="http://bigfix.ad.cdirad.com:52311/cgi-bin/bfgather.exe/CustomSite_Rayus_SQL_Patches"
SourceID="8321"
ActionName="Action1" />
</BaselineComponentGroup>
<!-- ===== Dynamic components (your current list) ===== -->
<BaselineComponentGroup>
$components
</BaselineComponentGroup>
<!-- ===== POST (single hardcoded fixlet) ===== -->
<BaselineComponentGroup>
<!-- Fill these three placeholders -->
<BaselineComponent IncludeInRelevance="true"
SourceSiteURL="http://bigfix.ad.cdirad.com:52311/cgi-bin/bfgather.exe/CustomSite_Rayus_SQL_Patches"
SourceID="8323"
ActionName="Action1" />
</BaselineComponentGroup>
</BaselineComponentCollection>
--- 4) POST baseline into your custom site ---
$createUri = "$RootServer/api/baselines/custom/$CustomSiteName"
$response = Invoke-RestMethod -Uri $createUri -Headers $headers -Method POST -ContentType "text/xml" -Body $baselineXml -SkipCertificateCheck
Extract new baseline ID for convenience
[xml]$respXml = $response
$NewBaselineID = ($respXml.BESAPI.'Baseline' | Select-Object -First 1).'ID'
Write-Host "Created baseline '$BaselineTitle' in site '$CustomSiteName' with ID: $NewBaselineID"
--- 5) Build & POST a scheduled Action for the new baseline ---
Convert schedule to UTC and compute end time
$startUtc = $ScheduleStartLocal.ToUniversalTime()
$endUtc = $startUtc + $RunDuration
$startUtcStr = $startUtc.ToString("yyyy-MM-ddTHH:mm:ssZ")
$endUtcStr = $endUtc.ToString("yyyy-MM-ddTHH:mm:ssZ")
#All Computers relevance removed from line 166ish
true
<!-- Or:
-->
$actionXml = @"
$CustomSiteName
$NewBaselineID
Action1
<CustomRelevance>member of group 1443 of site "ActionSite"</CustomRelevance>
</Target>
<Settings>
<PreActionShowUI>false</PreActionShowUI>
<HasStartTime>true</HasStartTime>
<StartDateTimeLocal>$($ScheduleStartLocal.ToString("yyyy-MM-ddTHH:mm:ss"))</StartDateTimeLocal>
<HasEndTime>true</HasEndTime>
<EndDateTimeLocal>$($ScheduleStartLocal.Add($RunDuration).ToString("yyyy-MM-ddTHH:mm:ss"))</EndDateTimeLocal>
<HasDayOfWeekConstraint>false</HasDayOfWeekConstraint>
<UseUTCTime>false</UseUTCTime>
<Reapply>false</Reapply>
<ContinueOnErrors>true</ContinueOnErrors>
<PostActionBehavior Behavior="Restart">
<AllowCancel>false</AllowCancel>
<PostActionDeadlineBehavior>RunAutomatically</PostActionDeadlineBehavior>
<PostActionDeadlineInterval>PT1M</PostActionDeadlineInterval>
</PostActionBehavior>
<IsOffer>false</IsOffer>
</Settings>
replace with and remove lines after and before to not reboot
$actionUri = "$RootServer/api/actions"
$actionResp = Invoke-RestMethod -Uri $actionUri -Headers $headers -Method POST -ContentType "text/xml" -Body $actionXml -SkipCertificateCheck
[xml]$actionXmlResp = $actionResp
$ActionID = ($actionXmlResp.BESAPI.Action | Select-Object -First 1).ID
Write-Host "Created scheduled action $ActionID. Starts (UTC): $startUtcStr Ends (UTC): $endUtcStr"
