Nerd Hobbyist: A 10 cent Web Interface on AD LDAP Queries

The Goal

Provide a web interface (web page) to create, save and execute Active Directory LDAP queries within a domain environment.  This version is predicated on using ASP, rather than ASP.NET.  This is sad and unfortunate, but it works.  I plan to post an update to this using ASP.NET, focused on "zero-cost" as the main constraint (i.e. no purchases required beyond the base operating system stuff).

Also, rather than just posting the code, I'm going to describe the process and the steps.  Otherwise, you will do the lazy thing (like I would) and just ignore my rambling and download it.  Then I'd get a ton of emails from you asking why something didn't work as you expected.

Can this be accomplished by other means?  Absolutely.  Are those other means easier to implement?  Maybe.  Are those other means cool?  Maybe.  Does Dave care?  Not really.

As for ASP: if you one of those folks who are already turning your nose up at this choice, note that there are still a shit-ton (that's a real number, trust me) of web pages still being served up via ASP, even on Microsoft web sites. So: nah-nah-nee-boo-boo.  So, for now, relax, drink up and enjoy my little bus stop cooking show.  Cheers!

The Ingredients

• An Active Directory network environment (preferably 2008 domain-level or higher)
• An IIS host (physical or virtual, server or client version).
• A dedicated AD account for use with executing the queries.
• A database for storing your precious LDAP queries in.
• A tablespoon of ASP (vbscript) code and your favorite text/code editor application.
• A few cups of coffee

The Recipe - Database Cookie Dough:

1. If you don't have a SQL Server database server at your disposal, then install SQL Server Express (2012 is preferred, or whatever is the newest and shiniest).
2. Create a database (preferred, to keep it separate from other databases).  Suggested name: adqueries.
3. Create a table in that database.  Suggested name: reports.  Figure 1 (below) shows a variation on this with some additional columns.
4. Create the following minimum columns:  ID, ReportName, Comment, QueryText
5. Make ID type = Int, Identity(1,1) and primary key (not null).  Make ReportName type=varchar(255) (not null), Comment type=varchar(255) (null), and QueryText type=varchar(255) (not null).
6. Grant permissions to the database and the table for however you prefer your spiffy web app to interact with it.  Integrated Security is fine, as is SQL security with an internal SQL account.  You can use text-book "role" based access management, but whatever you do, just make sure to grant access within SQL Server to allow that account to make a remote connection.

Figure 1 - Example Table Structure

For purposes of saving time, go ahead and manually stuff a few example reports/queries into the table.  That way when you finish your coffee and the first page (see below) you will have some sample rows to validate your web site interface to the database.

Figure 2 - Sample reports

Example queries/reports could be some really basic stuff like:

• Report 1
• ReportName= "Computers - Windows Server 2012"
• Comment = "Domain computers running Windows Server 2012"
• QueryText = "objectCategory='computer' and operatingSystem='Windows Server 2012*'"
• Report 2
• ReportName = "Computers - Windows 8"
• Comment = "Domain computers running Windows 8.x"
• QueryText = "objectCategory='computer' and operatingSystem='Windows 8*'"

The Web Sauce:

1. Create a folder on your chosen web host.  This can be a Windows 7 or 8 computer, or a Windows Server 2008...2012 computer.  As long as it's a member of your target AD domain.  Suggested path/name: c:\inetpub\wwwroot\adqueries.
2. Create a web site virtual folder linked to that shiny new folder.  It can use anonymous authentication if you feel comfortable with that, or do as you please.  If it works and doesn't cause the FBI to kick your door in and shine red LASER dots on your forehead, you will probably be alright.  Suggested site name: adqueries.
3. Optional (but preferred): Convert the virtual folder into an IIS application.  Assign a domain account for the application pool process context.  That account should have rights to query whatever it is you want to query.  It does NOT have to be a domain administrators member, or anything that serious.  Click the "Test Connection" button to ensure it works.
4. You will likely need a minimum of 4 code files:
1. A main page which provides a list of saved LDAP queries, each of which are hyperlinked to a "run-query" type of page.  Suggested name: default.asp
2. A "run-query" type of page.  Suggested name: report.asp
3. A code library page, which will be included in each of the other pages (centralized code stuff).  Suggested name: my_asskicking_codestuff.asp.  Include it in the other pages using the standard top-line import method: (#include file=_my_asskicking_codestuff.asp)
4. A form page for adding new, or editing existing, queries.  It should have text boxes for the following properties at a minimum (add more if you like):
1. Report Name [textbox]
2. Report Comment / Description [textbox]
3. LDAP query code statement [textarea] or [textbox]
   suggested name: reportedit.asp
5. OPTIONAL: You can code the form-page (mentioned above) to serve as both the "add new" and "edit" pages if you like, or make separate pages.  It doesn't matter as long as you're happy and it gets your mo-jo humming along.  For example: reportnew.asp, reportedit.asp
5. Drink your coffee and start coding it up.

The First Query

1. Here's an easy one:  objectCategory='computer' AND operatingSystem='Windows 8*'.  If you actually followed my suggestion earlier, then you'll want to try a new one, like 'Windows 7*' or whatever.  Keep mind you can make queries to search for almost anything: user accounts, groups, contacts, printers, shares, etc.
2. Drink more coffee

The ASP Stuff

I wanted to avoid this, but I suppose I can't totally ignore it.  I can ignore my neighbors (they ignore me anyway) but code is hard to ignore.

The code library file:

• Needs to include some basic stuff like variable definitions and Const definitions, which identify such things as ADSI values, ADO values, the name of your SQL Server and Data Source Name (DSN), and so on.  Example below:

     '------------------------------------
     ' comment: ADO and ADSI enumerations
     '------------------------------------
     Const ADS_SCOPE_SUBTREE = 2
     Const E_ADS_PROPERTY_NOT_FOUND = &H800500D
     Const adOpenForwardOnly = 0

     Const adOpenKeyset = 1

     Const adOpenDynamic = 2

     Const adOpenStatic = 3

     Const adLockReadOnly = 1

     Const adLockPessimistic = 2

     Const adLockOptimistic = 3

     Const adLockBatchOptimistic = 4

     Const adUseServer = 2

     Const adUseClient = 3

     Const adCmdText = &H0001

• The ADSI/OLEDB connection stuff:  The standard interface between script code and Active Directory (for LDAP queries) is through an OLEDB provider.  For more info on this, check out http://msdn.microsoft.com/en-us/library/windows/desktop/aa746517(v=vs.85).aspx 
• Take this example (credit: ActiveExperts.com: link) and use it to test by running it in a CMD console using RUNAS to verify the AD account you intend to use will work properly.
• Adapt the VBscript code (above) to ASP by changing the wscript.echo methods to Response.Write (be sure to enclose the string results in HTML tags to format it properly).
• Test it via your web browser.

Yes.  I know you can do this with ASP.NET and Visual Studio Express, but if you're still working with ASP, like millions of other Earthlings, you still have options (and free web blogs).  If you REEeeeeeeaaally want some actual code, let me know (post a comment below).

Now.  If you'll excuse me, I need to go pass out on my bed. Namaste.

Crude But Effective (ConfigMgr Right-Click Tools Trickery)

Intro:  I just took the wraps of this particular "feature" within a web application project I've been working on for some time now.  So I figured it was a good time to share some thoughts about why I spent the time and effort to make it work.  I'm not going to say it's 100% complete yet, and I still have some features to fill-out, but it's walking on two legs and says "Daddy!" so I'm kind of proud of it.  I actually submitted this for another blog site but it was rejected as not being within the topic set they prefer, so I'm posting it here. 

A Little Background

Anyone who grew up watching the original Star Trek series on TV should recall a particularly famous line quoted by Spock, where he said "Crude, but effective".  The implication made was that a “solution” doesn't always have to be elegant or optimal in order to be sufficient.  Hence the name of this article for the mini-project I’m about to describe and bore you to death. So, let’s get started!

One of the most widely-used tools in the world of Microsoft enterprise systems management, is System Center Configuration Manager.  One of the most widely-used tools to extend the functionality of Configuration Manager is (or are) the "SCCM Right-Click Tools", developed and supported by Rick Houchins (link).

The tool-set installs a set of scripts, and some XML extensions to the MMC console snap-in for Configuration Manager.  The result is an additional set of pop-out menus when you right-click on resources in the MMC console.  They are grouped into "Tools", "Actions", "Log Files" and so on, each having a set of links to perform useful tasks, upon a single resource (computer) or all of the resources in a selected Collection. Some of the features it provides include:

●     Invoke ConfigMgr Agent actions such as:

○     Hardware (and Software) Inventory

○     Machine (and User) Policy Retrieval and Evaluation

○     Discovery Data Collection Cycle

○     More

●     Run Client Tools such as:

○     Restart ConfigMgr Agent service

○     Uninstall/Re-install ConfigMgr Client

○     Re-Run Advertisements

●     View Client Log Files

●     View Reports for selected Clients or Collections

There are quite a few versions of this out in the wild, and I've rarely seen, or heard of two IT shops using the same (or even latest) version.  Regardless, Rick's product has become so popular and widely-known, that's it’s hard to find a ConfigMgr Administrator anywhere in the world that hasn't heard of it, let alone one that doesn't use it every day.  It’s even spawned inspired projects such as Client Tools (link) and SCCM Client Actions Tool (link).  Some have taken off, while others have not.  Ultimately, it's a good thing to inspire others to try good things for the good of others, is it not?

One of the larger projects I've been working on for the past year is a web-based tool for integrating and managing multiple enterprise "islands" of information to achieve an holistic management tool.  This involves Configuration Manager, Active Directory, legacy inventory management systems, multiple databases, and rolls all of that into a Role-Based Access Control interface that maps the features to the discrete functional groups within their IT department, as well as specific features made available to end users.

Some of you might wonder if this has anything to do with my old "Windows Web Admin" project that I killed a while ago.  The answer to that is "yes".  WWA formed the basis of this project, but if WWA was 1.0, this project is approximately 5.0.  There’s a lot of change and scaling out in this one, but it's genesis was WWA.  Okay, enough of that. Let's move on...

One of the most daunting challenges that I've been trying to solve is how to incorporate my own set of "client tools" into the web interface.  Why is this so difficult?  Primarily, the biggest concern is security risk and exposure.  There are quite a few potential ways to approach this, but let’s break it down in the most basic terms:

The Goal

The goal of this particular subset of the project is to be able to directly invoke processes on remote computers over a network connection, and initiate this from within a web browser.  Some aspects of the Right-Click tools are easy to implement via a web interface, such as exploring the C: drive, opening the remote log or cache folder, and ping for connectivity testing.  But the features which require invoking a WMI or WBEM/SWBEM interface remotely are a little more complicated to achieve from within a local web browser session.  At least they are for my limited set of abilities.

In the simplest terms, WBEM, or Web-Based Enterprise Management, is the web interface for WMI services on a given computer.  WBEM is the mechanism by which you connect to, and interact with, the ConfigMgr client on a remote computer.  It’s also how you connect to, and interact with the site server, but that’s for another article.

WMI and WBEM can be a little complicated to describe, but that’s not necessary for this article.  But you do need at least a basic understanding of WBEM as it pertains to "what it is", so that you can appreciate what’s going on under the hood when you turn the key and start this beast up.

The good news is that you don’t have to roll up your sleeves and get dirty with programming code in order to leverage WBEM's benefits.  There are packaged utilities that can do the messy work for you, such as the SendSchedule.exe utility included with the Microsoft ConfigMgr Toolkit v2.

There are probably more potential "options" to solving this dilemma, but I've boiled it down to three:

Option 1 - Client-Side Code

It could be done with some JavaScript code with JSON or JQuery, or whatever, running as a client-side process (on the computer where the browser is active).  This makes it possible to run in the context of the logged on user.

The problem with the client-side script option is security context and "sand-boxing" with respect to invoking other local scripts, or an executable, under the logged-on user context.  There's also the challenge of maintaining centralized access control and logging. The security model in this scenario relies on individual user accounts having permissions to invoke remote interfaces like the ConfigMgr Client Agent service.  This isn't a bad thing however, but it does depend on diligent administration of an AD security group.

Option 2 - Server-Side Code

It could be done with server-side code, but that would involve forked or marshaled processes running under the context of a proxy account.  Or it could be run in the context of the IIS application pool, or even the IIS web site.

The biggest problem with the server-side code approach is the use of a proxy user account, and controlling access to the folders and files in which the user context can execute.  The security model in this scenario is a single "proxy" user account, with permissions granted to allow it to invoke remote interfaces on client computers.

Option 3 - A Real Developer

It could also be done with custom programming using .NET or Java and a compiled executable or even a browser add-in.

The security model in this scenario could be either of the two described in the first two options above, or even a hybrid of both of them.  However, the less obvious "problem" with this approach comes down to complexity, time and resources.  Very often the fourth issue is budget.  In our case, we don’t have this as a viable option at our disposal.  What we do have at our disposal is.... me. 

That’s right.  Simple. Basic. Me.  My skill set is not the most robust on Earth, big shock, I know, but it does contain enough database, and coding skills, and a fetish for application design, to be dangerous.  And if you (ok, I) add a pinch of stupidity, sarcasm and bad humor, and a teaspoon of caffeine to the mix, you have a concoction that get it done.  So this led me to option 4...

Option 4 - Duct Tape, Chewing Gum, and Bailing Wire

The old McGyver approach.  This is actually a very old method, but it's a tried-and-true method, that has stood the test of time and many, many projects.  It's the old "web-database-scheduler" approach.  Let me digress...

In the most basic terms possible:

There’s a web interface for submitting the requested "action" to be performed on a remote client.  This captures the basic information: the client (or collection) name, and the action to be performed.  Before you start flapping away about which language is "best" for this role, I’ll just gently close your lips with my greasy fingers, encased in old welding gloves, and whisper: "shhhhhhhh... it doesn’t really matter."  It's true. You could crank this out using PHP, ASP, ASP.NET, Ruby, Python, Mython, Yourthon, Therethon or Whateverthon.  As long as it can display a web form in a browser session, collect the input, and interact with a database to store the information, you’re good to go.

Next, there's a database table for storing the submitted requests entered from the web form.  This includes the client name, the action to be performed, as well as who requested it, and when (date and time), and task-related things like "is-completed" and when, along with other optional pieces of information.

Then there's a scheduled task, which reads the database table, on a frequent and recurring schedule, fetching only those rows which have not already been processed (completed), and executes the requested action on the specified remote computer.  After each task is completed, the corresponding row in the database table is updated to indicate it was completed and time-stamped.  This is what effectively prevents the entire process from melting down by re-running every row every time.

So, putting this all together, you get a process that works like this:

1      Authorized user of the web site opens a web page for a particular computer or Collection of computers, and clicks a button/link for "Client Tools".  This opens a web form with a list of available "actions" to perform on the computer(s) remotely.  User selects the desired action and clicks "Submit".  The information is then entered into a database table.  In my case, I'm using SQL Server 2008 R2.  But you could use Oracle, MySQL, Sybase, Informix, DB2, or just about anything that’s "robust" enough to support a business environment with multiple users.

2      The scheduled task, running under the context of a proxy account with permissions to invoke client agent actions remotely, executes a script on the next cycle.  The script reads all rows which are not yet marked as being completed.  Iterating through the set of rows, it reads the name of the computer to be acted upon, and the requested "action" to invoke.  The script checks for connectivity to the remote computer, and then executes the remote action using either SWBEM interface (via COM or .NET), or in the case of my lazy-ass approach: executes the SendSchedule.exe utility (included with the ConfigMgr Toolkit v2 download).  After running the task, it updates the row to set the "completed" field and enters a time-stamp to indicate when it was processed.

3      The remote client receives the request from the remote script execution, under the user context of the scheduled task/job that launched it.  It then verifies authentication and, if allowed, invokes the client action or other (possibly) custom task.

Clunky?  Yep.  Complicated?  Not really (I've seen things MUCH more complicated doing much less).  Could it be done more simply or more elegantly?  You betcha!  

Some Advantages

So, what additional benefits does this approach provide?  For starters, since the action is really based on a SQL database repository, and a job scheduler, I have a centralized model.  That means I have the means to log everything going on.  Now, instead of every console-user running a local task, with log files on their computer and the remote computers, everything is in one place, where it's easy to sort and manage and get useful reports out.  It's also easy to apply a security model to restrict access in one place at one time.  I'm not going to say web applications are a panacea, but they do offer some very attractive capabilities.

Here's a few screen shots of it.  The first image is the Resource details view, which is showing the general "Computer System" properties.  The "Client Tools" button is at the upper-right corner.

After clicking the "Client Tools" button, the pop-up form is shown (below).  Right now, I only have three of the Client Actions exposed, not because there's a problem with them, but because I'm working on role-based filtering of features. The user session for this example doesn't have access to the other actions.

The image below is the log report, which captures every submitted request and shows when it was processed and the result.

Conclusion Contusion

Could this all have been a different/better way?  I'm sure it could have, but I'm working against two huge constraints: time and skill set.  Time is very limited and my skill set is still mostly ASP/SQL.  I've done a lot with PHP also, but in this environment it didn't make sense to shoehorn it in.  I used to work with ASP.NET for a brief period, but that was a while ago and I haven't had the opportunity to brush up on the newer technologies.  I know: excuses-excuses.  Feh.

The third constraint is budget.  Budgets are awesome.  If only we had one.  For now, duct tape and chewing gum will do just fine.

From Adios to Hola! SCCM is Back in my World

I thought it was funny that I ran across this post from 2010, where I said I was leaving the SMS/SCCM world behind because there "were no opportunities" available to apply my skills in that realm.  At the time it was true, there were no such opportunities in my reach.  However, since July 2010, when I moved on to a different employer, Configuration Manager is very much back in my life.  It was evident from how I pumped up the "AD Web Admin" project, and then dismantled it to use in a real production environment (in pieces. reorganized and reconstituted).
I've been spending a LOT of time weaving together ASP/HTML/CSS/JavaScript with SQL and SWBEM to extend Configuration Manager and integrate it via the web with Active Directory, asset inventory databases, and role-based access control.  It's a fun project and I love every minute I get to spend on it.  I know what you're thinking: "why not ASP.Net?".  Because it's what I originally built the ADWA project with and it was easier to restructure it than start over, and my ASP.Net skills aren't quite up to that task yet.  In any case, it works, and does what the customer wants it to do, which is a nice thing.

It just goes to show, to me at least, that you never know what's around the next corner.

Don't Forget the Eggs: ADO basic errors

I'm not a DBA, although I play one on breaks in the kitchen at work.  I have worked with various databases for quite a few years, including MS SQL Server, MySQL, and Oracle.  I don't count FoxPro or Access because I absolutely hate client-side databases due to the bullshit headaches they create for IT departments (and consultants like myself), but alas, I have already digressed on that subject in previous blog posts.

One thing I see quite a bit with ADO examples in particular is a lack of (a) error checking and (b) connection limiting.  I'm not talking about connection throttling, but rather: applying some refactoring logic to how you open and close connections to optimize the use of the open pipeline without keeping it open too long (or re-opening it too many times).

As for error checking:  This is a fairly standard/typical piece of VBscript/ASP code for running a "select" query via ADO against a database.  It doesn't matter whether that database is local to the server/host where the code is being executed, well, it does actually, it matters more if it's remote, but whatever, let's chew and digest slowly here...

[CODE]

Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

query = "SELECT * FROM dbo.SomeTable WHERE id=" & _
    idNumber & " ORDER BY ItemName"

conn.Open dsnString

rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly

Set cmd.ActiveConnection = conn

cmd.CommandType = adCmdText
cmd.CommandText = query
rs.Open cmd

If Not(rs.BOF And rs.EOF) Then
    cols = rs.Fields.Count
    rows = rs.RecordCount
    Do Until rs.EOF
        ' do something stupid here
        rs.MoveNext
    Loop

Else

    Response.Write "oops, no records were returned"
End If

rs.Close
conn.Close
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing

[/CODE]

This looks simple enough. But there are quite a few places that could implode here if not handled explicitly.  Granted, error handling with .NET is more robust, but indulge me here for a moment since (A) there's still 100 times more VBScript code strewn about this planet than .NET code, and (B) I'm old.  The big three problems that are most likely to occur with this scenario are...

Connection Failure
Connection Delay / Time-Out
Recordset Access Failure (access denied)

Let's handle them one by one...

Connection Failure

Prior to the "conn.Open" statement, we should override the default error system and then check for the exit code from the .Open method and see what happened.  If it was successful (exit code: 0), we continue on, otherwise we should handle the error and exit safely.


[CODE]

On Error Resume Next
conn.Open dsnString

If err.Number <> 0 Then

    ' an error occurred, so something clever here

    Response.Write "oops, cannot open a connection"

    Response.End

End If

[/CODE]

If you do your connection within a Sub() or Function() block, you should probably exit using Exit Sub or Exit Function, but that's not always true either.

Connection Time-Out

What if the connection is taking a longer time to resolve than usual?  We can handle that too...


[CODE]

On Error Resume Next

conn.ConnectionTimeOut = 15 ' allow 15 seconds to establish the connection
conn.Open dsnString

If err.Number <> 0 Then

    ' an error occurred, so something clever here

    Response.Write "oops, cannot open a connection"

    Response.End

End If

[/CODE]


Recordset Access Failure

Another common issue is when you can successfully open the connection, but cannot read from a table or view because of security permissions.


[CODE]

rs.Open cmd

If err.Number <> 0 Then

    ' do something awesome here

    Response.Write "oops, unable to access the table or view"

    Response.End

End If

If Not(rs.BOF And rs.EOF) Then
    cols = rs.Fields.Count
    rows = rs.RecordCount
    Do Until rs.EOF
        ' do something neato here
        rs.MoveNext
    Loop
End If

rs.Close
conn.Close
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing

[/CODE]


Connection Management

I've seen more situations than I can count where a single page of code (script file, web page, etc.) makes repeated requests from a database in sequential order (as the page is rendered or the script is executed).  Most often it's having to grab data from multiple tables and/or views, or execute multiple stored procedures or functions.  In a lot of cases, the code doing the heavy lifting is being included from separate files (using "includes").  That's all nifty and modular, which is a good approach, but always be VERY careful with that approach that you don't have each module do it's own connection open/close management.  This not only slows down the processing, but it requires more bandwidth and more load on the network and the database server as well.

A case in point might be a web page that renders a report of an employee, then it displays a table with performance evaluation records, followed by a table of employees managed by the employee in question.  If those data repositories are all on different database servers that may be all you can do, but if they happen to be on one server, or even in one database, you should seriously review minimizing the number of open/close requests on your connections.

A brief sample of this using pseudo code:

open connection1
open recordset1
close connection1
open connection2
open recordset2
close connection2
open connection3
open recordset3
close connection3

might work a lot faster and better as...

open connection
open recordset1
open recordset2
open recordset3
close connection

Some people prefer to open a "global" or "session" connection, whereby the connection is opened upon login or initialization by each user session.  The connection object itself is stored in the session stack and made available globally to that user throughout their session window.  Each concurrent user has their own connection opened and maintained on a stack.  Granted this makes it easier to run queries, updates, etc. without the overhead of managing connections at the more granular page/script level, but it definitely taxes the database server with a lot of unnecessary open connections.  For a handful of users that may be fine, but with hundreds or thousands of users it can be a mess and make the database server drag.

Just some random thoughts after beer.  Have any thoughts you'd like to share?

SQL Query Functions for Configuration Manager 2007 Folder Trees

When you look in the System Center Configuration Manager 2007 admin console, you should be familiar with "folder" structures with respect
to Packages and Advertisements.  However, when you want to determine the folder tree/path for a given package or advertisement programmatically, it takes a little patience and scratching.
 
There are two key SQL tables in the site database: Folders, and FolderMembers.  To start with, you can query the "FolderMembers" table to get the "ContainerNodeID" identifier value for a given Package or Advertisement by passing in the "InstanceKey" value.  This is actually either the AdvertisementID or PackageID value, which should begin with the 3-char site prefix (i.e. "ABC1234B", where "ABC" is the site code).  Once you get the ContainerNodeID, you can query for the Folder "Name" and “ParentContainerNodeID” values and begin walking up the logical folder hierarchy.  The functions below will help get you there.  The Function "CMFolderID" returns the ContainerNodeID for a given Package or Advertisement by name.  The result of that function call is then passed into the Function "CMFolderTree" to return a concatenated folder path using a fairly basic recursive SQL routine.
 
The only additional ingredients you will need to use these two functions are (a) the standard ADO enumeration constants definitions, and (b) a data source name connection (aka “dsn”) of some kind.  These were coded for use in ASP, but you can easily convert these to VBScript, KiXtart or PowerShell if you prefer. – Cheers!

Examples:

Package for application "My Stupid Application 2011" has an ID of "ABC1234X"
The console shows it under:

---\Packages
    +---\Engineering
          +---\Contoso 
                +---\Stupid Applications

nodeID = CMFolderID ("ABC1234X")
folderTree = CMFolderTree (nodeID )

returns: "Packages\Engineering\Contoso\Stupid Applications"

'----------------------------------------------------------------
' function: returns Folder NodeID from object name (package, advertisement, etc.)
'----------------------------------------------------------------

Function CMFolderID (instanceID)
  Dim conn, cmd, rs, query, retval : retval = ""
               
  query = "SELECT DISTINCT dbo.FolderMembers.InstanceKey, dbo.FolderMembers.ContainerNodeID, " & _
    "dbo.Folders.Name FROM dbo.FolderMembers INNER JOIN " & _
    "dbo.Folders ON dbo.FolderMembers.ContainerNodeID = dbo.Folders.ContainerNodeID " & _
    "WHERE (dbo.FolderMembers.InstanceKey = '" & instanceID & "')"
               
  Set conn = Server.CreateObject("ADODB.Connection")
  Set cmd  = Server.CreateObject("ADODB.Command")
  Set rs   = Server.CreateObject("ADODB.Recordset")
 
  conn.Open dsn
 
  rs.CursorLocation = adUseClient
  rs.CursorType = adOpenStatic
  rs.LockType = adLockReadOnly
 
  Set cmd.ActiveConnection = conn
 
  cmd.CommandType = adCmdText
  cmd.CommandText = query
  rs.Open cmd
 
  If Not(rs.BOF And rs.EOF) Then
    retval = rs.Fields("ContainerNodeID").value
  End If
  rs.Close
  conn.Close
  Set rs = Nothing
  Set cmd = Nothing
  Set conn = Nothing
  CMFolderID = retval
End Function


'----------------------------------------------------------------
' function: returns concatenated chained folder path string for object
'----------------------------------------------------------------

Function CMFolderTree(NodeID)
  Dim conn, cmd, rs, query, retval : retval = ""
               
  query = "WITH ParentChildRels (ParentContainerNodeID, ContainerNodeID, " & _
    "Name, HierarchyLevel) AS " & _
    "(SELECT ParentContainerNodeID, ContainerNodeID, Name, 1 as HierarchyLevel " & _
    "   FROM Folders " & _
    "   WHERE ContainerNodeID='" & NodeID & "' " & _
    "   UNION ALL " & _
    "   SELECT " & _
    "      r.ParentContainerNodeID, r.ContainerNodeID, r.Name, " & _
    "      pr.HierarchyLevel + 1 AS HierarchyLevel " & _
    "   FROM Folders r " & _
    "      INNER JOIN ParentChildRels pr ON " & _
    "         r.ContainerNodeID = pr.ParentContainerNodeID " & _
    ") " & _
    "SELECT * FROM ParentChildRels " & _
    "ORDER BY HierarchyLevel DESC, ParentContainerNodeID, ContainerNodeID, Name"
 
  Set conn = Server.CreateObject("ADODB.Connection")
  Set cmd  = Server.CreateObject("ADODB.Command")
  Set rs   = Server.CreateObject("ADODB.Recordset")
 
  conn.Open dsn
 
  rs.CursorLocation = adUseClient
  rs.CursorType = adOpenStatic
  rs.LockType = adLockReadOnly
 
  Set cmd.ActiveConnection = conn
 
  cmd.CommandType = adCmdText
  cmd.CommandText = query
  rs.Open cmd
 
  If Not(rs.BOF And rs.EOF) Then
    cols = rs.Fields.Count
    rows = rs.RecordCount
    Do Until rs.EOF
      If retval = "" Then
        retval = rs.Fields("Name").value
      Else
        retval = retval & "\" & rs.Fields("Name").value
      End If
      rs.MoveNext
    Loop
  End If
  rs.Close
  conn.Close
  Set rs = Nothing
  Set cmd = Nothing
  Set conn = Nothing
  CMFolderTree = retval
End Function

Windows Web Admin 2011.04.26.001

A new build of WWA has been uploaded to SourceForge.  This one adds a new report to the main sidebar (filename: report7.asp).  This report adapts the SQL code used by one of the SCCM Windows 7 Readiness web reports and adds a summary tabulation at the bottom.  The target collection can be selected from a drop-down list at the top-right of the report page.  Give it a try and let me know how it works for you.

https://sourceforge.net/projects/wwadmin/

Windows Web Admin: Moving to a New Location

I've created a space for this project on SourceForge.net.  It will be removed from my downloads page and from now on will be maintained here:

https://sourceforge.net/projects/wwadmin/

SCCM Web Admin Project

I mentioned this earlier and I have an update to share.  I've been working on several projects that involve SCCM 2007 automation, using scripts, web apps, SQL tasks and so on.  I extrapolated a somewhat generic approach to see if it could be of use to anyone else.

The ASP project files are contained in a .ZIP file.  It's self-contained with all the necessary stuff and in the correct folder structure.  There's a __README.txt file inside the .ZIP that you should read for help with pre-requisites, setup, known issues and so on.  I'm curious to see if anyone finds this useful.  I don't have any plans to sell it, so it will likely be an open source project per se.

Everything you need to configure is found within the "_settings.asp" file, and needs to be edited to suit your test environment.  Use Notepad or some sort of ASCII text editor to update that file.  Do not ever use Microsoft Word or anything like that. Please.

Download it Here

There is no warranty provided.  No support provided either.  I assume no liability for any damages or loss of data or productivity for any direct or indirect use of this application.  USE AT YOUR OWN RISK.  If you have questions you can e-mail me at the address provided inside the project documents.

I have tested it on Windows Server 2003, 2008, and 2008 R2 with SQL Server 2005, 2008, and with System Center Configuration Manager 2007 SP2.  I have not tested it with SCCM 2007 R2, nor have I tested with the pre-release versions of SCCM 2012.

If this turns into something with legs, I may post more documentation and breath more life into it with additional features and capabilities.  I do not have any plans at this time to port the code to ASP.NET or PHP or anything else.  I'm just seeing if it's of any use to anyone else before I decide whether to keep it on life support or pull the plug.

Detect Windows 7 Users from Web Site

I had to tinker with this for two separate projects, so hopefully it’s of use to someone else.  Not really rocket science, but then again: web pages are never rocket science are they?

PHP code

function isWin7() {
	if (strpos($_SERVER['HTTP_USER_AGENT'], 'Windows NT 6.1') == true) {
		return true;
	}
	return false;
}

ASP code

Function isWin7() {
	If InStr(Request.ServerVariables("HTTP_USER_AGENT"),"Windows NT 6.1") > 0 Then
		isWin7 = True
	End If
End Function

Buy vs Build: The Devils You Know

Author’s note: I’m not sure if this should be a longer or shorter “post” (“article”?) because the core intent could either be stated as briefly as a tweet or as long as a book.  I’m sure there could be a full semester college course on this subject, but I’m not that good.  I’m throwing a dart at the best guess board to see how it works in the end. This article emphasizes intranet portal projects, but it applies to anything that incurs a choice between buying versus building your own.  I hope it helps (or at least entertains) – Enjoy!

One of my main interests, and sources of work, is herding cats.  Actually it’s information aggregation, or process automation. Most often this boils down to using the “web” as the hub or focal point for tying it all together.  Intranets are my biggest customer.  The discussion usually circles around concerns over costs and time when choosing between a retail or “COTS” solution versus a “custom” developed solution. It usually starts with a short discussion that goes like this:

Customer: “We really need to pull our information together and link things up.  We have separate databases for HR, work scheduling, customer lists, project status, Active Directory and security roles, financial projections, contract bid data mining, compensation modeling, and so on.  We’d like to connect all those things as well as add some sort of employee “self-service” features.  Should we buy something like SharePoint or have you build it for us from scratch?”

Me: “It depends” (don’t you LOVE that classic consultant responses!)

Seriously though, it really does depend.  I have a matrix of criteria to help evaluate which is the “way to go” towards arriving at the desired result.  But first, the cheesy diagram below illustrates that no matter which “way you go” you’re likely going to incur time and cost with making it “fit” into your environment, and “fit” in between what you currently have and you wish to have.  The question is then which direction incurs the least burden.

So, just what exactly is this “matrix of criteria”?

Criteria	COTS	Custom
Budget “hard limit”	?	?
In-House Talent (developers, admins)	?	?
Features Gap	?	?
Interfaces	?	?

Pretty simple, right?  Well, not really.  Each of these criteria potentially involves a ton of digression and drilling down.  How much so depends upon the scale of the business, the scope and scale of the “things” being integrated, and tangible and intangible constraints.

Whoa!  (record scratch sound here…) - Did you just say “tangible and INtangible constraints”?!  WTF?

Tangible constraints are pretty familiar to us all: budgets, time, staffing, physical and logical space, and so on.  Intangible constraints are usually human-based: emotional aspects, protectiveness, skill levels and expertise, and secondary distractions like, oh, for example: Joe is on the team to discuss this whole project.  But Joe manages the HR database systems.  He was forced to sit on this team by his boss, whom he doesn’t like, but who insists that it might be time to consider moving the HR system into something more accessible and flexible for interfacing with other systems.  Joe sees this as a threat to his control, and thereby a threat to his existence and job security.

Yeah.  Something like that.

If you’ve ever sat in a planning meeting that involved discussion about changing something major, or something that’s been relied upon for years and years, you absolutely KNOW what I’m talking about.  It’s more prevalent in larger corporate environments than within small businesses, but it can show its ugly head anywhere really. I will go ahead and say this, even if it causes some of you to shake your head in disagreement:

The single biggest obstacle, and cost inflation factor, in any technology-oriented project is human emotion.

Yep.  If we just acted like logical robots and implemented technology without pausing to argue over rationale and subjective value, we could get those tasks done in less than half the time it typically requires.

But let’s get back to the focus of this post, which is how to rationalize a “buy versus build” decision, primarily with respect to intranet portal projects…

You’re probably thinking that I usually knee-jerk to making a “build it” recommendation.  You would be correct.  But if you assume that my decision scoring is something like 10:1, you’d be very wrong.  My average is roughly 60:40.  That’s not because I’m trying to capture new work and income, no.  It’s because it just happens to be a function of the nature of the evaluation of criteria within the clients environments I deal with most often.

Some of the typical factors that lead me to recommend a COTS solution however are (or should be) pretty obvious:

• Do they already have complimentary systems in place that would make it easier to add the propose intranet solution?  For example, all data is currently in a single (or clustered) database, or other information management systems by the same vendor have been in place for a long time.  It’s usually best to add a matching brick to an existing wall, than to tear down the wall to change the brick color.
• Do they have existing development and admin talent to match the proposed solution?  Do they have .NET and SQL developers, or do they have Oracle and PHP developers?  Will they be available for this project full-time or part-time?
• Is the customer list of desired features an exact match with out-of-box features of the product?  Is it “close”?  How close?  How many of the desired features are typically the most difficult to develop from scratch, but which are already provided by the proposed COTS product?
• Are there existing instances of the proposed product in use elsewhere in the company?

When these factors evaluate in one direction, I usually recommend going with the COTS solution and then work to help them plan for, and implement it in the test and production environments.  SharePoint, Drupal, Joomla, etc. are all good products and very much tested and proven in their own rite.

But when evaluation of the criteria leans the other way, it then opens up another discussion and round of evaluation to vett (God, I hate the word “vett”! But I have to use it here because I have a gun to my head) the issues related to custom development:

• Time and Budget constraints
• Degree of Access to interface resources (cooperation)
• Variety of interface technologies (spread)
• Hand-off Issues

Most of these should be easy to understand, but I’m going to dig a little deeper into them anyway (why not?).

Cooperation is huge.  If the team, or the delegated staff you’re going to be working with, are going to open up and provide access and information to help you interface with their systems and data stores, great.  That makes life so much better.  But when they circle the wagons to protect their turf, or simply don’t put forth any real effort to keep the ball moving, then it becomes a huge detriment.  This falls into a semi-intangible aspect of the project scoping and assessment.  Ultimately this boils down to TRUST.  You have to trust them and they have to trust you.  You can’t scope or predict anything that’s human-oriented without familiarity and that is based on trust.

Variety of technologies, or “spread”, represents more of a metric nature.  If all of the data stores share a common technology, like Windows or Linux servers, MS SQL Server, Oracle, or XML structure, or whatever, then it helps you narrow your ingredients for baking the solution.  If there are a dozen systems you need to tie into which each does their own platform, vendor and technology dance, you’re going to have a tougher time herding the cats.  Even if all of those systems support an XML-based interface, there’s going to be additional work with planning, testing, and validating each custom interface, separate from building the actual end state.

Hand-off issues involve how the solution will be managed and maintained going forward.  You know: after you complete the delivery.  Make sure this is spelled out!  So many times I’ve walked into a project proposal meeting and this is never mentioned.  Who is going to keep this machine running after you deliver it?  You?  Them?  A mix of both you and them?  If it’s a mix, that’s the most important to spell out in terms of what, when and why.  If they expect YOU to keep it running, what is your backup plan?  Are you the only person?  That’s great if you only care about a paycheck. It’s extremely bad for the customer if you drop dead or have surgery, as well as bad for you for instilling TRUST in the eyes of your clients.  I always try to shift the role to them and offer as much knowledge transfer as possible.  This is the only possible and practical “win-win” situation.

Conclusion

If you were expecting me to stand up and shout “always do this!” or “always do that!”, I’m sorry to let you down.  But this falls into my firm belief that most knee-jerk recommendations are bullshit.  There is no “one size fits all” in the business world.  Because (and this repeats another mantra of mine): a business is a name for a group of people.  People are not stamped from a machine.  People vary.  Business varies.  Problems vary.  Solutions vary.  Be adaptable and listen.  Take notes and analyze them before proposing a solution or recommendation.  Don’t be in a rush to give an answer in the same meeting where the questions were asked.  No worthy business will hate you for saying you’ll get back to them after reviewing the facts and factors.

Neat-O Windows Admin Tricks: Automating a Help Desk Web Form

Scenario:

Within your Microsoft Windows Active Directory network environment, you have an intranet web site.  That web site serves up a Help Desk web page for users/customers/losers/idiots/crackheads/ to use for requesting things.  It has a form to fill-out, and when they submit the form, it generates an e-mail or stores the info in a database table (and sends an e-mail), etc. whatever.  But the form has a text box for the user to manually type in the name of their computer and their user name and other general contact info.  You'd really like to have that general contact info, and the computer name info, automatically filled in, so that users/losers don't enter stupid or incorrect information.

What to Do:

This scenario involves two (2) general issues that need to be addressed.  Each of these issues can be addressed in a variety of ways.  I'm only covering one (1) way to address each.

Part 1 - Automating the User Contact information

User information is typically stored in Active Directory.  If you (or whomever) did their job properly, each user account object should have all the pertinent information entered, such as e-mail address, phone number, department, first and last name, location info, etc.  AD information is stored and shared via LDAP and ADSI.  You can leverage built-in LDAP and ADSI features within ASP (or pretty much any COM or .NET scripting platform) to access that information.  An ASP web application can use the following code to query Active Directory for user account information.

The catch?  You have to make sure you enable "Integrated" authentication for your intranet web site in order to capture the user account name in the background.  This is silent and automatic if the visitor's use Internet Explorer.  Otherwise, they will be prompted to provide their domain credentials before accessing the web page (username and password).  I will explain the code shown below just below that.

<% Function UserName() Dim tmp, domain : domain = "mydomain" If Session("username") <> "" Then UserName = Session("username") Else tmp = Trim(Request.ServerVariables("REMOTE_USER")) If tmp <> "" Then Session("username") = Ucase(Mid(tmp, Len(domain)+2)) UserName = Session("username") Else UserName = "" End If End If End Function %>

Part 2 - Automating the Computer Name information

This is only a tiny bit more complicated, but still pretty easy.  Because capturing the visitor's computer name is not nearly as straightforward, you have to try alternate methods.  One method is to capture the remote IP address, and perform some sort of background lookup to find the computer name.  Messy.  Ugly.  Painful.  Another method is a bit more circuitous, but it works very well: create a desktop (or start menu) shortcut which contains the computer name in the URL parameters.  Consider the following URL which would be requested from a shortcut on the desktop of a user working on computer named "Computer123":

URL: http:// intranet/helpdesk/helpform.asp?cn=Computer123

The small catch here is that you will need to make sure your web form can read the "cn=" parameter and transfer the assigned value (e.g. "Computer123") into the form edit box.  If your web form is ASP or ASP.NET, this is very simple to enable.  The example below shows how to capture this value with an ASP web page:

helpform.asp

<% cn = Trim(Request.QueryString("cn"))%> <html> <head> <title>Help Desk Request Form</title> </head> <body> <h1>Help Desk Request Form</h1> <form id="user" method="post" name="form1" action="request.asp"> Your Name: <input type="text" name="user" size="20" value="<%=UserName()%>"/> <br/> Computer: <input type="text" name="comp" size="30" value="<%=cn%>"/> <br/> Details:<br/> <textarea name="details" cols="55" rows="8"></textarea> <br/> <input value="Submit" type="submit" name="btnSubmit"/> <input value="Reset" type="reset" name="btnReset"/> </form> </body> </html>

There are many "easy" ways to create shortcuts, but they usually come down to using the Wscript.Shell object.  If you want/need to deploy a shortcut to a large number of desktops, you may want to consider scripting it.  You can "blast" out the change by running a script from your desktop or from a server, etc.  Or you can make it part of your login script, or deploy it as a package via Group Policy or SMS, SCCM, Altiris, etc.  Whichever way you deploy it doesn't really matter.  You should be able to automate the capturing of the computer name and use that to concatenate a URL text string to use for creating the shortcut target value.  The examples below show how you can do this within a login script (both VBScript and KiXtart flavors are shown).

Which way (script language, utility, etc.) you choose to go doesn't matter as long as it works.  Once you fetch the computer name, you can take that and concatenate the URL string and make the shortcut for the user to click on to access the Help Desk web form.  The script code below will create a shortcut on the user's desktop using the URL example above.

login.vbs

Function ComputerName() Dim objShell Set objShell = Wscript.CreateObject("Wscript.Shell") ComputerName = objShell.ExpandEnvironmentStrings("%computername%") End Function url = "http:// intranet/helpdesk/helpform.asp?cn=" & _ ComputerName() cap = "Help Desk Request Form" Set wshShell = CreateObject("Wscript.Shell") Set objFSO = CreateObject("Scripting.FileSystemObject") strPath = wshShell.SpecialFolders("Desktop") scPath = strPath & "\" & cap & ".url" If objFSO.FileExists(scPath) = False Then Set objShortcutURL = wshShell.CreateShortcut(scPath) objShortcutURL.TargetPath = url objShortcutURL.Save Set objShortcutURL = Nothing End If

 

login.kix

$url = "http:// intranet/helpdesk/helpform?cn="+@wksta $cap = "Help Desk Request Form" $wshShell = CreateObject("Wscript.Shell") $strPath = $wshShell.SpecialFolders("Desktop") $scPath = $strpath+"\"+$cap+".url" if exist($scPath) = 0 $objShortcutURL = $wshShell.CreateShortcut($scPath) $objShortcutURL.TargetPath = $url $objShortcutURL.Save() $objShortcutURL = 0 endif

Putting it All Together

Duct tape, Elmer's Glue, staples, chewing gum, rubber bands… ok, you should now have a "helpform.asp" file sitting on your web server and shared from an IIS site on the server which has integrated authentication configured (do not enable any other authentication options).  Then you would have either a "login.vbs" or "login.kix" login script file.  Target that using Group Policy or simply send a link to the script to one of your test users (aka minion guinea pigs) to test out.  The script should be executed under the user context, not the system context, so it can grab the user information and computer information as well.

Now, when a user logs on, it will create a shortcut on their desktop to "Help Desk Request Form" which contains the URL shown in RED above (except with their actual computer name filled in, instead of "Computer123").

When the user launches the shortcut, it requests the URL along with the computer name.  The ASP form reads the "cn=" parameter to fetch the computer name.  The ASP form also uses the ServerVariables collection to fetch the user account name.  You can modify the **** out of this to suit your needs/whims, etc.  Read the following disclaimer however. – Cheers!

IMPORTANT DISCLAIMER:

Test this in an isolated NON-PRODUCTION environment before attempting to use it in a real production environment.  There is no warranty or guarantee of ANY KIND WHATSOEVER provided for this information, either explicitly or implicitly.  I assume no responsibility or liabilities for any stupid shit dumbass crap, loss of data, downtime, anger, resentment, hostility, depression, angst, mood swings, etc., that may occur as a result of what happens if you don't test it thoroughly before letting it lose on your network.

Making ASP “Die” like PHP

How clever was that title!  Not clever.  I know.  Oh well.

So, PHP has the age-old “die()” function to stop processing and puke up a message in the process.  Great for most general needs.  When you want the page to crash-out gracefully and say something intuitive and elegant to the user, like “hey, you fucked up, you idiot!”  Just kidding.  ASP doesn’t really have an identical function, but it does have the Response.End object method.  So you can make a really simple function (ok, Sub) to do pretty much the same thing.

Sub Die(strMessage)
    If strMessage = "" Then
        strMessage = "processing stopped."
    End If
    Response.Write "<span style="color: red; font-weight: bold">" & strMessage & "</span>"
    Response.End
End Sub

' example of usage
If Session("LoggedOn") <> "TRUE" Then
    Die "logon failure!"
End If

When would you want to use this?  One good example is on pages where you want to ensure some global condition exists before rendering the page.  A good example of that would be checking to see if the user is “logged in” or “validated” or a quantity in a form was selected, or whatever.  Maybe you want to ensure a page is only called from a specific other page, and not directly.  You can pass a hidden form object or querystring to shake hands, but if a sneaky asswipe user decides to shortcircuit your site by calling the second page directly, you can check for that form/querystring input and gracefully crash-out if it’s not provided.  I’m sure if you smoke enough of something you can think of other possible uses.  But this hopefully helps you in some random remote way.

ASP/PHP: Make a List of U.S. State Abbreviations

I’ll be digging through my old projects to find anything interesting enough to bore you to absolute death (or gouge your own eyes out with a fork in order to stop that burning feeling experienced from looking at shlock like this).  Here’s an example for populating an HTML [select] form object list (aka “listbox” or “drop-down list”, etc.) with 2-character abbreviations for U.S. states.  Enjoy…

PHP version:

function StateCodes($default) {
    $delim = ',';
    $clist = "AL,AK,AS,AZ,AR,CA,CO,CT,DC,DE,FL,GA,HI,IA,ID,IL,IN,"
    . "KS,KY,LA,MA,MD,ME,MI,MN,MO,MS,MT,NC,ND,NE,NH,NJ,NM,NY,"
    . "OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,WY";
    $tok = strtok($clist, $delim);
    while ($tok != false) {
        if ($default == $tok) {
            echo "$tok\n";
        }
        else {
            echo "$tok\n";
        }
        $tok = strtok($delim);
    }
}

ASP version:

Sub StatesList(default)
    Dim lst, x
    lst = "AL,AK,AZ,AR,CA,CO,CT,DE,DC,FL," & _
        "GA,HI,ID,IL,IN,IA,KS,KY,LA,ME," & _
        "MD,MA,MI,MN,MS,MO,MT,NE,NV,NH," & _
        "NJ,NM,NY,NC,ND,OH,OK,OR,PA,RI," & _
        "SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY"
    If default = "" Then
        Response.Write "" & vbCRLF
    End If
    For each x in Split(lst, ",")
        If Ucase(x) = Ucase(default) Then
            Response.Write "" & x & "" & vbCRLF
        Else
            Response.Write "" & x & "" & vbCRLF
        End If
    Next
End Sub

Web Report of SQL Table and View Structures via ASP

Here’s a fairly basic ASP example for displaying the table and view structures in a SQL database.  I have only tested this with MS SQL Server 2005 and 2008, so I can’t vouch for other database platforms.  Be sure to edit the variables at the top (in red) carefully.

'****************************************************************
' Filename..: sqlschema.asp
' Author....: skatterbrainz (skatterbrainz.blogspot.com)
' Date......: 08/04/2009
' Purpose...: display tables and views
' SQL.......: SERVER\INSTANCE, DatabaseName
'****************************************************************
Response.Expires = -1

Const schema_name = "dbo"
Const db_server = "SERVERNAME\INSTANCE"
Const db_name   = "DatabaseName"
Const db_user   = "UserName"
Const db_pwd    = "Pa$$worD"

dsn = "DRIVER=SQL Server;SERVER="&db_server&";database="&db_name&";UID="&db_user&";PWD="&db_pwd&";"

'----------------------------------------------------------------

Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adUseClient = 3
Const adCmdText = &H0001

'----------------------------------------------------------------
' function: trap and display error and stop processing
'----------------------------------------------------------------

Sub ErrTrap(s)
    If err.Number <> 0 Then
        wscript.echo "error: " & err.Number & " / " & err.Description
        wscript.echo "reason: " & s
        Response.End
    End If
End Sub

On Error Resume Next    

Set conn = Server.CreateObject("ADODB.Connection")
Set cmd  = Server.CreateObject("ADODB.Command")
Set rs   = Server.CreateObject("ADODB.Recordset")

query = "SELECT table_name, table_type " & _
    "FROM information_schema.tables " & _
    "WHERE table_schema='" & schema_name & "' " & _
    "ORDER BY table_name"

conn.Open dsn
ErrTrap "fail: ado-conn-open"

rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly

Set cmd.ActiveConnection = conn

cmd.CommandType = adCmdText
cmd.CommandText = query
rs.Open cmd
ErrTrap "fail: ado-rs-open"

If rs.BOF And rs.EOF Then
    rs.Close
    conn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set conn = Nothing
    Response.Write "<strong>No records found</strong>"
    Response.End
End If

'----------------------------------------------------------------
' function: 
'----------------------------------------------------------------

Sub TableColumns(tableName)
    Dim conn, cmd, rs, query
    On Error Resume Next    
    
    Set conn = Server.CreateObject("ADODB.Connection")
    Set cmd  = Server.CreateObject("ADODB.Command")
    Set rs   = Server.CreateObject("ADODB.Recordset")
    
    query = "SELECT column_name, data_type, ordinal_position, column_default, " & _
        "character_maximum_length AS maxlen, is_nullable, numeric_precision " & _
        "FROM information_schema.columns " & _
        "WHERE table_schema='" & schema_name & "' AND table_name='" & tableName & "'"
    
    conn.Open dsn
    ErrTrap "fail: ado-conn-open columns"
    
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    rs.LockType = adLockReadOnly
    
    Set cmd.ActiveConnection = conn
    
    cmd.CommandType = adCmdText
    cmd.CommandText = query
    rs.Open cmd
    ErrTrap "fail: ado-rs-open columns"
    
    If Not(rs.BOF And rs.EOF) Then
        Response.Write "<table width=100% border=1 cellpadding=4 cellspacing=1>"
        Response.Write "<tr><td>Field</td><td>Ordinal</td>"
        Response.Write "<td>Type</td><td>Size</td><td>Null</td>"
        Response.Write "<td>Default</td><td>Prec</td>"
        Response.Write "</tr>" & vbCRLF
        Do Until rs.EOF
            ordinal  = rs("ordinal_position").value
            colname  = rs("column_name").value
            datatype = rs("data_type").value
            maxlen   = rs("maxlen").value
            nullable = rs("is_nullable").value
            numprec  = rs("numeric_precision").value
            coldef   = rs("column_default").value
            Response.Write "<tr>" & vbCRLF
            Response.Write "<td style=width:180px>" & rs("column_name").value & "</td>"
            Response.Write "<td style=width:60px>" & rs("ordinal_position").value & "</td>"
            Response.Write "<td style=width:120px>" & rs("data_type").value & "</td>"
            Response.Write "<td style=width:120px>"
            If Not(IsNull(rs("maxlen").value)) Then
                Response.Write rs("maxlen").value
            End If
            Response.Write "</td>" & vbCRLF
            Response.Write "<td class=v8w style=width:120px>"
            If rs("is_nullable").value = "YES" Then
                Response.Write " [Null]"
            End If
            Response.Write "</td>" & vbCRLF
            Response.Write "<td style=width:120px>" & rs("column_default").value & "</td>" & vbCRLF
            Response.Write "<td>" & rs("numeric_precision").value & "</td>" & vbCRLF
            Response.Write "</tr>" & vbCRLF
            rs.MoveNext
        Loop
        Response.Write "</table>" & vbCRLF
        rs.Close
        conn.Close
        Set rs = Nothing
        Set cmd = Nothing
        Set conn = Nothing
    End If
End Sub

'----------------------------------------------------------------
' function: 
'----------------------------------------------------------------

Sub ViewTables(vName)
    Dim conn, cmd, rs, query
    On Error Resume Next    
    
    Set conn = Server.CreateObject("ADODB.Connection")
    Set cmd  = Server.CreateObject("ADODB.Command")
    Set rs   = Server.CreateObject("ADODB.Recordset")
    
    query = "SELECT table_name, column_name " & _
        "FROM information_schema.view_column_usage " & _
        "WHERE table_schema='" & schema_name & "' AND view_name='" & vName & "'"
    
    conn.Open dsn
    ErrTrap "fail: ado-conn-open columns"
    
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    rs.LockType = adLockReadOnly
    
    Set cmd.ActiveConnection = conn
    
    cmd.CommandType = adCmdText
    cmd.CommandText = query
    rs.Open cmd
    ErrTrap "fail: ado-rs-open columns"
    
    If Not(rs.BOF And rs.EOF) Then
        Response.Write "<table width=100% border=1 cellpadding=4 cellspacing=1>"
        Response.Write "<tr><td>Table</td><td>Column</td>"
        Response.Write "</tr>" & vbCRLF
        Do Until rs.EOF
            ordinal  = rs("ordinal_position").value
            colname  = rs("column_name").value
            datatype = rs("data_type").value
            maxlen   = rs("maxlen").value
            nullable = rs("is_nullable").value
            numprec  = rs("numeric_precision").value
            coldef   = rs("column_default").value
            Response.Write "<tr>" & vbCRLF
            Response.Write "<td style=width:180px>'" & rs("table_name").value & "</td>"
            Response.Write "<td>" & rs("column_name").value & "</td>"
            Response.Write "</tr>" & vbCRLF
            rs.MoveNext
        Loop
        Response.Write "</table>" & vbCRLF
        rs.Close
        conn.Close
        Set rs = Nothing
        Set cmd = Nothing
        Set conn = Nothing
    End If
End Sub

'----------------------------------------------------------------

Do Until rs.EOF
    If rs("table_type").value = "BASE TABLE" Then
        Response.Write "<h4>" & rs("table_name").Value & "</h4>" & vbCRLF
        TableColumns rs("table_name").Value
    End If
    rs.MoveNext
Loop

rs.MoveFirst
Do Until rs.EOF
    If rs("table_type").value = "VIEW" Then
        Response.Write "<h4>" & rs("table_name").Value & "</h4gt;" & vbCRLF
        TableColumns rs("table_name").value
        ViewTables rs("table_name").value
        Response.Write "<br/>"
    End If
    rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing

Using GMail for CDOsys SMTP Relaying

I am in the process of moving one of my externally-hosted web sites to an internal host on my home network.  But one of the problems I’ve been working on is how to continue sending e-mail messages without setting up my own SMTP relay host.  I wanted to be able to make the process portable, so it could be used on any host as long as the server has an active Internet connection.  Thanks to a post on Google Groups, I was able to do this.  I’ve taken that code example and made it into a SUB so it’s a little easier to re-use as well.  A small but useful modification…

'----------------------------------------------------------------
' function: send cdosys message using GMail as SMTP relay host
' from: http://groups.google.com/group/hosted-the-basics/browse_thread/thread/c6cc889c9db0a02b?pli=1
'----------------------------------------------------------------

Sub SendGmail(RecipientEmail, SenderEmail, Subject, msgBody, msgFormat)
    On Error Resume Next
    Dim SMTPServer, SMTPusername, SMTPpassword
    SMTPserver   = "smtp.gmail.com"
    SMTPusername = "YOU@gmail.com"
    SMTPpassword = "YOUR_PASSWORD"
    sch = "http://schemas.microsoft.com/cdo/configuration/"
    Set cdoConfig = CreateObject("CDO.Configuration")
    With cdoConfig.Fields
        .Item(sch & "smtpauthenticate") = 1
        .Item(sch & "smtpusessl") = True
        .Item(sch & "smtpserver") = SMTPserver
        .Item(sch & "sendusername") = SMTPusername
        .Item(sch & "sendpassword") = SMTPpassword
        .Item(sch & "smtpserverport") = 465
        .Item(sch & "sendusing") = 2
        .Item(sch & "connectiontimeout") = 100
        .update
    End With
    Const cdoSendUsingPickup = "c:\inetpub\mailroot\pickup"
    Set cdoMessage = CreateObject("CDO.Message")
    Set cdoMessage.Configuration = cdoConfig
    cdoMessage.From = SenderEmail
    cdoMessage.To = RecipientEmail
    cdoMessage.Subject = Subject
    If Ucase(msgFormat) = "TEXT" Then
        cdoMessage.TextBody = msgBody
    Else
        cdoMessage.HTMLBody = msgBody
    End If
    cdoMessage.Send
    Set cdoMessage = Nothing
    Set cdoConfig = Nothing
    If Err.Number <> 0 Then
        Response.Write "error: " & _
            err.Number & " - " & err.Description & _
            "<br /><br />"
    End If
End Sub

SendGmail SendTo, SendFrom, Subject, MessageBody, "TEXT"

Be sure to edit the values in RED above before trying to use this.  Also, be sure to use your Gmail account as the SendFrom address value or it may not be delivered successfully.  You can easily modify this to work as VBScript by changing the Response.Write statements to Wscript.Echo, if  you want to.

In case you happen to be a TextPad user (like I am), I have added this to the ASP Clip Library posted on my Scripting Resources site at http://www.steinvb.net/scripting.php  Yes, I know it’s a bit ironic that I post something about ASP on a page written in PHP, but that’s how my brain works anyway.