Click an Ad

If you find this blog helpful, please support me by clicking an ad!

Friday, August 23, 2013

Daily Comprehensive DCDIAG on my Main Domain Controller

This script had interest from an enterprising gent on a previous blog post that outlined what kind of tasks I've automated and reporting I gather from my environment.

Every morning at 6AM, my primary domain controller runs a comprehensive DCDIAG, and sends it to me. I use select-string to search the results of the DCDIAG output for the string "Failed". It's not foolproof; sometimes I do get failure emails (most of the time it's laggy replication caused by Veeam taking a backup snapshot). But I'd rather have advanced warning that something might be wrong with the most important thing in my environment: Active Directory.

As has become my custom, the comments do the talking from here on out.

#Specify some variables: the output file, what I'll search for, and email settings.
$TempFile = "C:\Temp\DCDiag_Temp.txt"
$SearchText = "Failed"
$SMTP = "mailserver.contoso.com"
$To = "me@contoso.com"
$From = "reports@contoso.com"

#Run the DCDIAG with the following switches: Comprehensive, Enterprise (runs against all DCs)
#and verbose, outputting to file
DCDiag /c /e /v > $TempFile

#Read the File
$DCDiag = (Get-Content $TempFile)

#Look for the string, and count how many time it appears, then convert it to a string
#Then take out some newline characters
$FailCount = (($DCDiag | select-string -simple $SearchText | measure-object).count | out-string)
$FailCountString = ($FailCount | out-string)
$FailCountString = ($FailCountString.replace("`r`n",""))

#Format the email, placing a count of the term "Failed" in the subject
$Subject = "PS Report - DCDiag Error Report - $FailCountString Errors"
$Body = "$FailCountString Errors"

#Send me an email
Send-Mailmessage -from $From -to $To -subject $Subject -smtpserver $SMTP -body $Body -Attachments $TempFile

#Delete the temp file
Remove-Item $TempFile

---------------------------------------------------
Normally, I only send the email if there's something to report, but I send this one every day for two reasons. One, I like knowing that it's running, and two, it gives me a warm fuzzy feeling seeing that AD is healthy (almost) every morning.

Monday, August 19, 2013

Excel: Convert an Entire Column From Kilobytes to Megabytes

I'm always making reports in excel, usually when outputting to CSV. Until now, I would go and look up how to use Powershell to convert this, but sometimes I just need to throw something together quickly, and this is what I learned to do in Excel to convert a whole column from one thing to another:

I'm running Office 2010, by the way.

Put the number 1024 into a cell that's not in the column you're going to convert.
Now, copy that one cell.
Select the column that you want to convert.
Right-click, select Paste Special, Paste Special AGAIN, then "Divide".

Looking at the menu where you choose divide, you'll notice other mathematical operation. Sure enough, you can use this same method to add, subtract, or multiply an entire selection of values by whatever number you have copied.

Wednesday, August 14, 2013

Database Owner Unknown

I've been changing all of my MS SQL databases to the Simple recovery model, so I don't have to worry about transaction log backups and truncation. I only left really important ones that need point-in-time rollback available on full. While trying to change one of my 'ReportServer' databases to simple, I was not able to right-click on it in SQL Server Management Studio (SSMS) and select properties. I always got the following error:

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database '[DBName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

Now, I was logged in as the 'sa' account, so "insufficient access" isn't the problem. The problem was that the database in question HAD NO OWNER!!!

I verified this by running the following query against the master database:
sp_helpdb ReportServer

The runs a stored procedure (the 'sp' part) called 'helpdb' which displays info about the database you choose (ReportServer). This query returned a table of info, and the owner field said '~~~UNKNOWN~~~'. Not so good.

The fix was simple - use another stored procedure:
sp_changedbowner 'sa'

The query ran successfully, and I was able to then access the properties of the ReportServer database and change the recovery model.