The Missing SCCM Database View

For most interfaces I've worked on over the past ten years, which involve poking a stick at System Center Configuration Manager 2007 or 2012, this is one database view (or stored proc) that I'm usually building for myself.  It basically tosses about eight built-in Views and Tables into a blender, with a dash or two of stupid sauce and provides a fairly good picture of each computer in your site database.

It spews out the computer name, client info, site code, manufacturer, model, memory, Windows flavor, chassis type (I didn't cross-join that to produce names, but you can do that, I'm too lazy right now), BIOS info and serial number, CPU, AD stuff, install date, and of course, my personal favorite: the SCCM distribution point reference.  Note that I've provided a fall-back DP name for those which are not explicitly mapped via a site boundary, so they come back home to an MP usually (not always).  I used "DP0001" but you should stuff your own name in place of that.

[begincode]

SELECT DISTINCT
v_R_System.Name0,
v_R_System.ResourceID,
v_R_System.Client0 AS ClientInstalled,
v_R_System.Client_Version0 AS ClientVersion,
v_R_System.Creation_Date0 AS ClientDate,
v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 AS SiteCode,
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer,
v_GS_COMPUTER_SYSTEM.Model0 AS ModelName,
v_GS_COMPUTER_SYSTEM.SystemType0 AS SystemType,
v_GS_OPERATING_SYSTEM.Caption0 AS OSName,
v_GS_OPERATING_SYSTEM.CSDVersion0 AS ServicePack,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS Memory,
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 AS ChassisType,
v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS SerialNumber,
v_GS_PC_BIOS.Manufacturer0 AS BIOSvendor,
v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS BIOSversion,
v_GS_PC_BIOS.ReleaseDate0 AS BIOSdate,
v_GS_PROCESSOR.Name0 AS CPU,
v_R_System.AD_Site_Name0 AS ADSiteName,
v_GS_COMPUTER_SYSTEM.UserName0 AS ADUserName,
v_GS_COMPUTER_SYSTEM.Domain0 AS ADDomain,
v_GS_OPERATING_SYSTEM.InstallDate0 AS OSInstallDate,
v_BoundaryInfo.BoundaryID,
COALESCE (ProtectedSiteSystem_ARR.ServerName, 'DP0001') AS DPServer
FROM dbo.ProtectedSiteSystem_ARR AS ProtectedSiteSystem_ARR
INNER JOIN
dbo.v_BoundaryInfo AS v_BoundaryInfo
ON ProtectedSiteSystem_ARR.BoundaryID = v_BoundaryInfo.BoundaryID
RIGHT OUTER JOIN
dbo.v_R_System AS v_R_System INNER JOIN
dbo.v_GS_PROCESSOR AS v_GS_PROCESSOR
ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID
INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM AS v_GS_COMPUTER_SYSTEM
ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
INNER JOIN
dbo.v_GS_SYSTEM_ENCLOSURE AS v_GS_SYSTEM_ENCLOSURE
ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
INNER JOIN
dbo.v_GS_X86_PC_MEMORY AS v_GS_X86_PC_MEMORY
ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID
INNER JOIN
dbo.v_GS_OPERATING_SYSTEM AS v_GS_OPERATING_SYSTEM
ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
INNER JOIN
dbo.v_GS_PC_BIOS AS v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID
INNER JOIN
dbo.v_RA_System_SMSAssignedSites AS v_RA_System_SMSAssignedSites
ON v_R_System.ResourceID = v_RA_System_SMSAssignedSites.ResourceID
ON v_BoundaryInfo.Value = v_R_System.AD_Site_Name0

ORDER BY v_R_System.Name0

[endcode]

To test this out, open your SQL Management Studio.  Connect to your site database server and site database instance.  Then click "New Query" and paste the code in and press F5 to run it.  Add a splash of your favorite alcoholic beverage, some Barry White music, dim the lights, and enjoy the vibe.  Peace.

My New Book is Out! The AutoCAD 2015 Network Administrator's Bible

"The AutoCAD 2015 Network Administrator's Bible" covers everything from new features and requirements, to building Network Deployment Shares, to deploying with Scripts, Microsoft Deployment Toolkit 2013, and System Center Configuration Manager 2012. 

There are also tips for working with VMware Workstation as a test environment,  handling .NET dependencies on Windows 7 and Windows 8, and slip-streaming updates and service packs during deployments.

I hope you like it. Please post feedback on the Amazon site to share your thoughts after checking it out? I'd really like to hear from you.

Cheers!

Coming Soon: The AutoCAD 2015 Network Administrator's Bible

It's long overdue.  I'm long overdue as well.  It's been a long time since I've devoted myself to writing anything about AutoCAD or network deployments.  I'm almost done with editing this book and it should be available for purchase on Amazon Kindle very soon.  Remember that you do not need a Kindle device to read Kindle books.  There are free Kindle reader apps for iOS, Android, Windows, Mac, and more.

Here's a summary of topics included:

• Deploying with Scripts: Batch, VBScript, and PowerShell
• Deploying with System Center Configuration Manager 2012
• Deploying with MDT 2013
• Using Task Sequences
• Dealing with Requirements: .NET 3.5, using Global Conditions, etc.
• ADNM
• Deployment Shares
• Network and Client Logs
• Building a Virtual Test Environment with VMware Workstation
• Stupid jokes.  Dumb comments.  Awkward silences.
• And more!

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!

Random SCCM Database Thoughts

I ran these on a SCCM 2007 environment, but most of them should work in 2012 R2 as well.

Crack open your SSMS console, swallow your entire Espresso, crack your knuckles, inhale deep and slow, and let it out deep and slow.  Then scream something stupid and look serious.  Now, let's get started...

List the computers in a particular AD Site, and identify their makes, models, and BIOS serial numbers...

• Join v_R_System with v_GS_Computer_System and v_GS_System_Enclosure on ResourceID (using LEFT joins to avoid dropping those which don't report inventory yet).  Then group by the AD_Site_Name0 field.

• Step 1, filter on the following view-joins to see the general scope of data...

SELECT DISTINCT
   dbo.v_R_System.ResourceID, dbo.v_R_System.AD_Site_Name0, 
   dbo.v_R_System.Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, 
   dbo.v_GS_COMPUTER_SYSTEM.Model0, 
   dbo.v_GS_COMPUTER_SYSTEM.SystemType0, 
   dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0
FROM dbo.v_R_System LEFT OUTER JOIN
   dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID =
      dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN
   dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID =
      dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID

• Step 2, hone it down...

SELECT DISTINCT

   dbo.v_R_System.ResourceID, dbo.v_R_System.AD_Site_Name0, 

   dbo.v_R_System.Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, 

   dbo.v_GS_COMPUTER_SYSTEM.Model0, 

   dbo.v_GS_COMPUTER_SYSTEM.SystemType0, 

   dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0

FROM dbo.v_R_System LEFT OUTER JOIN

   dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID =

      dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN
   dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID =

      dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID

WHERE dbo.v_R_System.AD_Site_Name0 = 'DOUBLE_HEADED_DONG_FACTORY'

Find all clients which are assigned to a particular IPv4 gateway...

• Step 1, just for fun, filter and browse the results of round 1, using v_Network_Data_Serialized

SELECT DISTINCT 
   DNSHostName0, ResourceID, IPSubnet0, MACAddress0, 
   IPAddress0, DHCPEnabled0, DHCPServer0, DNSDomain0, DefaultIPGateway0
FROM dbo.v_Network_DATA_Serialized
WHERE (IPSubnet0 IS NOT NULL)
   AND (DHCPEnabled0 = 1)
   AND (IPAddress0 NOT LIKE 'f%')

• Step 2, go in for the kill.  Find all that are using gateway 192.168.2.11...

SELECT DISTINCT 

   DNSHostName0, ResourceID, IPSubnet0, MACAddress0, 

   IPAddress0, DHCPEnabled0, DHCPServer0, DNSDomain0, 

   DefaultIPGateway0
FROM dbo.v_Network_DATA_Serialized
WHERE (IPSubnet0 IS NOT NULL) 

   AND (DHCPEnabled0 = 1) 

   AND (DefaultIPDGateway0='192.168.2.11')

ORDER BY DNSHostName0

List the unique AD Site Names for all computers in a given Collection...

• Join v_R_System with a sub-query on the desired Collection "ABC12345".

SELECT DISTINCT AD_Site_Name0 dbo.v_R_System
WHERE dbo.v_R_System.ResourceID IN
   (SELECT ResourceID FROM dbo.v_CM_RES_COLL_ABC12345)

List all of the Distribution Point Servers in site "ABC"...

• Filter on View named v_SystemResourceList...

SELECT SiteCode,ServerName
FROM dbo.v_SystemResourceList
WHERE SiteCode='ABC' AND RoleName='SMS Distribution Point'
ORDER BY ServerName

List distinct Site Server Role type/names in the database, along with counts of servers for each role (keep in mind that servers can provide multiple roles, so don't sum the totals and think that's an accurate count of total site servers)

• Filter on View named v_SystemResourceList...

SELECT DISTINCT RoleName, COUNT(*) AS ServerCount
FROM dbo.v_SystemResourceList
GROUP BY RoleName
ORDER BY RoleName

List User Account status values and counts for each.

• Start with a basic SQL query to identify the unique values for column User_Account_Control0 from view named v_R_User

SELECT DISTINCT User_Account_Control0, COUNT(*) AS UserCount
FROM dbo.v_R_User
GROUP BY User_Account_Control0

• Then add a dash of SQL "CASE" statement with some Oregano and Basil (for other values to match up, check out Rajnish's blog post here)...

SELECT DISTINCT 

User_Account_Control0, 

COUNT(*) AS UserCount, 

CASE User_Account_Control0 

WHEN 512 THEN 'Enabled' 

WHEN 514 THEN 'Disabled' 

WHEN 544 THEN 'Enabled Must Change Password' 

WHEN 66048 THEN 'Enabled Password Never Expires' 

ELSE 'You can code the others...' 

END AS UAC_Name 

FROM dbo.v_R_User 

GROUP BY User_Account_Control0

List computers a particular AD user has logged onto within the past 30 days...

• Find logins for user "doofus" on domain "contoso".  Join v_R_System with v_GS_SYSTEM_CONSOLE_USER on ResourceID and filter on the SystemConsoleUser0 column.  Then add a DateDiff() filter to restrict on logons within the last 30 days...

SELECT 
   dbo.v_R_System.Name0 AS ComputerName, 
   dbo.v_GS_SYSTEM_CONSOLE_USER.ResourceID,
   dbo.v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS LastLogon,
   dbo.v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS NumberLogons,
   dbo.v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS UserID,
   dbo.v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS LogonTotalTime
FROM dbo.v_GS_SYSTEM_CONSOLE_USER INNER JOIN
   dbo.v_R_System ON dbo.v_GS_SYSTEM_CONSOLE_USER.ResourceID =
      dbo.v_R_System.ResourceID
WHERE 
   (dbo.v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 = 'contoso\doofus')
   AND
   (DATEDIFF(dd, dbo.v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0, GETDATE()) < 30)

Need to identify Advertisements pointed at Direct-membership Collections?

• Join v_Advertisement to v_Package, and v_Collection, and sub-query against v_CollectionRuleDirect using CollectionID as the filtering column...

SELECT 
   dbo.v_Advertisement.AdvertisementID, 
   dbo.v_Advertisement.AdvertisementName, 
   dbo.v_Advertisement.PackageID, 
   dbo.v_Package.Name, 
   dbo.v_Advertisement.CollectionID,
   dbo.v_Collection.Name AS CollectionName
FROM dbo.v_Advertisement INNER JOIN
   dbo.v_Collection ON dbo.v_Advertisement.CollectionID =
      dbo.v_Collection.CollectionID INNER JOIN 
   dbo.v_Package ON dbo.v_Advertisement.PackageID =
      dbo.v_Package.PackageID
WHERE (dbo.v_Collection.CollectionID IN
   (SELECT DISTINCT CollectionID FROM dbo.v_CollectionRuleDirect)) 
ORDER BY 
   dbo.v_Advertisement.AdvertisementName

Need to computers with every version of Internet Explorer?

• Well, you might expect to query v_GS_Installed_Software_Categorized or the ARP tables, but remember that IE10 and 11 came out as KB updates for some platforms.  So best to query v_GS_Software_Product.  Note the some entries (ProductName0 LIKE 'Internet Explorer%') OR (ProductName0 LIKE 'Windows%Internet Explorer%') will produce the version within the product name, while others will only show "Internet Explorer" and the version in the ProductVersion0 column.  Drink plenty of coffee and enjoy that.  Don't forget to filter out the double counted items (yes. they are hiding there).  Don't be surprised if you need to crack open your dusty T-SQL book and brush up on the CASE statement.  I'll let you have fun with this one, and I'll post my take on it later.

If I get more coffee in me and feel motivated, I may post more.  Let me know if these are helpful?

SCCM Lab Setup Laziness with PowerShell and Duct Tape

I've been dusting off my pointy little head with another round of "lets play set up with Configuration Manager!" and, well, I hate using GUI tools or command line stuff if a script will save me time.  The time invested in smacking the keyboard and making grunting noises and laughing hysterically is recouped later with spare change and coffee spilling, so it pays off.

Disclaimer:  I really don't have any duct tape right now, so you'll have to go without on this one.

Anyhow, I've been following along with a template procedure my buddy and trusted ass-kicking extraordinaire colleague and uber-technowunderkind: Chris DeCarlo compiled.  One part of this procedure has me creating a bunch of AD user accounts to tie to various things.  Some are for SQL services, others for AD tasks, and obviously some are for SCCM itself.  (btw- Chris, you did an outstanding job on this document. Kudos!)

Here's the accounts (sorry man, but I modified a few names out of brain-damaged habit).  The short names on the left are the sAMAccountName values, and to the right are their descriptions / explanations.

• SCCMRS - SQL Reporting Services publishing account
• SCCMNA - SCCM network access account
• SCCMInstall - SCCM site server install account
• SCCMDomJoin - SCCM domain joining account
• SCCMClient - SCCM client push account
• SCCMSQLSvc - SCCM SQL Server service account
• SCCMSQLAgent - SCCM SQL Agent service account
• SCCMOSD - SCCM OSD deploy and capture account

You may or may not need (or want) to create all of these, but I have the keyboard so this plane is going into the mountain and I'm the captain... so hold on.

In addition to this, I'm lazy.  Yes, I know that's a shock.  I'll wait as you pick your jaw off the floor.  (tap tap tap tap - eyes on phone,...) ok.  Rather than doing this the "right way", I do it (for lab purposes only) the "easy unrecommended way", which is to stuff all of these accounts into the "Domain Admins" group and then laugh as loud as possible.

You need two files (okay, you don't really NEED two files, but for this example it works):

• A Comma-Separated Values file (.csv)
• A PowerShell script (v3 or v4)

Assumptions

1. Domain is "fubar.local"
2. OU is created at root of the domain as "ServiceAccounts"
3. You are logged onto the server/desktop in the LAB as a Domain Admin user
4. You have faith in what I'm telling you (rotfl! okay, just kidding)

I built and tested this cardboard thing using Windows Server 2012 R2 with PowerShell v4 and some coffee, chewing gum and a few chicken drumsticks my wife just cooked (damn good too).

[CrappyCode]

$inputFile = Import-CSV  "useraccounts.csv"

$strPwd = "Tarfu123"

$ouPath = "OU=ServiceAccounts,DC=fubar,DC=local"

foreach($strLine in $inputFile) {

$cn = $strLine.cn

$samid = $strLine.sAMAccountName

$ln = $strLine.sn

$fn = $strLine.givenname

$dn = $strLine.displayname

$desc = $strLine.description

$upn = $strLine.UserPrincipalName

New-ADUser -SamAccountName $samid -Name "$cn" -UserPrincipalName $upn -AccountPassword (ConvertTo-SecureString -AsPlainText "$strPwd" -Force) -Enabled $true -PasswordNeverExpires $true -Path "$ouPath" -Description "$desc"

}

$inputFile | % {Add-ADGroupMember -Identity "Domain Admins" -Member $_.sAMAccountName } 

[/CrappyCode]

If you're not familiar with PowerShell, or scripting in general, you don't need to copy the [CrappyCode]. and [/CrappyCode] end tags.  Those are just for entertainment.  You will want to edit the domain names to protect the innocent, and whatever else you feel like modifying to suit your environmental needs.  The items in red are likely the items you will want to change for your needs.  

Also, the last line redirects the CSV piped content through a PowerShell pipeline into Add-ADGroupMember to stuff the new accounts into the Domain Admins group.  So easy, and cheap too.  Be careful of the line-wrapping headaches that come with copying from web browser windows. :)

The next piece is the CSV file (below).  Note that the first line contains the logical column headings, while the remaining lines are the actual data.  As long as the values are in the same relative order from left-to-right, it should work fine.  If you have values that contain apostrophes or commas be careful to "escape" them properly so they don't choke out the code like a backyard wrestling match gone wrong.

[CSV]

cn,givenname,sn,sAMAccountName,displayname,UserPrincipalName,description

SCCMRS,,,sccmrs,SCCM Reporting Services,sccmrs@fubar.local,SCCM SQL Reporting Services Account

SCCMNA,,,sccmna,SCCM Network Access,sccmna@fubar.local,SCCM Network Access Account

SCCM Install,,,sccminstall,SCCM Install,sccminstall@fubar.local,SCCM Server Installation Account

SCCMDomJoin,,,sccmdomjoin,SCCM Dom Join,sccmdomjoin@fubar.local,SCCM Domain Join Account

SCCMClient,,,sccmclient,SCCM Client Push,sccmclient@fubar.local,SCCM Client Push Account

SCCMSqlSvc,,,sccmsqlsvc,SCCM SQL Service,sccmsqlsvc@fubar.local,SCCM SQL Server Account

SCCMSqlAgent,,,sccmsqlagent,SCCM SQL Agent,sccmsqlagent@fubar.local,SCCM SQL Agent Account

SCCMOSD,,,sccmosd,SCCM OSD,sccmosd@fubar.local,SCCM OSD Deploy and Capture Account

[/CSV] 

Then, in your LAB environment (do not do this in production unless you like spending a lot of time in a courtroom with ugly people in suits), log on as a Domain Admin user, open the PowerShell console (right-click and select "Run as administrator"), and CD (change directory) to the path where you saved both of these files.

Then type in "powershell.exe -ExecutionPolicy Unrestricted -File useraccounts.csv

If you see a bunch of red text, you screwed up (probably as a result of believing what I tell you), but don't freak, go into the code and verify everything is neat and clean and the quotes are matched, etc.  Standard scripting/programming drudgery stuff.

When you're done, and assuming it works as intended (it did for me), you should see those accounts in the designated OU and each is a member of the "Domain Admins" group.

Cheers!

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. :)

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.

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: Database Exploration, Part 2 - Notes

Before I continue on with this is "theme" that I've started, there are some very important issues I need to discuss.  Rather than boring you with a long introduction, I will just dive in and hit each one as I go.  I'll warn you that this is article is taking a sharp turn into a dark tunnel of seriousness.  No joking around here.  Very unlike my usual goofy stuff, but it's important to cover this before I continue on.

SMS Provider vs. SQL Server

From a "purely technical" aspect, you can interact with, and manage, a Configuration Manager site data store through the SMS Provider interface, or through SQL Server (ADO or ADO.NET, etc.), however, you absolutely NEED to be careful to avoid some easy mistakes.  This is all within the context of building custom applications which interface with your Configuration Manager infrastructure.  This is also regardless of whether you are working with Configuration Manager 2007 or 2012.

• While you can query (retrieve) information from either interface, the SQL interface is usually much faster to execute. I'm obviously talking about using the ADO or ADO.NET pipeline.  However...
• NEVER attempt to update anything directly through the SQL Server interface! All operations that involve modifying site resources, collections, or settings (and so on), should be performed through the SMS Provider only.  Some examples include adding a Package to a Distribution Point, or adding a Resource to a direct-membership Collection.  Going around the SMS Provider can cause serious problems for your Configuration Manager site.  I'll spare you the lengthy explanation of how the inboxes and outboxes are spooled and de-spooled in the background, and how it all weaves in and out of the database 
• Executing intensive queries (or updates, for that matter) against the SMS Provider interface can impact Configuration Manager processing, especially if performed at peak processing times (discovery cycles, software deployments, etc.).  The net result may cause a backlog in data processing and show up in your component status logs as well.  Try to limit such activity to off-peak times or days to avoid impacting Configuration Manager itself.
• Executing intensive queries directly against the site SQL Server database may also impact performance, and should be carefully monitored by using SQL profiling and performance logs to determine the level and duration of such impact.
• Use the most efficient tool to handle a specific task:  If you are post-processing query results and spending a lot of code cycles calculating date differences, cost values, or mapping integers to string values - do that instead within the query!!!  SQL is so much faster and more efficient at many common data manipulation tasks than standard 3GL, 4GL programming languages or scripts.
• Minimize Connections!  If you have code firing off multiple queries, be sure to pay close attention to how you open and close your data connections.  If you can use one connection for all of your queries, do it.  It will save time and reduce the overhead impact on the data store host itself. This is true for using SQL Server or the SMS Provider.

Database Separation and Isolation

Most any DBA with a fair amount of experience will advise you to avoid direct interaction with "mission critical" data stores if you can instead use a replica.  It really boils down to how time-sensitive the information is that you rely upon to accomplish the required task.  If you need to generate inventory reports, and your inventory is only updated every day or week, you probably could do just fine by pointing your queries at a replica database and avoid adding more overhead on your production database.  It's just one more thing to consider if you are worried about performance impact.

The Right Tools

If you haven't used SQL Server Management Studio, or haven't used it much, give it a try.  In fact, if you're testing your queries through your code debugger, STOP.  That's a bad habit and can yield some very skewed results.  As the old saying goes: "Just because you CAN, doesn't mean you SHOULD".  I can't count the number of times I've asked a programmer to minimize their code debugger and run the same queries in the SSMS console, and seen their reaction to how different the performance can be.  It can really highlight where program code is slowing down a conversion or calculation step that could be more efficiently executed within the SQL statement.  

It's not really about SSMS.  Any tool that lets you model and execute T-SQL statements directly against the data store will work fine.  It's when you run the SQL expressions from within the program code that things can get twisted.  Eliminating secondary and tertiary processing layers ensures you get an accurate, honest and clear picture of what's going on.

Safety

Living on the edge is cool, if you get paid to do commercials for Red Bull.  For the rest of us, it helps if we take certain precautions to avoid letting simple mistakes explode into disastrous calamities.  If you have the option of a test environment, use it.  If not, employ test-environment methods to mitigate unintentional impact on production systems.  It's really that simple.

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!

Book Update

I posted some gibberish a few weeks ago about another book project.  Well, I'm getting close to wrapping it up, so I thought I'd go ahead and blabber about it in case anyone has anything they'd like to comment on or any topics they'd like to see included in it.

The book is titled: The AutoCAD Network Administrator's Bible - 2013 Edition

I know: Not very creative or clever.  However, this is not a typical dust-off and rehash book.  I've been painstakingly re-writing it from start to end.  Some of the major changes included in this book:

• AutoCAD 2013 network deployments
• Design Review 2013 deployments
• DWG TrueView 2013 deployments

In addition, I've updated the deployment vector scenarios and included much more detail for each...

• Deployments with System Center Configuration Manager 2007
• Deployments with System Center Configuration Manager 2012
• Deployments with Active Directory Group Policy
• Deployments with Microsoft Deployment Toolkit (MDT) 2012
• Deployments with VBScript, CMD / Batch script, and PowerShell

There's still the familiar topics, which have been updated for the current product versions as well...

• Troubleshooting Tips
• FlexLM License Server architecture and implementation considerations
• Software Deployment strategies
• Tools:  Sysinternals, XCACLS, REGINI, MSICUU, REG, etc.
• Client Performance Optimization

As I've said before, if you have any particular ideas or topics you'd like to see included, post a comment and let me know.  I'm getting close to finishing it, but I'd still hold off for a good suggestion.  I'm looking forward to wrapping this project up so I can focus on my family and day job more as Fall sneaks into Virginia.

Cheers!

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.

Help? Running Remote WMI/WBEM from IIS Applications

This post is really more of a question than an answer.

One of the projects I work on is an ASP web application that provides a management interface to integrate Active Directory, System Center Configuration Manager, various Inventory Databases, various business systems and so on.  We also heavily rely upon the MMC (console) use of Configuration Manager for a good portion of management tasks.  One of the things we also rely upon are the SCCM "Right-Click Tools" produced by Rick Houchins.  These are essentially a collection of client-side scripts (kept on the computer where the MMC is used, not on the remote client, however some remote script aspects are involved).  We also use the SCCM Client Center application, provided on SourceForge by Roger Zander.  Both are invaluable tools for efficient management of a Configuration Manager environment.

I've been asked if we can somehow integrate some of the functionality into the web interface, which is running from an IIS 7.5 instance on the intranet using AD integrated security.  I've done some research on alternative methods for making this work, but every option seems risky and klunky as well.

The only functions I've been asked to consider for this are the following:

• Retrieve and Evaluate the Machine Policy
• Re-Run Advertisement

Both of these features involve COM interface connection to the remote client using the CPApplet automation class.  I don't expect anyone to offer up detailed code or instructions or anything like that.  I'm just looking for general direction and advise.  Any help will be greatly appreciated.  Thank you!

Config Manager 2007 SQL Query for Advertisement Status

SQL query for pulling a list of client status information for a given advertisement in a Configuration Manager 2007 site database.  The columns returned are:

Name, ResourceID, Status, Status Detail, StatusTime, OS Name, Service Pack, AD Site Name

It also converts the UTC time value for LastStatusTime to local time, with DST offset.  Enjoy!

SELECT dbo.v_R_System.Netbios_Name0 AS Name, 
 dbo.v_ClientAdvertisementStatus.ResourceID, 
 dbo.v_ClientAdvertisementStatus.LastStateName AS [Status], 
 dbo.v_ClientAdvertisementStatus.LastStatusMessageIDName AS [Status Detail],
 CONVERT(datetime, 
            SWITCHOFFSET(

                CONVERT(datetimeoffset, 

                    dbo.v_ClientAdvertisementStatus.LastStatusTime),
  DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS StatusTime, 
 dbo.v_GS_OPERATING_SYSTEM.Caption0 AS OSName, 
 dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS SP, 
 dbo.v_R_System.AD_Site_Name0 AS SiteName 
FROM dbo.v_ClientAdvertisementStatus LEFT OUTER JOIN 
 dbo.v_GS_OPERATING_SYSTEM ON dbo.v_ClientAdvertisementStatus.ResourceID = 
 dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN 
 dbo.v_R_System ON dbo.v_ClientAdvertisementStatus.ResourceID = dbo.v_R_System.ResourceID 
WHERE (dbo.v_ClientAdvertisementStatus.AdvertisementID = 'ABC20778') 
ORDER BY Name

This came from a project I've been working on.

Autodesk Revit 2012 and Configuration Manager 2007: Win7 vs XP

While packaging Autodesk Revit 2012 Architecture Suite for mass deployment (via Microsoft System Center Configuration Manager 2007 R3), I encountered a problem where Windows 7 clients installed just fine, but Windows XP SP3 clients did not.  The error was 1603 or 1619, but the client log did not indicate any more specifics.  I ran the installation  (deployment) manually (interactively) and it worked fine on both platforms, but through Configuration Manager 2007 R3 it would not successfully install on Windows XP SP3 clients.  At all.  Ever.

Until...

It's an old trick that sometimes works, and in this case it did:

Within the Package, under the Program properties settings, on the Environment tab, check the option...

"Allow users to interact with this program"

It now installs on Windows XP SP3 clients as well as Windows 7 clients.