Friday, February 26, 2010

Create a single table from multiple tables with the same structure in SQL

If you're looking for a way to accomplish in SQL what an append query was to Access, you're in luck. The trick is the UNION keyword. This will combine two or more tables together into one. For example, say I have two database tables. Each table contains a list of people and includes their SSN, first name, and last name. The names of these tables are people1 and people2. Rather than having to look at each of those table individually, you can simply combine them with the UNION

SELECT * FROM people1 UNION SELECT * FROM people2

That will give you a single result set that contains all distinct records from both people1 and people2. If you had more tables you could just add more UNIONs

SELECT * FROM people1 UNION SELECT * FROM people2 UNION SELECT * FROM people3 UNION ...

One drawback with this is that UNION only returns distinct records. However, if you want to see everything from the tables you're joining whether it exists in more than one table or not, simply use UNION ALL instead. This returns all results, and does not require them to be distinct from one another.

SELECT * FROM people1 UNION ALL SELECT * FROM people2

This would return all rows from both people1 and people2, and if a person was listed in both tables they would show up twice in the result set.

If you're looking for a free SQL tutorial, or just more about using UNION, check out what W3Schools.com has to offer on their site. I've used that site for everything from SQL to HTML, and feel it has been one of the more helpful resources I've run across.

Friday, February 12, 2010

My Computer opens slowly and you get a flashlight icon

I had a user complain that her Windows XP computer starting running slow, and whenever she opened My Computer she would see a flashlight moving back and forth, but the drives either wouldn't show up or would take a long time. At first I expected it to be some type of spyware or adware, but that wasn't it. A quick Google after ruling that out brought me to the solution. Windows Image Acquisition, which is a service that can run in the background to make sure your computer is ready to recognize something plugged into it, such as a camera. Disabling supposedly solves the problem, and it worked for this particular user. For more details, here's how to do it yourself:

1. Right-click on the My Computer icon and select Manage
2. Expand Services and Applications
3. Click on Services
4. In the window to the right, scroll down until you find "Windows Image Acquisition". Right-click on it and go to Properties
5. Change the Startup Type to "Disabled" and click OK
6. Close the Computer Management window that you have open

Now go and give the My Computer icon a click and see if it still gives you a flashlight icon. In the case I dealt with, as soon as the service was disabled, clicking on My Computer brought up the system drives as quickly as it should have. Hopefully you have the same result.

Tuesday, February 9, 2010

Protect yourself from phishing and spoofed email

Curiosity doesn't just kill cats. It will kill your computer too if you're not careful. This post is a bit more generic than normal, but with a recent influx in spam hitting the mail server I manage I thought this might be helpful to more than just the employees at the company I work for. 

If you want to test your knowledge about phishing, take the Sonicwall quiz. It can be found at http://www.sonicwall.com/phishing/index.html. If you get 8 or more correct, then I’d say this post probably isn’t going to be useful for you so go ahead and read a different one. Otherwise you may want to keep reading.

To put the amount of spam out there in perspective, between 50-90% of all messages sent to the mail server I manage are spam. Worldwide, spam is said to make up 80-85% of all email sent, and some say that is a conservative estimate.

The first thing to be aware of is phishing. If you don't know what phishing is, the definition according to Wikipedia is "the process of attempting to acquire sensitive information ... by masquerading as a trustworthy entity in an electronic communication". This happens a lot, and if you have an email address you likely have received at least one phishing email. It’s easy to mask the actual site address with something that looks legit. Here is an example. At first glance it looks like I put two links to the Wells Fargo home page, but can you tell what’s wrong with them?



The top link takes you to Wells Fargo, just like you’d expect. The second link will bring you to the Google homepage, even though it looks like it should go to wellsfargo.com. Checking where a hyperlink goes before clicking on it is a good practice to adopt because it can lead to safer web browsing and instant messaging. It’s also a great way to avoid getting any type of viruses or malware. There should never be a link to files that end with .exe, .msi, or any other executable file extension unless you are expecting it from a software vendor. If you accidentally click the link, you should not agree to let anything be installed if you get prompted by the Run/Save/Cancel box. Normally to see where a link goes you can either hover over it with the mouse to see it displayed as a small information box, or hover over it and check the status bar at the bottom of the window. You can try it with the wellsfargo.com links above.

Here’s another one (not a real page, just an example):

http://www.paypal.com-account.login.mywebsite.com/login.asp

Looking at it quickly, you may think this takes you to paypal.com’s login page. However, the secret is to pay attention to the periods and forward-slashes /. This will actually direct you to somewhere on mywebsite.com. Everything else is just fluff to redirect you to a particular page, and the www.paypal.com part is used to make the link look legit. If it had been http://www.paypal.com/account.login.mywebsite.com/login.asp, this would be a page within the paypal.com site because of the / after paypal.com. Technically this URL could still be a problem, but this would only occur if the computer you were using was either severely infected, or if someone hacked Paypal.com itself, both of which are unlikely to go unnoticed.

For more information about Phishing check out any of the following links:



Spoofing is also very common, and I bet almost all of you have some experience with it. Spoofing is the practice of impersonating another email address, which then makes it look like someone else sent the message. If you ever check your Postini junk mailbox you’ll probably find emails in there with your own email address, but the content in the email is about prescription drugs or some other fake ad. This is classic spoofing. It’s very easy to do, and really hard to prevent. The reason it’s difficult to control is that the safety of your email address doesn’t just depend on you, but rather anyone you’ve ever sent an email too. If any of them happen to get infected by some type of malicious software that steals their address book or email addresses, your email is then easily spoofable. It’s so simple to do, practically any computer could be setup to spoof addresses from anyone. There are certain things that can be done on a mail server to detect and reject spoofed messages, but it’s not foolproof and still requires users to follow email best practices. The main thing to remember with spoofing is that just because an email says it’s from someone you trust, that does not make it legitimate. For more about spoofing, check out its Wikipedia entry

Here is the quick list of the 3 most important things to remember when it comes to safe email habits:

1. Before you click on anything, make sure it’s directing you to where you expect to go
2. If you don’t know what something is, don’t click it regardless of who it says it’s from. This goes for both attachments and links
3. Use your best judgment when deciding whether an email is legit or not. If it looks suspicious, it probably is. Also, don’t automatically trust the name in the From field because it could easily have been spoofed


Of course, you should also be running some type of anti-virus and possibly anti-spyware software on your computer to prevent infections as well. If you don't already have something, check out one of my older posts about some free software packages that will help keep your computer from getting infected.

Monday, February 8, 2010

Change "Out of Office" message for another user

This allows you to change the "Out Of Office" message for another user, and also specify other settings for them, without the need to know their password. This requires that you have Outlook Web Access enabled (OWA for short), and of course you have to have access to the other user's account. The specific permissions needed would be "Read permissions" and "Full mailbox access", which can be set for the user in the Exchange Advanced tab in their Active Directory account properties. The default Administrator account likely was granted these permissions by default as well, so if you have that account enabled and would rather use that to login that should work too.

Doing this is surprisingly simple, and much easier than resetting the user's password so you can access their account. First, login to your OWA account with the user account that has mailbox access to the account you want to modify. Once logged in, in the URL, add /username/Inbox to the end of it. For an example, when I login as the administrator, the URL of my Inbox is https://owa.mydomain.com/exchange. If I want to change the account for a user named User1, then I would change the URL to be https://owa.mydomain.com/exchange/User1/Inbox and then go to that page. That should take you to User1's Inbox via OWA, and from there you can go into the Options and set the Out Of Office assistant to respond however you need it to.

This works for accounts that are disabled as well, without the need to re-enable them. This can be very helpful if you want some type of auto-reply for users that are no longer at the company you work for without having to get into server-based rules. Thanks go to lazynetworkadmin.com for their post about the same thing