Tip: Extract raw data from your Spiceworks database using Excel

I wrote this quick article yesterday and posted in the Spiceworks forums - thought you SW users here would get some use out of it!
Rob



This article describes how you can use Excel (or any program that uses ODBC) to extract the raw data from the Spiceworks database.

This document covers Windows, but the same concepts apply when using Linux or any other OS capable of running a SQLite3 ODBC connection.

Requirements:

• Spiceworks (any version)
• Windows OS
• Microsoft Excel or other Spreadsheet program
• File level access/permissions to the Spiceworks_prod.db file

	Download and install the SQLite ODBC driver Go to http://www.ch-werner.de/sqliteodbc/ and download and install sqliteodbc.exe (current version). Accept all default options. Linux versions of the driver can also be found on this page.
	Configure your ODBC driver a.) Open your Data Sources (ODBC) administrator from your administrative tools folder. b.) Click on the 'System DSN' tab. c.) Click on 'Add...' d.) Scroll down the available ODBC drivers listing and find 'SQLite3 ODBC Driver'. Highlight this entry and click 'Finish'. e.) At the Configuration screen, give your connection a name (in this example, we'll call it 'Spiceworks', although you can name it whatever you want). f.) Next, type the fully resolved path to the Spiceoworks_prod.db file or click the 'Browse' button to browse to the network folder where your Spiceworks database is stored. Note that you will require file-level permissions to access the database. g.) Leave all other settings as default, click 'OK' to finish up the configuration. h.) Close out of the ODBC Data Sources applet.
	Open Excel or Spreadsheet software of choice Since we are using Excel 2003 as an example, your options may/will differ depending on what spreadsheet application you use. a.) Open Excel and create a new blank spreadsheet. b.) Click on 'Data'> 'Import External Data'> 'New Database Query'. You will be presented with the 'Choose Data Source' dialog. c.) Find the 'Spiceworks' connection (or whatever name you gave your connection in step 2.), click on it, and click 'OK'. d.) You will see an error stating that "The datasource contains no visible tables", and will be presented with a Query Wizard. Click on the 'Options' button. e.) You should now see the 'Table Options' dialog. Clear all checkboxes with exception to 'Tables', then click 'OK'. You should now see all the tables listed in the left-hand column. Now you can choose which tables and columns you wish to query! If you want to use MSQuery to build your SQL, you can cancel out of the Query Wizard, and click 'Yes' when asked if "...you want to continue editing this query in Microsoft Query".

Exchange admin tip: Create a query-based distribution group

Requirements:

• Windows Active Directory
• Exchange server 2003 or higher

Do you have distribution groups or lists that you are maintaining constantly? Are they department or organization-based groups? Perhaps you have organized your OU structure within Active Directory according to department or logical business units?

If your answers were "yes" or you are just plain curious...then here is a really handy way to create virtually maintenance-free distribution groups in Exchange. The only thing you would need to do is make sure that your users are located in their proper OU structure in AD so they automatically become members of the group you are creating.

Here's the information from Microsoft:

To create a query-based distribution group

1. In Active Directory Users and Computers, in the console tree, right-click the container where you want to create the query-based distribution group, point to New, and then click Query-based Distribution Group.

2. In Query-based Distribution Group name, type a name for the query-based distribution group, and then click Next.

3. Under Apply filter to recipients in and below, verify that the parent container shown is the one that you want the query-based distribution group to be run against. If this is not the correct container, click Change to select another container.

   Note:
   The query returns only recipients in the selected container and its child containers. To get the results that you want, you may have to select a parent container or create multiple queries.

4. Under Filter, select one of the following options:

   • To filter the query based on a set of predefined criteria, click Include in this query-based distribution group, and then select from the following criteria:
     - Users with Exchange mailboxes
     - Users with external e-mail addresses
     - Groups that are mail-enabled
     - Contacts with external e-mail addresses
     - Public folders that are mail-enabled
     
   • To create your own criteria for the query, click Customize filter, and then click Customize.
     

5. Click Next to see a summary of the query-based distribution group that you are about to create.

6. Click Finish to create the query-based distribution group.

   The new query-based distribution group appears under the container that you selected in Step 3.

So, I've created an LDAP filter/query that picks up users that are located in an OU (in my case, an OU that denotes a physical location, "State Street").

Here is the query that I've created:

(&(!cn=SystemMailbox{*})(& (mailnickname=*) (| (&(objectCategory=person)(objectClass=user)(|(homeMDB=*)(msExchHomeServerName=*))) )))

Don't worry, you create these queries on the fly and isn't as complex as it looks above.

But...you can create compound filters if you want to get really crazy.

Excluding users from a query:

I created a filter that excluded our physicians here in town (the last part of the query excludes an account called helpdesk from the distribution list):

(&(!cn=SystemMailbox{*})(&(&(&(|(&(objectCategory=person)(objectSid=*)(!samAccountType:1.2.840.113556.1.4.804:=3))(&(objectCategory=person)(!objectSid=*))(&(objectCategory=group)(groupType:1.2.840.113556.1.4.804:=14)))(& (mailnickname=*) (| (&(objectCategory=person)(objectClass=user)(|(homeMDB=*)(msExchHomeServerName=*))) )))(objectCategory=user)(!description=Physician*)(!samAccountName=helpdesk))))

So, the benefit? First, each distribution list gets their own SMTP email address - and, as long as my users appear in those OU's, my distribution lists are always up to date!

Keeping your install packages updated: Ketarin 0.9.9.21

Ketarin 0.9.9.21
http://ketarin.canneverbe.com/

As a tech guy, I get lots of requests for house calls, and I have a trusty USB key loaded up with software ready to go and fix all those issues that everyone seems to run into...

So, a few weeks ago, I took notice of a Lifehacker article about Ketarin (and darn it, I missed the original Rarst.net article here), an install package updater.

Ketarin is a program that will download your favorite applications to a USB drive or other folder structure - but that's not all. It will also check to find updated versions of the applications you configure so you can be sure that you have the latest copy of CCleaner, VNC, or what-have-you.

Ketarin is a definite must-have for anyone who carries a drive filled with software at the ready for any support call.

Using Ketarin is very simple - especially if you are using a built-in feature which pulls your favorite apps directly from filehippo.com. You can also set it to pull applications from other sites using some of the built-in variables.

You can tweak your settings to allow more than 2 (default) downloads at a time, and also some other general options (proxy, update at app startup, etc.). Also, you can enter a command to run after your application download - for example, perhaps you want to unzip a downloaded file...

This app is a wonderful idea, and it really is a time-saver for those of us who keep a repository for our house calls.

You can also export your settings so you can share your favorite apps with others (needless to say, there is an import function). Ketarin can operate via command line, and as mentioned before, does support a variety of specialized variables. Certainly a very powerful app wrapped up in this tiny package.

Enhance your mobile's voicemail (and a bonus for BlackBerry users): Youmail

Youmail
http://www.youmail.com/

Cell phone voicemail is a pretty standard thing. You receive a call. Maybe you don't pick it up or can't get to the phone, and just let it go to voicemail. Later on you might get a few more calls and subsequently, a few more voicemails.

With the standard voicemail for cellular users, a person would dial up their voicemail, listen to each message (or at least skip through the ones they don't want to listen to) - i.e. in a linear fashion...you can't easily know who left you a voicemail, nor can you easily skip to the ones that are most important to you.

If you've recently purchased a Verizon phone within the last year or so, you've probably noticed the Verizon Visual Voicemail application, which allows you to listen to the voicemail you want in the order you want. Not only that, but you get a visual identifier letting you know who left that voicemail, and at what time.

image courtesy of BoyGenius Report

Nice, but you need to pay a $2.99 fee per month (which I admit is not too bad). But...now you don't have to pay that fee if you sign up for Youmail.

Youmail is an online voicemail service that allows you to have your own visual voicemail - all through an email, SMS or an online interface.

an example of an email notification

To set up Youmail, you go to their website, follow the onscreen instructions, and by the time you are finished, you should be able to call your cell-phone, have it forward to Youmail's services and get a voicemail notifier via SMS or email (and also reprogram your phone to dial Youmail's number from your VM shortcut key via telephone if you don't have a data plan for your phone).

What you can do with Youmail:

• Visual voicemail via phone (SMS), email, or online
• Custom greetings for your callers
• Voicemail filtering (got a harrassing caller, don't give them the opportunity to leave voicemail!)
• Voice-to-text (paid option)
• Show missed callers (even if they don't leave a message)
• Import your contacts from gmail, yahoo or an imported contacts file (csv)
  

I have to say, setting up Youmail was extremely easy, you just need to make sure you follow all the instructions (Dave!).

One thing I really liked was the ability to send a voicemail easily to an email recipient...making it even easier to make fun of that misunderstood or confused message that someone left you.

Also, Youmail gives you the ability to share and embed your favorite voicemails onto a web page (although I'm not sure why I would do that...maybe some of you have crazier friends than I do).'

I sent this site on to my boss, and he uses U.S. Cellular as his provider. He stated his number wasn't supported, although I did not see any information stating there was a carrier limitation...it could be possible that your service may not work. Let us know in the comments!

The only downside:

If you don't have SMS texting or a data plan (to receive email notifications) on your phone, then there is no real way to be notified on your device that you have a new voicemail. So, if this is the case, then Youmail probably isn't for you.

Also, if you have a limited texting plan, be aware you will be charged for each notifcation per your carrier's billing plan. Thankfully, you can opt out of the text messages and get just email notifications.

A bonus for BlackBerry users:

If you have a BlackBerry as I do - you can install a custom built application (much like the Verizon Visual Voicemail app, although not as flashy) called 'VyMail' by Joe Krill. He says it is in 'very early beta stages', but I've not had any real problem with it, other than it does not yet support speaker phone...i.e. you have to listen to the messages from the handset speaker (this issue is with the Storm only).

Website: http://www.joekrill.com/vymail/
OTA link: http://www.joekrill.com/vymail/ota/

Old school command tip: Using the ren (rename) command effectively

Admittedly, some younger users out there may have never had a chance to work in the old command window that much (aka 'DOS' for us old-timers) in Windows, but it can be a very rewarding experience if you know what you are doing.

The command-line has some very powerful ways for you to perform various system functions, and should not be underestimated, especially if you get into batch files.

The 'ren' (rename) command is very powerful.

Simply put, you use ren to rename a file from one name/extension to another. But, you can also use ren to rename groupings of files.

For example, to rename a file:

ren temp.txt test.old --> test.old


Rename a group of files, giving a suffix to every file:


ren *.bmp *_suffix.bmp

So, if you had a filename called 'leaf.bmp', it would now be renamed to 'leaf.bmp_suffix.bmp'.

Removing the extension from all like files:

Don't like the extra extension? You could have done this:

ren *.bmp *.

This would essentially remove the extension from all .bmp files.

Then you could re-run the suffix rename above, giving you 'leaf_suffix.bmp'.

Renaming with a prefix:
Renaming with a prefix isn't quite the same.

For example, if you ran this command:

ren *.bmp prefix*.bmp

You would see something like this:

testfile.bmp --> prefixe.bmp
testfile003.bmp --> prefixe003.bmp
test04.bmp --> prefix.bmp
vnc.bmp --> "A duplicate file name exists, or the file cannot be found"

Note that the third filename 'test04' is the same length as the word 'prefix', so it just renamed itself to 'prefix.bmp'. However, the fourth filename is less than the total characters found in the word 'prefix'. If we were renaming only this file, it would come out as 'prefix.bmp', but since we already have a file of that name, we can't rename this file.

So, the "ability" to renaming files easily to give it a prefix is rather underwhelming, but you can do something similar in Windows - see my tip about renaming multiple picture files here. Not to mention that you could write a batch file to do some tricky stuff as well.

Got any other nifty file rename tricks (No cheating! Only built-in commands to Windows allowed!)? Post them here!

Partition your drive while in Windows: Easus Partition Manager 3.0

Easus Partition Manager 3.0
http://www.partition-tool.com/personal.htm


Free for personal use, Easus Partition Manager is a viable alternative for Symantec's Partition Magic. Actually, it looks and functions nearly identically to version 8.04 (the last version of Partition Magic that I've used).

Some of the main partition functions:

• format
• resize
• delete
• add
• copy
• set active
• change drive letters

There's really not much to say about Partition Manager...it just works. When you apply your changes, PM will restart your computer, then make changes during the Windows boot process (prior to getting into the Windows GUI). Partitioning actions occur quickly, and provides you with easy to read/understand details.

PM supports Windows 2000, XP and Vista (32 bit) versions and drives up to 1.5TB in size.

It appears that PM can be used as a plugin for the Bart-PE boot CD! Very nice.

It would be nice if there was the ability to convert the partition between FAT/FAT32/NTFS (or what is allowed by filesystem restrictions)...

It's great to know that there is a great free alternative to the Norton monopoly!

Share your media to any computer or mobile: Orb 2.01.0013

Orb 2.01.0013
http://www.orb.com

Do you get that itch for that favorite TV show you missed while on vacation, or perhaps you had a photo that you wanted to show your family, but forgot it was on your computer at home? Maybe you wanted to stream your own music since you just can't find that Internet radio station that you like.

There are some devices you can go out and buy that allows you to access your media from a remote location (Sling Box, for example), but if you have a computer, why spend that money when you have something already which is much more powerful?

So, If any of the aforementioned scenarios fits your bill, then you need to try Orb. Orb is a multimedia sharing app that allows you to access your music, videos, recorded TV, pictures and documents/files from anywhere you have a network/Internet connection.

The real beauty of Orb is the fact that you can create custom feeds for your friends (or yourself) and listen/watch them at your leisure...making it possible to be your own DJ.

Share to yourself or to anyone else via a huge variety of connected devices:

• Mobile devices - iPhone, 3Gp capable mobile, BlackBerry, other PDA etc.
• Gaming/Entertainment systems - XBox 360, Wii, PS3
• Computers - PC, Mac

You can also share your media and notify your friends (they must have a free Orb account) by email, RSS, or a personalized Orb page!

Configuring the Orb desktop app is as easy as it gets. Simply add or remove whichever folders you want Orb to monitor when cataloging your media.

Note the TV tab. Orb supposedly has the capability to utilize your Cable/Tuner so you can stream live television - - I've had some issues getting the downloaded TV listings to work (but haven't had time to really troubleshoot).

You can add or remove Orb users from this interface, in addition to tweaking startup and other parameters for the program.

Access your files anywhere

Perhaps the most powerful aspect of Orb (at least for me) is the ability to download your documents (or any file, really). Very powerful indeed. What would be cool here is an interface to an online office application like Google's or Microsoft's offering for inline editing. So, that means currently, when you want to access or edit a file, you must download it first - not a huge deal if the file is small. Thankfully, Orb offers you the capability of zipping the file prior to download.

Did you say mobile devices, too?

Yup. Orb has the capability of streaming media to your phone. I've personally tested it on my Storm using 3Gp, although I wasn't able to get the videos to work right as of yet (but I've read some online forums stating that others have had good experiences - I was getting audio only, which was still pretty cool). Normal audio worked awesome. Basically, when you stream your media to your phone, I noticed that it gets treated just like an Internet radio station: You can't skip ahead or back since it is technically a live stream. Has anyone else had a different experience?

The iPhone has a specialized application that has been built specifically for the device - so it isn't a web interface (like on the BlackBerry, for example)...and for this reason it isn't free. You have to go into the App Store and purchase it from there.

Photos

You can create slide shows of your shared photos, or view them one by one - handy if you're at Grandma's house and you forgot to bring that photo album with you.

Interface

Overall, the interface follows a very easy-to-use standard dual pane explorer-like window. The mobile interface is a bit more like a message board layout (for lack of a better analogy).