Windows Administration

Powershell Remove files

I have a server that attempts to backup up the SQL Server databases and it always fails because not enough space is available. This usually is not a big deal as I would run a SQL Clean up task before the backups run to remove the old files. In this case though I am using a third party utility to run the backups and it does not have that functionality. So, since I am no sqljockey yet, I have chosen Powershell as my tool. Here is what I have done.

$days = [datetime]::now.adddays(-7)
E:
cd \
cd Directory
ls | where {$_.name -like "*.bak"} | rm -force
cd tranlogs
ls | where {$_.name -like "*trn"} | rm -force
cd ../../Directory
ls | where {$_.name -like "*.bak"} | rm -force
cd tranlogs
ls | where {$_.name -like "*bak"} | rm -force
ls | where {$_.name -like "*txt"} | where{$_.creationtime -lt $days} | rm -force
c:

You can see that I simple cd to the directory that I have my backupfiles in and do an rm or Remove-object. I am also keeping 7 days worth of the txt log files that the backups create. Just in case I need them. Hope that helps. Enjoy

Random Password Script Powershell

I was recently asked if I could write a script that would create a random password based on a word list. The password needs to have a capital letter, a special character, and a number. The password also has to be made up of two seperate words that are in the word list.

my words.csv file is formatted as so

Number, Word

Here is what I did.

$symbols = “(”, “!”, “@”, “#”, “$”, “%”, “^”, “&”, “*”, “_”, “+”, “=”, “?”, “/”, “~”, “;”, “:”, “,”, “<”, “>”, “\”, “)”, “.”
$words = Import-Csv “\\path to word list\words.csv”
$max = $words.Count

$first = New-Object system.Random
$1value = $first.next(0, $max)
$firstword = $words[$1value].word
$firstword = $firstword.toupper()

Start-Sleep -milliseconds 20
$second = New-Object system.Random
$2value = $second.next(1, 23)
$special = $symbols[$2value]

Start-Sleep -milliseconds 300
$third = New-Object system.Random
$3value = $third.next(0, $max)
$secondword = $words[$3value].word

Start-Sleep -milliseconds 230
$num = New-Object system.Random
$4value = $num.next(0, $max)
$4value = $words[$4value].number

$newpass = “$firstword$special$secondword$4value”

Echo “`n”
$newpass
Echo “`n”

So as you can see I am using the word list to pull the two words as well as the number value. In order to get my capital I am using the toupper() function on the first word, and for the special character I create an array of characters and then pick one to use.

Any comments or questions please let me know.

More with Quest AD Powershell CMDLETS

I am continuing work on the script that I am converting from VBScript to Powershell and I must say that it is going quite well with the help of the Quest cmdlets. In the script I want to go through a particular OU and delete any accounts that are currntly disabled, and were created a minimum of 180 days ago, and have not been used in a minimum of 180 days. I can do this with the following block of code.

$deletedays = - 180
$deletedate = [datetime]::Now.AddDays($deletedays)

Get-QADUser -SearchRoot “pni.us.ad.gannett.com/PNI/Users/Disabled” | where{(($_.lastlogontimestamp.value -lt $deletedate) -and ($_.creationdate -lt $deletedate) -and ($_.AccountIsDisabled -eq “True”))}  | Tee-Object -filepath “c:\removedaccounts.txt” | Remove-QADObject -Force

So you will also notice that I am using the Tee-Object cmdlet. This is not a quest cmdlet but it is nice as I can log what accounts I am deleting with the Remove-QADObject cmdlet that is provided by Quest. Be careful when doing things like removing accounts in scripts and be sure to test completly. A good way to test is to use the -whatif clause. This will show you what would happen if you did run it.

Get List of all DC’s in your Domain

I am working on converting a vbscript that I wrote a couple of years ago into a powershell script. This script requires that I query all of the Domain Controllers in my domain to get the most up to date data that is possible. I used the Quest AD Commanlets and they made it easy. I have discussed these in the past and if you have not gotten them yet then go get them. They are at the following link:

 http://www.quest.com/powershell/activero…

to get a listing of your DC’s just do the following.

$dcs = Get-QADComputer -ComputerRole DomainController

now you have a listing of them in the $dcs variable and you can scan them all.

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.

SQL Ping Servers Improved

Not long ago I did a post about pinging the interface of a server since you may encounter a problem connecting to WMI even if a server is up. The article is at http://itknowledgeexchange.techtarget.com/dba/powershell-ping/.

So Like I describe in that post I did not want our DBA team to be paged about a SQL Server being down when it was really an issue with the host server. In that case I would like the Windows Administration team to be notified so that they can take care of the issue. I also wanted to check on some other things about the server. I wanted to check the physical interface, check that the agent service is running, and also verify that connections to the database can be made. I have finished the script to do this and I will go over it with you a function at a time. First we will look at the Main function. This is where everything gets set up.

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

####### Start Script ############

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

$ErrorActionPreference
=
“SilentlyContinue”

$time
=
Get-Date
-displayhint
time

$date
=
date

New-PSDrive
i
-PSProvider
filesystem
-Root \\SomeServer\SomeShare

if (test-path
“i:\OUT\ping_with_service_check.txt”)

{

Clear-Content
-path
“i:\OUT\ping_with_service_check.txt”

}

##Read in List of SQL Servers And set up all needed variables for script

$NTusers
=
“phx.it.systems.nt@pni.com”

$servers
=
Import-Csv
-Path
“i:\inputs\sqltab.txt”

foreach ($server
in
$servers)

{

$machine
=
$server.server

$instance
=
$server.instance

$torp
=
$server.torp

$ping
=
$server.ping

if ($instance
-eq
“NULL”)

{

$SqlServer
=
$server.server

$folder
=
$server.server

$sqlagent
=
“sqlserveragent”

$sqlserverservice
=
“mssqlserver”

}

else

{

$SqlServer
=
“$machine\$instance”

$folder
=
“$machine-$instance”

$sqlagent
=
“sqlagent`$$instance”

$sqlserverservice
=
“mssql`$$instance”

}

echo
“`n############################”

echo
“`n############################” >> “i:\OUT\ping_with_service_check.txt”

$machine >> “i:\OUT\ping_with_service_check.txt”

$machine

if ($ping
-eq
“Y”)

{

if (test-path
“i:\$folder\wmi_Failure.txt”)

{

$wmierror
= 1

$wminoemail
= 1

$wmifile
=
Get-ChildItem
“i:\$folder”
|
where{$_.name
-eq
“wmi_failure.txt”}

$filetime
=
$wmifile.lastwritetime

if($filetime-le
$date.AddDays(-1))

{

del
“i:\$folder\wmi_Failure.txt”

}

}

if (!(test-path
“i:\$folder\wmi_Failure.txt”))

{

$wmierror
= 0

$wminoemail
= 0

}

if (test-path
“i:\$folder\ping_Failure.txt”)

{

$pingfail
= 1

$pingnoemail
= 1

$pingfile
=
Get-ChildItem
“i:\$folder”
|
where{$_.name
-eq
“ping_failure.txt”}

$pingfiletime
=
$pingfile.lastwritetime

$pingfiletime

if ($pingfiletime
-le
$date.AddHours(-6))

{

echo
“File was created more than 1 minute ago. $pingfiletime”

del
“i:\$folder\ping_failure.txt”

}

}

if (!(test-path
“i:\$folder\ping_Failure.txt”))

{

$pingfail
= 0

$pingnoemail
= 0

}

if (test-path
“i:\$folder\agent_Failure.txt”)

{

$agentfail
= 1

}

if (!(test-path
“i:\$folder\agent_Failure.txt”))

{

$agentfail
= 0

}

Ping_Interface

}

if ($ping
-eq
“N”)

{

echo
“$sqlserver - is not being pinged at this time. Check SQLTAB” >> i:\OUT\ping_with_service_check.txt

echo
“############################” >> “i:\OUT\ping_with_service_check.txt”

}

}

Ok so you can see from looking at the script that this is where the script starts even though, you will see when you see the entire script, that this is the end of the script. That is because in Powershell you must define all functions before the script begins.

As you can see I start out by setting some things upand clearing out my log file. You could just keep appending to the file but I chose not to. You can also see that I set up a variable called $servers. This is populated by a .csv file that I have that has all the information about my SQL Servers that I am going to need for this script and for my other monitoring script. The heading of that file is as follows.

Monitor,Server,Instance,TorP,ErrorLog,Ping,OS2000

This way I know if I want to monitor the server, I may choose not to if it is out of commission for a while, I know the hostname, instance name, if it is Test or Production, if I want to run this Ping Servers Script on it and if it is running Windows Server 2000 as I can not run powershell locally on those servers.

Now that I have that variable populated I can start my loop. I start setting up each individual variable that I am going to need later on. Now I start looking for some touch files called wmi_failure.txt and ping_failure.txt. These are so I will know if either a wmi call or a physical interface ping failed on the last run of the script. On both of these I do not want to send out notification every 10 minutes but for wmi failures just once a day and for interface failures once every 6 hours. Now you may be asking why only every 6 hours if a servers interface is down since that means the server is basically useless. I would agree with you, but remember I wanted to notify our Windows Team about this issue and this was there call. No pages and only email us once every 6 hours about the issue. I was shocked at this response. Anyway, I look at the file if it is there and then I check how old it is based on the current time of the script running. If it has been passed the allotted amount of time then I remove that file and set the failure variables to 0 so the script will know to send out notifications about the failures should they still occur. Once I set all that up I can call my firs function if this script should be run against the server. That is noted by $ping being equal to Y or N. Next time I will show you my Ping_Interface function.

If you have any questions or comments about this script please let me know at http://sysadminsmith.com and click the ‘Submit a Question’ link to the right.

Powershell Ping

I have a script that goes out and pings all of my SQL Servers. This script, right now, simply makes a connection to the SQL Server instance. If I am able to connect I call it good and if not I send out notifications saying that the SQL Server is down. I found that more often than not I was getting these alerts because of some issue with the OS. This could be that the network interface was down, the server was powered off, or a number of other issues that could impact connecting to the SQL Server instance. My team usually ended up looking into it and having to call one of our Windows Systems Administrators in order to get the issue corrected.

The DBA Team did not really like that we got notified on OS issues before the Windows Team. The Windows Team was happy that we were letting them know of issues but they do not really like that the DBA team sees the problem before they do either. Because of this I decided to redo the SQL Ping Servers thing that I am going to do is to ping the servers interface and make sure that the server is up and responsive. If it is not then I as a DBA will not really be able to do anything anyway so if the ping fails I will just notify the Windows team directly. To do this Powershell needs to connect to WMI and look at the win32_pingstatus like so.

$pingresult
=
Get-WmiObject
win32_pingstatus
-f
“address=’$machine’”

This does work well but I ran into another issue. What if WMI of a server is not working for some reason? I have had this issue come up and then it may look like I cannot ping the physical interface on a server when in fact I can. I thought that I need a more effective way just to test the PING. Why not just use good old ping. So I did the following and it works great.

$pingresult
=
ping
pni-vmdbasql
-n 1

$pingresult

if ($pingresult
-like
“*(0% loss*”)

{Echo
“Ping Success”}

if (($pingresult
-like
“*100% loss*”) -or ($pingresult
-like
“*could not find host*”))

{echo
“Ping Failed notify Systems Team”}

This way I can test the actual ping of the server. If the hostname cannot be resolved or if my loss is 100% then I consider this a failed test and I will notify the Windows Team. If I have 0% loss then I can move to my next function and continue with my basic health check of the SQL Server.

I will but more about this PING SQL Server script out when I have completed it. I just thought this was a very important distinction to make you aware of.

Any questions please head over to http://www.sysadminsmith.com and click on the ‘Submit a Question’ link to the right.

Script and incorporate some more steps in it that will better determine what the cause of the issue is and will notify the appropriate team.

The first

Scripting

So I have people ask me all the time how I can get so much done in a day. I have to be honest with you, I do not really do that much. This is because I write scripts to do anything and everything for me if I have to do it more than once I script it. If I think something might be useful I script it. I hate doing the same work over and over. Take something as simple as creating a new user on a domain. This is a very simple task in AD but it takes about 2 minutes per account. It is easy to find a script that will create mass accounts with some generic name. Why not take that same script and make it so it creates one account at a time or ten or whatever you need. You can make it so you type in the name of the user interactively or read names in from a list that you have. Simple things like that. Creating this account only takes 2 minutes in AD but I can do it in under 10 seconds.

When you work in the fast paced world like we do, especially in IT where everyone wants it now now now, every second counts. Saving just under 2 minutes does not seem like a lot but that is the just the beginning. If you save 5 minutes here and 10 there and 2 there then it adds up very fast so you can leave work an hour early, or you just make your boss think that you are that much better and more effective. It does not really matter if you are a Windows person or a Unix/Linux person. SCRIPT anything you can and save time.

I come from a Windows background and in this area the Unix/Linux admins are years ahead of the Windows users. I had done some automation using VBScript when I was a Windows Admin, but when I got into Unix for Database Admin, I quickly learned that scripting is the way. Now with Windows Powershell, Windows administrators can be much more effective in less time. Please learn a scripting language that is of use to you and that you understand. I prefer Powershell but VBscript is a good way to go if you like it better.

Good luck scripting and as always, if you have a question let me know by heading to http://sysadminsmith.com and click the ‘Submit a Question’ link on the right.

Free Space or Mount Points

I had a question about getting free space of mount points on a windows server. The user had some Exchange servers with a huge chunk of san disk attached. Then he mounted drive letters into empty folders on that disk. This gave him multiple mount points on that one volume. Now he wants to be able to report on each mount point individually. I do not have any servers that have been set up this way and I am out of the office all week. I did find this post in anther forum though.

PS C:\WINDOWS> gwmi win32_volume|where-object {$_.filesystem -match “ntfs”}|ft name,capacity,freespace

name capacity freespace

—- ——– ———

C:\ 20973137920 12781355008

D:\ 88266911744 30121046016

E:\ 36410552320 11617628160

D:\Mounts\D02\ 1924139716608 1785309392896

D:\Mounts\D03\ 1099506044928 1016254210048

D:\Data\Shared\D01\ 2186130853888 697559801856

And here is a link to the forum that I found it. I have not attempted this as I do not have any servers with this type of mount point but try it and I hope that it works. I am not sure if it will though since win32_logical disk, I do not think it will report like this. I also know that you can try win32_mountpoint and this will get you a list of all your mounts. You may be able to do some work and math using that to figure out the free space. I will try to set this up once I return to the office and see if I can work something out if this does not work.