November, 2008

Powershell – Get a list of Exchange Users

In my last post I talked about using the Exchange Management Shell to get a list of users that needed to be placed into a group. Here is what we did. First we had to determine how we could find just the users that were of interest to us. We know that we had set two Custom Attributes on each of these users mailboxes so we decided to use that as a search filter. This is a pretty simple one liner but very effective and powerful.

Get-mailbox | where-object{$_.CustomAttribute15 –eq “some value”} | format-table Name | out-file “path to file”

So that looks at all mailboxes on the domain and will return only the names of the users whose mailboxes have CustomAttribute15 set to the value that we were looking for. Now that I have that list I am ready to move on to putting those users in the group. If I added the snap in for the Quest AD Command lets then I could have just used another | to that command and not even needed the file. We wanted that for records and to verify that the correct users were being modified. Again if you have any questions or comments about this please let me know by leaving them here or heading out to my website SysAdminSmtih.com and submitting a question to me.

Powershell – Add a User to a Group

Today I was asked to help a fellow administrator get a list of users and add all of these users to a specific group. The users that he was interested in were all the users that he had moved to Exchange since starting his migration process. In order to get this list of users we used the Exchange Management Shell and the get-mailbox command to get the list. Once we had the list we needed to add these users to a specific group. We could have used ADSI to do this but I thought it was a great time to learn more about and use the AD Command-Lets that Quest Software has provided for free. So now we have the list of users and we formatted it to only give us the lastname, firstname. Now I added the snap in for the new command lets and we were off. Here is what we did and it worked just great.

$users = get-content “List of users”

Foreach($user in $users)

{

    Add-qadgroupmember –Identity “CN=GroupName,OU=OUName,DC=DCName” –Member $user

}

Just that simple and we added about 450 users to the group that we needed. This was a fun one to do and nice to learn more about the Quest tools. They did a good job on the AD Command lets and I appreciate them making them available for all. If you have any questions or comments about this please let me know by leaving a comment here or by heading to my website SysAdminSmith.com and submitting a question to me. I will get back to you either way.

MS SQL Server Monitoring Part 4

It has been a while since my last post about MS SQL Server Monitoring using Powershell. This is because I found that I was not catching all the errors that I wanted. I was missing any jobs that were failing. SQL Server gives you the ability to output job failures to the Windows Event log but not into the SQL Server ERRORLOG file. So I decided that I wanted to monitor the Application Event logs on the servers as well. This was not as easy as I had hoped. First I have to get a connection into the event log file and then I can scan the file. I also only want to scan the event log from the last time that it was scanned until now. This took some creativity and some working with Data Data types. Anyway, that is enough about talking about the problems. Let’s look at some code.

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

## Search Server Application Log for SQL Errors ##

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

$elog = “Application”

if ($instance -eq “Null”)

{

    $instancename = “mssqlserver”

}

else

{

    $instancename = “MSSQL`$$instance”

}

echo “the Instance name is $instancename”

$time = Get-Content “h:\$folder\time.txt”

Echo “Looking for errors since $time`n”

$objlog = New-Object system.Diagnostics.EventLog($elog, $machine)

$logentries = $objlog.get_entries()

foreach ($logentry in $logentries)

{

    $eltime = $logentry.timegenerate | Get-Date -uformat “%m/%d/%Y %R”

    $Source = $logentry.source

    $message = $logentry.message

    if (($source -eq $instancename) -and ($eltime -ge $time))

{

        $ignoreit = 0

        foreach ($estring in$estrings)

{

            if ($message -like “*$estring*”)

{

                $ignoreit = 0

                foreach ($istring in $ignore)

{

                    if ($message -like “*$istring*”)

{

                        $ignoreit = 1

                    }

                }

                break

            }

            else

{

                $ignoreit = 1

            }

        }

        if ($ignoreit -eq 0)

{

            #Echo “Error Found”

            $mark = 1

            $descript = 1

            echo $folder >> “H:\Alert\Alert”

            if (!(test-path “H:\$folder\monitor.out”))

{

                New-Item -type File “H:\$folder\monitor.out”

            }

            Echo $message >> “H:\$folder\monitor.out”

            Echo “Error Found for $Source at $eltime”

        }

        else

{

            #Echo “No Error Found”

        }

    }

}

So I am looking at $elog and I have that set to Application. You can change this variable to look at any of the event logs that you would like. I also have to tell it what the last time was that I looked for an error. I do this by settting up a file in $folder that will keep track of the time of the last entry that I have looked at. I initially have this set to mm/dd/yyy hh:mm. I read this in and then I have to read in the time of the entries that I am reading. I convert that in order to do the compare of the last timestamp of the last entry. Once I do that I can also compare the source of the error to the instance name that I am searching for. If the entry meets the criteria then I can start looking for strings that are in my error_strings file and ignoring errors that have a string that is also in the ignore_strings file. After that it is pretty basic. If I find an error I put that in the monitor.out file and I put an entry in the alert file for this server. That will trigger the notifications until I or somebody responds to the error. Let me know if you have any questions or comments. Leave them here or stop by http://sysadminsmith.com and submit a question to me.

Powershell Alternate Credentials

I received a question from Bryan about my Post on how to use Powershell to Monitor Disk Space. He was curious how he could pass alternate credentials using Powershell in order to connect to some servers that are sitting out in a DMZ. First I want to thank Bryan for this awesome question and thank him again for heading over to http://sysadminsmith.com and submitting the question. You can also leave comments here and I will do my best to get to them all. Anyway, I knew that Powershell has a commandlet of get-credential and that this will open a windows authentication box for you to enter username and password. We do not want to have to manually enter that credential every time though. So here is what I did.:

PS C:\Documents and Settings\smithc> Read-Host ”Enter password to secure:” -AsSecureString | ConvertFrom-SecureString | Out-File c:\securepass.txt
Enter password to secure:: ********
PS C:\Documents and Settings\smithc>

So that was not to bad and here is what is in my file now:

You can see that this is not what I put in for my password. This is awesome!! And check this out. Lets say I get access to this machine and I want to know what that password is. Not only can I not read it in a text editor but even if I get Powershell to get it for me I can still not see the password. Check this out:

I love that. Anyway, now we have a password that is a secure string and we want to use that for out Credential. Here is how we do it.
$pass = get-content c:\securepass.txt | convertto-securestring
$creds = new-object -typename System.Managemnet.Automation.PSCredential -argumtents “Username”, $pass

Is that cool or what? I love it stuff….

So now here is the code with the new pieces as well as the changes in red.

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

###        Gather Disk Information     ###

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

Clear-Content “D:\Scripts\Powershell\PAC\lowdisk.txt”

$i = 0

$pass = get-content c:\securepass.txt | convertto-securestring

$creds = new-object -typename System.Managemnet.Automation.PSCredential -argumtents “Username”, $pass

$users = “email@email.com“, ” email@email.com “

$computers = Get-Content “D:\Scripts\Powershell\PAC\computerlistall.txt”

echo “ServerName        Drive Letter    Drive Size    Free Space    Percent Free” >> “D:\Scripts\Powershell\PAC\lowdisk.txt”

echo “———-        ————    ———-    ———-    ————” >> “D:\Scripts\Powershell\PAC\lowdisk.txt”

foreach ($computer in $computers)

{

$drives = Get-WmiObject -ComputerName $computer Win32_LogicalDisk -credentials $creds | 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

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

##    Determine if any disks low    ##

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

if (($ID -eq “D:”) -or ($ID -eq “S:”) -or ($ID -eq “T:”) -or ($ID -eq “C:”) -and ($free1 -lt 1))

{

echo “$computer        $ID            $size        $free        $b” >> “D:\Scripts\Powershell\PAC\lowdisk.txt”

$i++

}

}

}

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

##    Send Notification if alert $i is greater then 0         ##

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

if ($i -gt 0)

{

foreach ($user in $users)

{

echo “Sending Email notification ro $user”

$smtpServer = “smtp server”

$smtp = New-Object Net.Mail.SmtpClient($smtpServer)

$emailFrom = “fromuser@domain.com“

$subject = “Email Subject”

foreach ($line in Get-Content “D:\Scripts\Powershell\PAC\lowdisk.txt”)

{

$body += “$line `n”

}

$smtp.Send($EmailFrom,$user,$subject,$body)

$body = “”

}

}

So I hope that this answers Bryan’s question and I hope it will help out some others. Let me know if you have question or comments about this. Leave them here of check out my site at http://sysadminsmith.com.

Exchange Quota Management

One of our Mail Administrators is new to the world of Exchange as we are currently migrating from Lotus Notes. She is also new to the world of scripting but is eager to learn how to administer exchange using Powershell. I sat with her today and we walked through a simple example of how to set quotas for user mailboxes. Here is what we did:

$names = Import-Csv “path to csv file containing first and last names”

foreach($name in $names)

{

$first = $name.first

$last = $name.last

$identity = “$last, $first”

set-mailbox `”$identity`” -issuewarningquota 180mb

get-mailbox `”$identity`”

}

So let’s break this down a bit.

1. I had the mail admin get a list of all the users and put them In a csv file with the headers of first, last
2. We bring that in and assign the variables $first and $last
3. I then create the $identity variable and assign that with $last, $first so it would look like ‘Smith, John’
   1. This is so we modify the correct mailbox
4. Then I use the exchange commandlet set-mailbox and pass it the $identity variable and the parameters that I want to change.
   1. In this case only the issuewarningquota parameter is being modified.
5. Then I do a get-mailbox for the same $identity and I pipe that to format-table and look at the name and the parameter that I just modified in order to make sure that it has been set correctly.

That is it. It is so easy to make these changes to a huge number of users. If you have any questions or comments please let me know. Also if you would like to drop me a line head over to http://sysadminsmith.com and submit a question.