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!