The Multifunctioning DBA

Oct 24 2012   9:22PM GMT

PowerShell CName Reporting

Colin Smith Colin Smith Profile: Colin Smith


Recently my team started using CName records instead of server names for applications to connect to. This makes moving things around on backend much easier. So here is the script that I am using.

Snapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Invoke-Sqlcmd -ServerInstance dbhubsql -Database dba -Query "truncate table SqlCname"
$cnames = dnscmd DCName /EnumRecords Domain . /Type CNAME
foreach($cname in $cnames)
if ($cname -like "sql*")
$cname = $cname.Replace("3600","")
$cname = $cname.Replace("CNAME","")
$cname = $cname.Replace(".domain.","")
$mid = $cname.IndexOf(" ")
$alias = $cname.Substring(0,$mid)
$server = $cname.Substring($mid+1)
$alias = $alias.Trim()
$server = $server.Trim()
$uid = Invoke-Sqlcmd -ServerInstance server -Database db -Query "Select UniqueID from instance where InstanceName = '$server'"
$uid = $uid.UniqueID
Invoke-Sqlcmd -ServerInstance server -Database db -Query "insert into SqlCname (UniqueID, Alias) Values ($uid, '$alias')"


I am pushing the results into a database that I already have, just a new table. This allows me to report on this data anytime I need to.

Hope this helps.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: