The Multifunctioning DBA


February 5, 2009  4:57 PM

GP Question and Answer

Colin Smith Colin Smith Profile: Colin Smith

Satish wrote me the following question:
Hi,
I would like to know that, is there any way that we ( as a normal domain user with out any admin privileges )logon to a desktop which has been added to a domain and then manage to do admin tasks like changing time ,or editing grouppolicy on the local machine.

The answer to this is basically No. Obviously, there are some ways that you can gain admin access to the local machine but that may not really help either. I will not go into the ways that one would be able to gain this access over a machine but let’s just assume that you do get your hands on a local admin account and you log in as that user. When a computer is connected to a Domain it has two sets of policies, the local computer and user policy settings and the domain computer and user policy. The Domain policy is always going to override the local policy of the machine. This means that if you do log in and change settings such as the background, the clock, or anything else that can be defined by a group policy, the policy will revert back to the domain policy as soon as the machine is able to check in with the domain and pull that policy down. So basically I am saying that changing the local policy settings will essentially have no affect on the machine if the Domain Administrators have done a good job and set up all the policies that they care about in the domain policy. The only way to change that is to modify the policy or move your Computer and or User objects to OU’s that do not have that policy applied to them in Active Directory. Unfortunately, you will need appropriate permissions to do either of those actions. Perhaps not Domain Admin rights, but an administrator would have to delegate that permission down to your domain account for you to make the changes.

I hope that makes sense and answers your question. If not, or if you have any other questions, please go to http://sysadminsmith.com and click on the Submit a Question link on the right.

February 2, 2009  4:18 PM

Oracle Statspack Help

Colin Smith Colin Smith Profile: Colin Smith

Recently I received an email from Ram. It said the following:

I am ram,I have a question regarding development DBA.I need to Optimize SQL and PL/SQL Queries.I need to analyze statspack and some knowledge transfer regarding Data Mining.Can you please help me regarding these concepts.

My company is using 9i Release 2 on an unix flavour.I can give you more information regarding this if i have any of of ur mail ID’s or chatting ID’s.

Thanks for ur time.i will be waiting for ur response.

Thanks & Regards,

Rakesh

To this I replied that I may not be able to help with every aspect of this question. I will try to do what I can. I pointed Ram to the http://itknowledgeexchange.techtarget.com/itanswers/ site in hopes that he would be able to ask the question there and get help with some of the things that I am not going to be much help with. Since I am not a developer, I am not very proficient in PL\SQL however I am learning more and more all the time. I also am not going to be able to help much with DataMining. I think, however, that I will be able to help with the statspack info. From the question I can only assume that Ram has the statspack job set up and running and collecting data. I can tell you that in my production system I have this job running every fifteen minutes and I am keeping 2 weeks worth of snaps. Here is a link to the Oracle site that will help you run the reports from the information gathered.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm

If this does not help then please let me know what you are having trouble with and I will be glad to help you out with that. Just goto http://sysadminsmith.com/ and click the Submit a Question link.

Thanks for the question Ram.


January 31, 2009  8:20 PM

Powershell Tools

Colin Smith Colin Smith Profile: Colin Smith

Powershell V1.0 does not come with any tools to write scripts. Powershell V2.0 does come with a IDE but it is not as good as some of the other third party IDE’s and V2.0 is still in CTP so you do not want to put it into production. Here are a couple links to a few that I have used and found to be pretty good. My favorite one is the Idera Powershell Plus product but download them all and try them out and then get the one that works best for you. Here are the links.

http://www.idera.com/Content/Show65.aspx

http://adminscripteditor.com/

http://www.powergui.org/index.jspa

http://www.primalscript.com/


January 30, 2009  6:01 PM

Powershell V2.0 CTP 3

Colin Smith Colin Smith Profile: Colin Smith

Powershell CTP 3 is out and ready to download and try on for size. I am downloading now and can not wait to get it installed and start playing around with it. I am looking forward to the release of V 2.0 and I really hope that they add actual useable remote functionality. I would like to have this for my SQL Server Monitoring tools that I am writting all in Powershell. This should be able to help me better manage and interact with the SQL Servers from my PDA.  Here is a link to the download and I will post more about my Powershell scripts that I use for basic SQL Server admin later on.

http://www.microsoft.com/downloads/details.aspx?FamilyID=c913aeab-d7b4-4bb1-a958-ee6d7fe307bc&displaylang=en


January 28, 2009  8:29 PM

Powershell Explained

Colin Smith Colin Smith Profile: Colin Smith

If you are using Powershell but would like a better explanation of how it all actually works then here is a great video with Jeffery Snover and he explains things very nicely. Check it out and I hope that it will help you understand Powershell and even get even more into it.

http://channel9.msdn.com/shows/Going+Deep/Expert-to-Expert-Erik-Meijer-and-Jeffrey-Snover-Inside-PowerShell/

If you have any questions please let me know at http://sysadminsmith.com and click on the Submit a question link on the right.


January 27, 2009  10:53 PM

Partitioning Tool

Colin Smith Colin Smith Profile: Colin Smith

I made a big mistake at home and only gave myself a 10 Gig partition of a 250Gig drive to be my boot drive. This was OK for a while but I did not manage the space well and before I knew it I was out of space and could not delete any more files. I found this great tool for home that is free for home use and I am sure that the other pay per versions of this are even better. All I can say is that it worked awsome for me so I thought I would let you all take a look as well.

http://www.partition-tool.com/

And come check out my site and let me know if you have any questions at http://sysadminsmith.com


January 27, 2009  8:58 PM

Learning SQL

Colin Smith Colin Smith Profile: Colin Smith

I have been a DBA for about a year now and I still have not had to really do much SQL. I do other things such as backups, restores, indexing, and other things to make sure that our databases are in good shape and that our data is safe. I thought it would be a great idea to learn some SQL though so when a developer complains about a query running slow I can take a look at the query and understand what it is doing and perhaps help them re-write the query so that it will preform better. I installed the AdventureWorks DB on my SQL Server Express instance that is running on my Desktop and I printed out a diagram of the database. I found a good site http://w3schools.com/ that has tutorials for many different proggraming languages and I have been working down the SQL Tutorial. I would not claim to be a developer but I have learned a lot and would recommend this to others that are trying to learn SQL.


January 27, 2009  8:52 PM

Disk Monitoring Q and A

Colin Smith Colin Smith Profile: Colin Smith

Mike sent me an email asking about the input file for the disk monitoring script that I posted a while back. He could not get the script to work as he was putting the hostname and the IP of the machine in the computerlistall.txt file. The script was written to have either the hostname or the IP of the machine but not both. This started me thinking that a file with both the hostname and the IP of the machine would be useful in more ways then just this script. We may be able to use this file for multiple scripts and certainly is a good idea just to manage and keep track of names and IP’s. So I did the following to accommodate Mike. I hope this helps Mike and some others out there.

Here is a screenshot of my new computerlistall.txt file. notice that I do have headers in the file. This is very important because of the way the import-csv cmdlet works.

Computerlistall.txt

Computerlistall.txt

Now that I have that as my input I have to modify the script just a bit to accommodate this format.

The arrows point out what I have changed. I put in the $computer and the $ip lines just to show you the output when doing this.

So you can see that $compuuter is now the value of the hostname and $ip is now the value of the IP for that server. I do not know about you but I think this is freaking Cool.
So if you modify your computerlistall.txt file to be a file with header values and data values seperated by commas and modify the script as I have shown you here then you should be all set. This will allow you to use either the $computer or the $ip variable in the WMI connection string. Below is the entire modified script.

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

###         Gather PAC Disk Information      ###

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

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

$i = 0

$users = “some.email@address.com”

$computers = import-csv “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 ($line in $computers)

{

$computer = $line.hostname

$ip = $line.ip

$computer

$ip

}

$drives = Get-WmiObject -ComputerName $computer Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}

foreach($drive in $drives)

{

$size1 = $drive.size / 1GB

$size1

$size = “{0:N2}” -f $size1

$size

$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++

#[char]10 | Out-File -append ./low.txt

}

}

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

##    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 = “”

}

}

Hope that helps and please drop me a line by heading over to http://sysadminsmith.com/ and clicking the Submit a Question link on the right hand side of the page. Thanks


January 22, 2009  5:24 PM

Putty Connection Manager Tool

Colin Smith Colin Smith Profile: Colin Smith

If you are working in Unix or Linux you are most likely using some tool to connect via ssh to that server for your session. Putty is a free tool that manages this very nicely. Many of you are already using Putty I am sure. If you are I did find a Putty Connection Manager that is really very nice. This allowas you to hav multiple putty sessions open as tabs. Check it out it is a very nice tool. http://puttycm.free.fr/


January 21, 2009  5:25 PM

Powershell 2.0 CTP (Transactions)

Colin Smith Colin Smith Profile: Colin Smith

As I said I am just starting to fool around with Powershell V2.0 and I ran across a cmd-let that really grabbed my attention and I went to read more about it. This is a Fanatastic Idea and I am so excited to see Powershell use this in more Providers.

Transactions, How cool is that? Just like when running a query in a database, this will run the commands as a transaction and have the ability to roll back the changes. If all of the changes that you would like to be made are not completed correctly it will rollback to the way it was before the script ran. Currently it looks as though only the registry PSProvider supports this but man I hope that changes. I can think of many scripts where this would be useful. One example is my SQL Server Monitoring tools, I have a function that goes out and changes the on-call contact. I think it would be nice for it to be all or nothing. That way you do not get two people on call if for some reason the function does not complete. I love it. Read more at http://www.microsoft.com/technet/scriptcenter/topics/winpsh/transactions.mspx


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: