June, 2009

Select Folder Dialog Box

If you would like your scripts to interact with the user and have a nice popup box for a user to select a folder here is a link to an article that will help you out. I think this is pretty great.

 http://powershell.com/cs/blogs/tips/arch…

Reboot a Remote Windows Host Remotely

Say you need to reboot a server and this is something that you would like to automate based on certain criteria. For instance, I might want to reboot a server if I can talk to the server but for somereason my SQL Server is not up and running. If everything is configured correctly the SQL Server should start up again on boot. So you can do something like the following.

$varname = Get-WmiObject Win32_OperatingSystem -computername “remotemachine”

$varname.reboot()

Now you can check your SQL Server once the machine is back up.

Test a path in Powershell

this is a simple thing but it can be very useful. I use this is many of my scripts. Sometimes I want to make sure that a file is available for me to append and if not then I would like to create a new one. Take a log file for example, if the file has been moved or deleted, perhaps it grew very large and needed to be removed, the script can just create a new one without running into any problems. If the file is still available though, then the script can just append the current file. here is an example:

if (!(test-path “H:\path\file.txt”))
{
New-Item -type File “H:\path\file.txt”
}

This will check if the file exists and if not then it will create it.

Finding what account a Service is running as

Yesterday my boss came to me asking if I knew what server an account was running on. I know that the account is used as a SQL Server service account because of the naming convention that was used. I also know that the account is being used as I used the Quest Software cmdlet get-qaduser to query AD for the account and get the last logon time. Now I just needed to find out what server it was running on. With the account naming conventions that we use this normally would not be an issue but in this case it was as the server that it should have been used on no longer exists. The server had been virtualized and now has a new name. So I put together a script that would identify the server for me. I had the script read in all of my servers from my sqltab file that I have talked about before, and list all of the sql server services and the accounts that they were running as for each server. Here is a nice little one liner that will get you a listing of all sql server services on a server and the account that they are running as. Hope you can get some use out of this.

Get-WmiObject Win32_service -computername $computername | select name, startname | Where-Object{$_.name -like “*SQL*”}

Another Example of why you should learn Powershell

I was roaming around the web and reading some articles and I ran across the following blog post:

 http://bassplayerdoc.blogspot.com/2008/1…

I think this is a great reason why all administrators that deal with Microsoft products should learn powershell. I know some Administrators are happy with VBScript and I know that many people use Pearl or other languages. I am all for knowing them and using them when they make the most sense. Some languages are better at some things then others. I think that Powershell is the Future of Microsoft Administration and this is a great example. Check out the other articles on this Blog as well. Some really good stuff.

Shocking Death of an Icon

Since I work for a news media outlet I thought that I would comment on this news. Michael Jackson’s untimely death. Everyone knows about Michael and all the controversy that has surrounded him for many years now. whether or not you are a fan, a hater, you believe he has been guilty or innocent of all the crimes that he was accused of, all must admit that he made is mark on the world though his music. He entertained us with music, lyrics, and dancing. He could move like nobody else and so many artists today try to imitate him in so many ways. I do not think that I have ever met anyone who has not liked at least one of his songs or videos. How can you not be a fan of Thriller? I remember being very young, 7 or 8, and I was at a rollerskating rink when the video made its premiere. All skating stopped and everyone sat down in front of a big screen and watched the video. Who else has had the world stop to pay attention like that? Not even all Presidents have had the world listen like that.

I am not going to voice my opinion about him as a man but as an entertainer he was one of the best. He has influenced so many in so many ways, and I do think he will be sorely missed. My thoughts go out to his friends and family and all I can say is “Do You Remember the Time”.

PowerShell SQL Server Health Check Script

OK so I think that I have everything I want in my script to check on the health of my SQL Servers. I will run this at least once a day and more if needed. I hope that this will help some people out and if you have any questions, comments, or ideas of what else might be good to include please let me know.

SQL Health Check Script:

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
#$Excel.visible = $False

#$workbook = $excel.Workbooks.Open("D:\pac health checks.xlsx")

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 2

#Read thru the contents of the SQL_Servers.txt file
#$servers = Import-Csv "\\pni-vmdbasql\d$\monitoring\inputs\sqltab.txt"
$servers = Import-Csv "\\ent-pocpacapcx01\d$\monitoring\sqltab.txt"

#########################################################
foreach ($entry in $servers)
{
$torp = $entry.TorP
$mon = $entry.monitor
$machine = $entry.server
$errorlog = $entry.errorlog
$os = $entry.os2000
$iname = $entry.Instance
if ($iname -eq "Null")
{
$instance = "$machine"
}
else
{
$instance = "$machine\$iname"
}
if ($torp -eq "Prod")
{
$ServerType = "Production"
}
else
{
$ServerType = "Test"
}
$instance = $instance.toupper()

#########################################################
#foreach ($instance in get-content "D:\SQL_Servers.txt")
#{

#Create column headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,2) = $instance
################################################

#This script gets SQL Server database information using PowerShell

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Create an SMO connection to the instance
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs = $s.Databases

#$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

#Formatting using Excel

$version = $s.VersionString

if ($version -like "*9.00.1399*")
{        $sqlversion = "SQL Server 2005 No Service Pack"
}
elseif ($version -like "*9.00.2047*")
{        $sqlversion = "SQL Server 2005 SP1"
}
elseif ($version -like "*9.00.3042*")
{        $sqlversion = "SQL Server 2005 SP2"
}
elseif ($version -like "*9.00.3068*")
{        $sqlversion = "SQL Server 2005 SP2"
}
elseif ($version -like "*9.00.3054*")
{        $sqlversion = "SQL Server 2005 SP2 with Q934458"
}
elseif ($version -like "*9.00.3073*")
{        $sqlversion = "SQL Server 2005 SP2 with Q954606"
}
elseif ($version -like "*9.00.3186*")
{        $sqlversion = "SQL Server 2005 SP2 with Q939562"
}
elseif ($version -like "*9.00.4035*")
{        $sqlversion = "SQL Server 2005 SP3"
}
## SQL Server 2000 Checks
elseif ($version -like "*8.00.194*")
{        $sqlversion = "SQL Server 2000 No SP"
}
elseif ($version -like "*8.00.384*")
{        $sqlversion = "SQL Server 2000 SP1"
}
elseif ($version -like "*8.00.532*")
{        $sqlversion = "SQL Server 2000 SP2"
}
elseif ($version -like "*8.00.760*")
{        $sqlversion = "SQL Server 2000 SP3"
}
elseif ($version -like "*8.00.818*")
{        $sqlversion = "SQL Server 2000 SP3 with MS03-031"
}
elseif ($version -like "*8.00.2039*")
{        $sqlversion = "SQL Server 2000 SP4"
}
elseif ($version -like "*8.00.2040*")
{        $sqlversion = "SQL Server 2000 NoSP with Q274329"
}
elseif ($version -like "*8.00.2050*")
{        $sqlversion = "SQL Server 2000 SP4 with MS08-040"
}
elseif ($version -like "*8.00.2187*")
{        $sqlversion = "SQL Server 2000 SP1 with Q923849"
}
elseif ($version -like "*8.00.2282*")
{        $sqlversion = "SQL Server 2000 QFE"
}
elseif ($version -like "*8.00.3073*")
{        $sqlversion = "SQL Server 2000 SP2 with Q954606"
}
#SQL Server 7
elseif ($version -like "*7.00.1094*")
{        $sqlversion = "SQL Server 7 SP4 with Q815495, Q821279"
}
#SQL Server 2008
elseif ($version -like "*10.0.1442.32*")
{        $sqlversion = "SQL Server 2008 X64 MSDN Beta"
}

else
{        $sqlversion = "Check me $version"
}

$Sheet.Cells.Item($intRow,3) = "Version:"
$Sheet.Cells.Item($intRow,4) = $sqlversion
$Sheet.Cells.Item($intRow,5) = $ServerType
if ($Servertype -eq "Production")
{
$fgColor = 35
}
else
{
$fgColor = 0
}
$Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor

###########################################################

$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$Sheet.Cells.Item($intRow,3).Font.Bold = $True
$Sheet.Cells.Item($intRow,4).Font.Bold = $True
$Sheet.Cells.Item($intRow,5).Font.Bold = $True

##############################################
## Are full and tranlog backups running?
## DB Reindexing running?
##############################################

$intRow ++

$jobsserver = $s.JobServer
$jobs = $jobsserver.Jobs
$ijob = 0
$backjob = 0
$tranjob = 0
foreach ($job in $jobs)
{

if (($job.name -like "*backup*") -and ($job.name -notlike "*Tranlog*"))
{
$backjob = 1
$fullbackup = $job.name
$fullbackupstatus = $job.LastRunOutcome
$fullbackupdate = $job.LastRunDate

}
if ($job.Name -like "*tranlog*")
{
$tranjob = 1
$tranlogbackup = $job.name
$tranlogbackupstatus = $job.LastRunOutcome
$tranlogbackupdate = $job.LastRunDate

}
if ($job.Name -like "*index*")
{
$ijob = 1
$rebuildindex = $job.Name
$rebuildindexstatus = $job.LastRunOutcome
$rebuildindexdate = $job.LastRunDate

}
}

## Check to verify full backups are running ##
$dayago = [datetime]::Now.AddDays(-1)
if (($fullbackupstatus -eq "Succeeded") -and ($fullbackupdate -gt $dayago))
{
$fullbackupjob = "Full Backups Running"
}
elseif(($fullbackupstatus -ne "succeeded") -or ($fullbackupdate -lt $dayago))
{
$fullbackupjob = "Full Backups Failing"
}
if ($backjob -eq 0)
{
$fullbackupjob = "No Full Backup Job"
}

## Check to verify that Tranlogs backups are running fine ##
$hourago = [datetime]::Now.AddHours(-1)
if (($tranlogbackupstatus -eq "Succeeded") -and ($tranlogbackupdate -gt $hourago))
{
$tranbackupjob = "Tran Backup Running"

}
if(($tranlogbackupstatus -ne "succeeded") -or ($tranlogbackupdate -lt $hourago))
{
$tranbackupjob = "Tran BackupFailing"
}
if ($tranjob -eq 0)
{
$tranbackupjob = "No Tran Backup Job"
}

$weekago = [datetime]::Now.AddDays(-7)
if (($rebuildindexstatus -eq "Succeeded") -and ($rebuildindexdate -gt $weekago))
{
$indexjob = "Indexing Running"

}
elseif(($rebuildindexstatus -ne "succeeded") -or ($rebuildindexdate -lt $weekago))
{
$indexjob = "Indexing Failing"

}
if ($ijob -eq 0)
{
$indexjob = "No Indexing Job"

}

$Sheet.Cells.Item($intRow,1) = $fullbackupjob
$Sheet.Cells.Item($intRow,2) = $tranbackupjob
$Sheet.Cells.Item($intRow,3) = $indexjob
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$Sheet.Cells.Item($intRow,3).Font.Bold = $True

##############################################

$intRow++

$Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
$Sheet.Cells.Item($intRow,2) = "RECOVERY MODEL"
$Sheet.Cells.Item($intRow,3) = "SIZE (MB)"
$Sheet.Cells.Item($intRow,4) = "SPACE AVAILABLE (MB)"
$Sheet.Cells.Item($intRow,5) = "DATA DRIVE"
$Sheet.Cells.Item($intRow,6) = "SPACE AVAILABLE ON DISK (GB)"
$Sheet.Cells.Item($intRow,7) = "MIRROR STATUS"
$Sheet.Cells.Item($intRow,8) = "LOG SIZE (MB)"

#Format the column headers
for ($col = 1; $col –le 8; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}

$intRow++
#######################################################
#      #This script gets SQL Server database information using PowerShell
#
#      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
#
#      # Create an SMO connection to the instance
#      $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
#
#      $dbs = $s.Databases
#
#      #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
#
#      #Formatting using Excel

foreach ($db in $dbs)
{
$name = $db.name
$model = $db.recoverymodel
if ($model -eq 1)
{
$modelname = "Full"
}
elseif ($model -eq 2)
{
$modelname = "Bulk Logged"
}
elseif ($model -eq 3)
{
$modelname = "Simple"
}

$logfiles = $db.LogFiles
foreach ($log in $logfiles)
{
$logsize = $log.size/1KB
$logsize = [math]::Round($logsize, 2)
}

#if(($name -ne "master") -and ($name -ne "model")) # -and ($name -ne "msdb"))
#{
#Divide the value of SpaceAvailable by 1KB
$dbSpaceAvailable = $db.SpaceAvailable/1KB

#Format the results to a number with three decimal places
$dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable

$Sheet.Cells.Item($intRow, 1) = $db.Name

$Sheet.Cells.Item($intRow, 2) = $modelname
$Sheet.Cells.Item($intRow, 3) = "{0:N3}" -f $db.Size

#Change the background color of the Cell depending on the SpaceAvailable property value
if ($dbSpaceAvailable -eq 0.00)
{
$fgColor = 38
}
else
{
$fgColor = 0
}

$Sheet.Cells.Item($intRow, 4) = $dbSpaceAvailable
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor

$dblocation = $db.primaryfilepath

$dblocation = $dblocation.split(":")

$dbdrive = $dblocation[0]
$drives = Get-WmiObject -ComputerName $machine Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
foreach($drive in $drives)
{
$size1 = $drive.size / 1GB
$size = "{0:N2}" -f $size1
$free1 = $drive.freespace / 1GB
$free = "{0:N2}" -f $free1
$ID = $drive.DeviceID
$a = $free1 / $size1 * 100
$b = "{0:N2}" -f $a

if ($dbdrive -eq "C")
{
$fgColor = 38
}
else
{
$fgColor = 0
}

$Sheet.Cells.Item($intRow,5) = $dbdrive
$Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor

if ($id -like "$dbdrive*")
{

if ($free1 -lt 5)
{
$fgColor = 38
}
else
{
$fgColor = 0
}
if (($ID -eq "C:") -and ($free1 -lt 1))
{
$fgColor = 38
}
$Sheet.Cells.Item($intRow,6) = $free1
$Sheet.Cells.item($intRow, 6).Interior.ColorIndex = $fgColor
}
}
if($version -like "*2000*")
{$mirrorstate = 0}
else
{
$mirrorstate = $db.MirroringStatus
}
if ($mirrorstate -eq 0)
{
$mirror = "No Mirror"
}
if ($mirrorstate -eq 1)
{$mirror = "Suspended"
}
if($mirrorstate -eq 5)
{
$mirror = "Synchronized"
}
if ($mirrorstate -eq 1)
{
$fgcolor = 38
}
else
{
$fgcolor = 0
}
$Sheet.Cells.Item($intRow,7) = $mirror
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

if ($logsize -gt 500)
{
$fgColor = 38
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow,8) = $logsize
$Sheet.Cells.item($intRow, 8).Interior.ColorIndex = $fgColor

$intRow ++

#}
}

$intRow ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

$workbook.saveas("D:\pac health checks.xlsx")
#$excel.Quit()
$WorkBook = $Null
$WorkSheet = $Null
$Excel = $Null
[GC]::Collect()

Here is a sample of what the report will look like when you have run it.

Report Output Sample

Powershell Query AD

I have talked in the past about some cmdlets that Quest Software provides for Powershell. Well today they came in usefull for me. My boss came up and wanted to know when the last time a certain service account had authenticated to the domain. Now we log all users logging in via our login script but since this is a service account, the login script does not fire and therefore we do not get a log of the event. So using the cmdlets from Quest Software I did the following and got what we needed.

$1600users = Get-QADUser | where {$_.logonname -like “*p1600-10*”}
foreach($1600user in $1600users)
{
$name = $1600user
$date = $1600user.LastLogon
echo “$name logged in on $date”
}

That is it and it let us know that we have two accounts that have to do with the server services that we were interested in and also let us know the last logon time for each.

Hope this is helpful.

SQL Server Health Report

Yesterday I posted about a great article that I found on how to use Powershell to create an excel file and also use SMO to get information about SQL Server. I am working on a report that I will have run daily. This report is going to give me an overall health check of my SQL Servers. I am going to have it report to me if all of my important jobs are set up and running properly, These jobs being things like backups, tranlog backups (if the Database is using the Full Recovery Model), Rebuild indexes, and things like that. it will tell me how big each Database is, where the MDF and LDF files are on the filesystem, How much drive space we have available for those databases on the filesystem, how much space allocated to the DB is Free, and finally, for instances that have mirroring set up it will tell me the state of the mirror.

For the servers that I am working with on this report I feel that this really does give me a good health check of the server. I am sure that as time goes on I will add more and perhaps modify this to output as a web page and then put some nice eye candy on it for things like cache hit ratio and other metrics like that. I will be posting the code for this report when I am complete with it.

Microsoft Live Mesh

So, I do not know about you allI can say that since I know something about computers I get hit up for free computer help from family all the time. I do not really mind this but it is sometimes hard to get to them in a timely manner. Well I stumbled upon Microsft Live Mesh the other day and it is pretty cool. Here is a link so you can check it out.

 https://www.mesh.com/welcome/default.asp…

This gives you 5GB of space on a virtual desktop so that you can synch and share files between computers, and it will let you remote a computer while letting the people on the other end watch, or you can hide it from them. I have used this a couple times and I like it. It is still in Beta so take it with a grain of salt. I hope that it gets better but I really love the Idea. Before I found this I was tempted to sign up for another pay service to do remoting. All you need for this is a Windows Live ID.

Let me know what you think about it.