Identify IE Version Installs using SCCM, SQL, Chewing Gum and Coffee

You could hunt down the Add or Remove Programs list, or tunnel your way through v_GS_INSTALLED_SOFTWARE_CATEGORIZED, or walk around with a clipboard and a baseball bat, or you could do it the easy way:  a SQL query against v_GS_SoftwareFile.  Be sure to change the database name tag to whatever your site code is.

[begin code]

USE your_site_database_name

GO

SELECT DISTINCT 
  a.netbios_name0 COMPUTER_NAME, 
  CASE 
    WHEN PATINDEX('%.%',b.fileversion) = 3 THEN 

      SUBSTRING(b.fileversion,1,2) 
    WHEN PATINDEX('%.%',b.fileversion) = 2 THEN 

      SUBSTRING(b.fileversion,1,1) 
    ELSE SUBSTRING(b.fileversion,1,1) 
  END AS IEX 
FROM 

  dbo.v_R_System a LEFT OUTER JOIN 

  dbo.v_GS_SoftwareFile b ON a.ResourceID=b.ResourceID 
WHERE 

  filename LIKE 'iexplore.exe' AND Active0=1
  AND 

  LTRIM(fileversion) <> ''

ORDER BY COMPUTER_NAME

[end code]

Namaste!

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.

How Old is a Computer?

Let's pretend it's exam time, mmmkay?  Goody!  I know you're jumping out of your seat with joy right now, so let's begin.

Scenario:  You're at your office desk on Monday morning, giggling out loud while reading the latest Dilbert strip on the web, when your phone suddenly rings. You normally ignore it, but the LCD (ok, maybe you have Lync alerts enabled) shows "CIO" is calling.  You sip your coffee/RedBull/Monster/hot tea/etc. and swallow before answering.

You: "Systems Engineering.  You stab 'em, we slab 'em.  How can I help you?"

CIO: "I need a report that shows how old each computer in our organization is, sorted by the oldest at top.  How soon can you have that to me?"

You: "Uhhhhhhhhh....."

You pause and realize you have Microsoft System Center Configuration Manager 2012 SP1 installed and everything is working smoothly, including inventory and reporting.  Then you realize that "age" isn't so clear cut of a thing when it comes to computers.  To avoid sounding like an idiot, you respond with your usual clever answer:

"I think I may have what you need, but let me verify anyway and I'll get back to you as soon as possible.  Would that be okay, [sir/ma'am]?"

CIO: "That's fine.  I'll need an answer before the board meeting at noon."

*click*

Question:  What is the most reliable method of determining the "age" of a given physical computer (server, desktop, laptop, tablet, etc.):

1. The install date of the operating system
2. The BIOS firmware date
3. The dateCreated property of the Active Directory account
4. The Purchase Order (PO) date
5. The manufacture's model sticker on the back/underside of the box
6. The CPU version information
7. The motherboard version information

Answer:  __ ?

4. The Purchase Order (PO) date

Unfortunately, options 1, 2, and 3 are easily changed by routine processes in the environment.  Option 5 isn't accessible from a programmatic (e.g. WMI, SNMP, etc.) perspective.  Options 6 and 7 don't necessarily indicate an aggregate "date" on which the computer began "life" (whatever that's defined as being).

That leaves option 4.  If your purchase order and invoicing system is online (rather than paper), and you have the means to tap into its database, you could run some queries and get what the CIO needs.  If the database is linkable to the Configuration Manager site database, you can do some SQL "joins" to leverage the goods on both sides of the aisle.  This makes for an easy CIO-pleasing result.

If your PO system is paper-based, or isn't accessible to running custom reports, well, you may be shit-out-of-luck.  But all is not lost!  If you stop and think about who was responsible for requesting the shitty, inefficient PO system for the organization, and that person is not on your list of friends, it could be an opportunity to play the office politics game and toss out one of those "See! I told ya so!" cards and call for a show of hands.  Then again, you may simply be shit-out-of-luck, in which case, you might want to finish your drink, take a deep, slow, meditative breath, and call the CIO back with the not-so-good news.

It's surprising, to me anyway, how often this situation arises.  A "computer" device isn't as monolithic as a human in some respects, which may sound really strange and ironic. A human has a single "birth date", which can be verified via a birth certificate, passport, military I.D., or driver's license.  A computer starts off with a duality of hardware + software, and even then, some of the hardware isn't so hard-coded (firmware updates).  If only computers had a singular, reliable, consistent "birth certificate".  Imagine what the little inked foot prints might look like. :)

Who is Ralph Grabowski? And Why CAD Pro's Should Read His Stuff

Ralph Grabowski has been writing about the world of engineering-related and design-related software for quite a long time.  And that world has continued to grow and evolve without slowing down.

When I started working in the "design" field, it was 1984.  The predominant technology of the time was a wooden board covered with a plastic-film graph paper, a cable-mounted "drafting machine", and a stack of stencils and templates.  The medium was one of the following:  Tracing paper, Sepia, or Mylar.  The instrumentation was usually a mechanical pencil with either an H-series graphite lead, or (more often) plastic "lead" of either E0, or E1 grade.

In 1985 that relatively arcane world started to fade away at a steady pace, and a new breed of computers and software began taking its place.  First were the mainframe systems, like Autotrol and CADAM.  Then came a few more, like Computervision, UniGraphics, Pro/Engineer, and Intergraph.  Then wallets started to evaporate.  The cost for mainframe, and later the more compact "workstation" packages, was astronomical. As in: you'd need an observatory telescope to see the end of the price tag.  It was scary.

Renegade companies, with dreams of producing cheaper alternatives on the newer (and more affordable) MS-DOS PC-platform, started springing up, with names like GenericCAD, DesignCAD, Autodesk, FastCAD, Drafix, This-CAD and That-CAD, and too many others to recall now.  Some survived for a while, some died out, and a few remained and exist to this day.  And through a big portion of this timeline, most of it actually, there have been a few journalists who've tried to get a handle on just what this "CAD/CAM" and "CAD/CAM/CAE" stuff was all about, and more importantly: Where it was all going.

One of them has been, and still is, Ralph Grabowski.  His newsletter, upFront.eZine, has been cranking out in-depth reviews, interviews, news, and events for as long as I can remember.  A mix of web-content and e-mailed content, it is an enormously valuable resource for engineers, designers, managers, software developers, start-up visionaries, and anyone just plain curious about this unique segment of the technology world.

Although my personal and professional involvement with the design world somewhat ended a few years back, I'm still tied to parts of it by way of my role as an IT consultant.  I still package and deploy CAD products for various environments, and I still get called in to consult and work with FlexLM and FlexNet implementations.  For that reason alone, I still read the upFront.eZine newsletter to stay current with what's going on.

For continuing to push forward and keep us all clued-in:  Thank you Ralph!

Config Manager Queries: CPU Types

I probably should revive my old ScriptZilla blog for stuff like this, but the heck with it.  I'm just posting all this here from now on.  After all: It is the brain-skattering blogness that I'm kicking around, if that even makes any sense.

This is a simple SQL query to fetch all the unique CPU manufacturers and names within your inventoried ball of confusion...

SELECT DISTINCT Manufacturer, Name, COUNT(Name) AS QTY
FROM dbo.v_LU_CPU
GROUP BY Manufacturer, Name 
ORDER BY Manufacturer, Name

Here's an example using VBScript (example is using a DSN-less connection with an explicit SQL user account and password. You can obviously run this under SSPI or "trusted" context, or using a stored DSN)

dsn = "DRIVER=SQL Server;SERVER=DBServer1;database=SMS_ABC;UID=username;PWD=password;"

Set conn = CreateObject("ADODB.Connection")
Set cmd  = CreateObject("ADODB.Command")
Set rs   = 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
    xrows = rs.RecordCount
    Do Until rs.EOF
        For i = 0 to rs.Fields.Count -1
            wscript.Echo rs.Fields(i).Name & vbTab & rs.Fields(i).Value
        Next
        rs.MoveNext
    Loop
Else
    wscript.echo "no records found, bummer."
End If

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

I was going to post a PowerShell example, but going from v2 to v3 I'm finding all sorts of confusing recommendations about the "best way" to invoke a simple T-SQL "SELECT" query against a remote SQL Server that my head is already spinning. Even some that just recommend installing custom cmdlet extensions, and whatnot. If anyone wants to point me to a nice, simple, concise, example (i.e. equal or fewer lines of code than the VBScript example above) please post a reply. Gracias!

Configuration Manager: Exploring the Database Goodies, Part 1

I spend a lot of time crawling around in the tables and views of Configuration Manager site databases.  There are enough tables and views to spend a lifetime analyzing and discussing them.  There are quite a few that are very useful for custom reports, extensible applications development and good ole fashioned data mining.  Some of these are:

• v_R_SYSTEM
• v_GS_COMPUTER_SYSTEM
• v_GS_SYSTEM_ENCLOSURE
• v_GS_INSTALLED_SOFTWARE_CATEGORIZED (phew!  Long name!)
• v_GS_OPERATING_SYSTEM
• v_GS_X86_PC_MEMORY

...and dozens more.  The real power in these comes from judicious use of SQL "JOIN" operations, whereby you merge pertinent and relevant data from two or more tables or views (or tables and views) to get an aggregate result.

For example, the following query pulls all Laptop systems that have less than 2,048 MB of memory (2 GB's), but it does a little more.  You may notice another database schema being referenced (ABC_SCCM).  This is a separate database I created on the same SQL Server instance, where I have created a TABLE named ADUsers.  This is where a daily process queries the Active Directory environment, truncates and re-populates the table to keep it up to date with user accounts in the organization.  (Note, there are other ways to accomplish this, but this is just one way)...

SELECT dbo.v_GS_COMPUTER_SYSTEM.Name0 AS ComputerName, 
   dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, 
   dbo.v_R_System.User_Name0 AS UserID, 
   ABC_SCCM.dbo.ADUsers.Fname+' '+ABC_SCCM.dbo.ADUsers.Lname AS FullName, 
   ABC_SCCM.dbo.ADUsers.Dept AS Department, 
   dbo.v_R_System.AD_Site_Name0 AS SiteName,  
   dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS Memory 
FROM dbo.v_GS_COMPUTER_SYSTEM INNER JOIN 
   dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = 
dbo.v_R_System.ResourceID 
   INNER JOIN 
   dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = 
dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID 
   LEFT OUTER JOIN 
   dbo.v_GS_X86_PC_MEMORY ON dbo.v_R_System.ResourceID = 
dbo.v_GS_X86_PC_MEMORY.ResourceID 
   LEFT OUTER JOIN 
   ABC_SCCM.dbo.ADUsers ON dbo.v_R_System.User_Name0 = 
ABC_SCCM.dbo.ADUsers.Userid 
WHERE (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN 
(8, 9, 10, 11, 12, 14, 18, 21)) 
   AND (dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 < 2097152) 
ORDER BY dbo.v_GS_COMPUTER_SYSTEM.Name0

Now I can view the following attributes for each row in the results:

• ComputerName (NetBIOS name)
• Model Name
• UserID (sAMAccountName from AD account)
• User Full Name (concatenated from First and Last Name values)
• User Department
• AD Site Name
• Computer Memory (in Kilobytes)

I've been asked quite a few times what the difference between two of these nested VIEW objects: v_GS_COMPUTER_SYSTEM, and v_R_SYSTEM (or v_R_SYSTEM_VALID).

Basically, v_R_SYSTEM is populated by site discovery data, and v_GS_COMPUTER_SYSTEM is populated by client hardware inventory data.  So, from a sequential  or chronological aspect, v_R_SYSTEM is normally populated first, because client systems are typically discovered before they are installed and inventoried.  The net result is that during that gap in events, the resource (computer object within Configuration Manager) is available for management from a Collection perspective.  In other words, you can add the resource to a Collection before it's been inventoried, even before it's had a ConfigMgr client installed.

You obviously don't need to join VIEWs or TABLEs to get useful results. For example, you can find out the counts of computers by each manufacturer in your environment...

SELECT DISTINCT Manufacturer0 AS Manufacturer, 
  COUNT(*) AS QTY 
FROM dbo.v_GS_COMPUTER_SYSTEM 
GROUP BY Manufacturer0 
ORDER BY Manufacturer0

This report only uses v_GS_COMPUTER_SYSTEM, but the limitation is that it can only report from computers which have submitted hardware inventory data. That's always a bad thing, nor is it always a real limitation. It depends on what your needs are, and what your environment is like.

But when you need to pull more information, it often falls in separate VIEWs or TABLEs, such as finding all of the computers for a given user account.  In other words, find all the computers where a specific user account is shown as the "Primary User".  You can get that from one VIEW (v_R_SYSTEM) but if you also want to see the Model of those computers, you will need to get that from another VIEW (v_GS_COMPUTER_SYSTEM), for example...

SELECT DISTINCT dbo.v_R_System_Valid.ResourceID, 
   dbo.v_R_System_Valid.Netbios_Name0 AS ComputerName, 
   dbo.v_R_System_Valid.User_Name0 AS UserName, 
   dbo.v_R_System_Valid.User_Domain0 AS Domain, 
   dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, 
   dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model 
FROM dbo.v_R_System_Valid LEFT OUTER JOIN 
   dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System_Valid.ResourceID = 
   dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
WHERE (dbo.v_R_System_Valid.User_Name0 LIKE '%johndoe%') 
ORDER BY ComputerName

You may be wondering what the difference is between v_R_SYSTEM and v_R_SYSTEM_VALID. Ok, besides the "VALID" part, the difference is really based on the IsObsolete and IsDecommissioned fields. If these two fields are not "True", then the resource is included in v_R_SYSTEM_VALID, making it a logical subset of what v_R_SYSTEM contains. For more information, check out this TechNet article.  This may seem trivial, but the more you work with these views, and the more you rely upon them, the more this small distinction will matter.

Conclusion

I will hopefully be posting more on this subject.  I have been so busy with my head shoved up the SQL ass of Configuration Manager for so long that I really just didn't think about sharing my experiences with it all until now.  I will try to balance the posts between "raw" T-SQL and query aspects, as well as the more discreet implications of using it within scripts and web applications.  In the meantime, post a reply/comment if you have any questions or suggestions for future posts?  Thank you!