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
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”)

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: