Brian Leary at myITforum.com

MDOP Exam 70-656 Goes Live - with Charter Member Swag!

bleary — Tue, 29 Jul 2008 03:17:00 GMT

Exam 70-656 for Microsoft Desktop Optimization Pack (MDOP) went live on June 19th. The first 500 exam takers may take the exam for free by using the Prometric Promo Code "MDOP", and the first 200 to pass the exam get a Charter Member jacket and glass award! If you are considering taking the exam, it consists of 75 questions, is over three hours long, and I recommend that you make sure that you know each component of MDOP very well in order to pass the test. I took it last Saturday and passed with a score of 748/700, so hopefully I am one of the first 200 so I get the cool swag!

http://www.mdopcharter.com/

Microsoft cracks down on certification exam cheating

bleary — Thu, 10 Jul 2008 02:36:00 GMT

I'm glad they are finally cracking down on this. If anyone gets caught cheating on an exam, they will be banned for life from taking any other Microsoft exams. Hopefully this will improve the integrity and credibility of Microsoft Certifications!

http://www.networkworld.com/newsletters/edu/2008/070708ed1.html

Where the Hell is Matt?

bleary — Wed, 09 Jul 2008 03:29:00 GMT

This guy named Matt quit his job to film himself dancing in different locations around the world. Stride Gum liked him so much they sponsored his second trip.

http://www.stridegum.com/#/mattsplace/

Hotfix Rollup 1 for Microsoft SoftGrid 4.2

bleary — Wed, 02 Jan 2008 21:33:00 GMT

Microsoft has released the Hotfix Rollup Package 1 for SoftGrid 4.2, which can be found at http://support.microsoft.com/kb/941408.

This cumulative update for SoftGrid v4.2 provides the latest updates to SoftGrid 4.2. In addition to stability improvements, this update contains the following changes:

• Improvements to the package upgrade when you import upgraded sequencings into the SoftGrid Server Management Console snap-in.
• Improvements to the package downgrade operation.
• Improvements to nonpaged pool usage when you sequence large applications.
• Improvements when you sequence applications that use both the Microsoft .NET Framework 1.1 and the .NET Framework 2.0.
• Improvements to command-line parameter handling of virtualized child processes.

New capabilities
This update also provides the following new capabilities:

• Support for the MSI Utility for Microsoft Application Virtualization.
• Support for SoftGrid Server components that run under WOW64 in 64-bit versions of Windows.

SCCM 2007 Exam Study Guide - Question #1

bleary — Thu, 15 Nov 2007 06:42:00 GMT

Whenever a new software product comes out, I always have this impending dread about having to read a bunch of documentation on it or waiting for a decent book to be released on the subject so that I can pass the exam on it someday. Since I am already fairly entrenched in the SCCM 2007 Documentation, I am going to save you some time and start sharing some of the key points of the product with all of you by writing up some sample exam questions that can help you to learn SCCM 2007. I will try to release these questions on a fairly regular basis; mostly when I get a chance to come up for air from working my day job.

Question #1:

Arthur is the IT Director for Contoso University. Arthur has submitted a requisition to the university for a licensed copy of Systems Center Configuration Manager 2007 which he expects to have approved and delivered within a month. In the interim, he directs his staff to implement the 120-day Evaluation Edition of Systems Center Configuration Manager 2007 in the production environment with the expectation of converting it to a licensed copy when his requisition is filled and he receives the license key. During this time, the IT department becomes heavily dependent on the software distribution and Remote Assistance features of Systems Center Configuration Manager 2007. The university is now 119 days into the 120 day evaluation period, and due to the bureaucracy of the university Arthur still does not have a licensed copy of SCCM. Arthur manages to acquire a pirated license key for Systems Center Configuration Manager 2007 from the internet and directs his staff to use the key to license the evaluation edition until they can get the requisition through for the university's copy of the software. Somehow, Microsoft gains knowledge of Contoso University's illegal copy of Systems Center Configuration Manager 2007 and reports the university to the Federal Bureau of Investigation. The FBI descends on the campus and arrests Arthur for piracy. While he is detained and awaiting trial, Arthur's goldfish dies in his office due to neglect. How many years will Arthur spend in prison for pirating Microsoft software?

A. Pirating Microsoft software hurts everyone, so it is a major felony. Arthur is looking at 15-20 years.

B. Pirating Microsoft software is a misdemeanor. Arthur will only spend 1 year in prison, but his wife will fall in love with the gardener and leave him during this time.

C. Arthur will be acquitted because he had submitted a requisition for the software and had made an attempt to obtain a licensed copy.

D. This is a white collar crime, so Arthur will only spend 2 months in a minimum security prison and then be under house arrest for a month.

E. Since the early 1700’s, piracy has always been punishable by hanging. Arthur will be shipped off to a country where cruel and unusual punishment is a legal and acceptable means of dispensing justice and he will be hung by the neck until his lifeless body is forever still.

Answer:
The answer is... well, it depends. It depends on the mood of the judge during Arthur's trial and how good of an attorney Arthur can afford. If any members of the presiding jury have been recent victims of software piracy, then it will not bode well for our hero either. What we can learn from this cautionary tale (besides the obvious - don't pirate software) is that the Evaluation Edition of Systems Center Configuration Manager 2007 can be upgraded to the full version once you have a valid key for the product. You would do this by re-running the SCCM 2007 setup program of the evaluation edition. Another important point is that you cannot upgrade SMS 2003 to the evaluation edition of SCCM 2007. If you try to do this, the option for upgrading the site will be grayed out. For more info: http://technet.microsoft.com/en-us/library/bb693584.aspx

**DISCLAIMER: In case some of you might have been fooled, these are not actual SCCM exam questions or braindumps. Like, you didn't really think that this was a real question, did you? These are just some questions I made up for your amusement and to help you learn some stuff about SCCM in the process. Also, Microsoft is (hopefully) not as Draconian as they are portrayed in this sample question. I mean, this is not the RIAA we are talking about here cracking down on someone's dead grandmother for illegal hip-hop MP3's...

Upcoming SCCM 2007 Webcasts for October 2007

bleary — Fri, 21 Sep 2007 17:16:00 GMT

Here a some upcoming SCCM 2007 webcasts from Microsoft during the month of October:

TechNet Webcast: Overview of System Center Configuration Manager 2007 (Level 300)

Monday, October 1, 2007

11:30 A.M.–12:30 P.M. Pacific Time

TechNet Webcast: Deploying Operating Systems with System Center Configuration Manager 2007 (Level 300)

Friday, October 5, 2007

11:30 A.M.–12:30 P.M. Pacific Time

TechNet Webcast: Desired Configuration Management in Systems Center Configuration Manager 2007 (Level 300)

Tuesday, October 16, 2007

1:00 P.M.–2:00 P.M. Pacific Time

Talk Like A Pirate Day

bleary — Wed, 19 Sep 2007 11:53:00 GMT

ARRRGHHHH, ye Mateys! It be Talk Like A Pirate Day! Here be a list of Pirate phrases so you know how to talk to your fellow scurvy curs:

http://talklikeapirateday.com/wiki/index.php?page=PiratePhrases

**UPDATED: Avast, the Talk like A Pirate Day website I linked to has been scuttled! Go on over and check out this other site instead: http://talklikeapirate.com/piratehome.html

Here's what's left of the other site:

Due to the extremeley high load, we had to take the site down. We have several paying customers and their interests had to come first. I'm really sorry, next year I will definitely have a bigger server.

The Official Talk Like a Pirate website might still be running (although their Pirate Translator is a bit crap).

Here's a search of Google for Talk Like a Pirate

Seriously, I had no idea this website was going to be so popular! Thanks again to everyone who visited.

Happy Talk Like a Pirate Day,
Old Salty Dog

SCCM 2007 Webcasts and Virtual Labs

bleary — Tue, 04 Sep 2007 20:01:00 GMT

The on-demand SCCM 2007 webcasts can be tricky to find on the Microsoft site as there isn't a search category for SCCM yet, so here's a handy list of what's available. I also included links to the SCCM Virtual Labs.

TechNet Webcast: Introduction to System Center Configuration Manager 2007 (Level 200)

TechNet Webcast: Deploying System Center Configuration Manager 2007 (Level 200)

TechNet Webcast: Deploying System Center Configuration Manager 2007 Clients (Level 300)

TechNet Webcast: New License Options for System Center Configuration Manager 2007 (Level 100)

TechNet Webcast: Microsoft System Center Configuration Manager 2007: Deployment (Level 300)

TechNet Webcast: Microsoft System Center Configuration Manager 2007: Managing Servers (Level 300)

TechNet Webcast: Managing Distributed Branch Office Environments with System Center and Windows Server 2008 (Level 300)

TechNet Virtual Lab: Introduction to System Center Configuration Manager (SCCM) 2007

TechNet Virtual Lab: Upgrading from Microsoft Systems Management Server 2003 to System Center Configuration Manager 2007

SoftGrid Sequencing Training Videos

bleary — Tue, 19 Jun 2007 16:54:00 GMT

I found some good online videos for learning how to sequence applications in SoftGrid. They were created by Tim Mangan, who used to work at Softricity and he created the original training program for sequencing apps.

http://www.tmurgent.com/VideoSeries.aspx?FolderOffset=\Videos\Sequencing%20With%20Tim%20Mangan\

The 20 Commandments of Software Packaging

bleary — Mon, 18 Jun 2007 15:41:00 GMT

One of my fellow consultants (thanks Erik!) pointed this white paper out to me the other day. A lot of the information is specific to AdminStudio, but even if you don't use that product it still has some good guidelines for packaging. You will need to fill out a registration form on the Macrovision website in order to download the white paper.

The 20 Commandments of Software Packaging

Eddie Van Halen Frankenstein Guitar

bleary — Wed, 13 Jun 2007 14:45:00 GMT

Guitar Center is taking pre-orders for replicas of Eddie Van Halen's Frankenstein guitar - yours for only $25,000! They detailed it down to every cigarette burn, scratch, and worn spot, and even screwed a quarter onto the guitar just like Eddie did. That's an awful lot of money for something that's all beat up; I'm thinking I could do close to the same thing with a Dremel and a blowtorch!

SoftGrid 4.2 Tech Preview Now Available!

bleary — Fri, 01 Jun 2007 20:18:00 GMT

Microsoft is pleased to announce the immediate availability of the Technical Preview for Microsoft SoftGrid Application Virtualization 4.2. This evolutionary release contains several updates, including Windows Vista compatibility, support for sequencing Side-by-Side (SxS) applications (such as Office 2007, Visual Studio Express and more), and bug fixes since the 4.1 release. This Technical Preview includes the Microsoft SoftGrid Client for Desktops and the System Center Sequencer. To test this preview, the administrator will need a SoftGrid 4.1 or later server on the test network (available from MVLS).As a reminder, the Tech Preview is a non-supported release and cannot be used in a production environment. Microsoft recommends updating to the full production version of 4.2 once it becomes available in July 2007 as part of the Microsoft Desktop Optimization Pack for SA 2007 release.

Please visit the Microsoft Connect site at http://connect.microsoft.com to gain access to the Technical Preview. Sign in with your Microsoft Windows Live ID and then click on "Available Connections" in the left pane. You will see "SoftGrid 4.2 Tech Preview" in the list. Click "Apply" and follow the directions and answer the survey questions to arrive at the SoftGrid welcome page. At this point, click on the "Downloads" link in the left pane and you will see the Tech Preview download.

Microsoft has also provided a feedback channel through the Microsoft Connect site. Click on the "Feedback" link in the left pane to access the feedback section and then click the green "submit feedback" button to send any issues you are seeing with the Technical Preview. This feedback helps Microsoft to better determine the future directions for the Microsoft SoftGrid platform. However, since this is not a supported release, Microsoft will not be able to respond to the submissions.

Source: The Softgrid Team Blog

Windows Server 2003 SP2 and the Non-existent SoftGrid Management Console

bleary — Tue, 29 May 2007 04:40:00 GMT

I ran into an issue tonight when I was trying to build a Softgrid demo. I loaded up a VM Workstation with Windows Server 2003 with SP2, but when I ran the Softgrid install, it did not install the Softgrid Management Console. After doing some searching on the web, I found an article on The Softgrid Team Blog that addressed the issue, and the comments were especially helpful. The problem stems from the Softgrid Management Console requiring MMC 3.0, which is already included in SP2 for Windows Server 2003 and the Softgrid installation does not properly detect that it's already there. This is supposed to be fixed in SP1 for Softgrid 4.1 which is due out Q2 of 2007. So until then, you either need to just manage your Softgrid server from a workstation, or you can edit the MSI with Orca to make it properly detect MMC 3.0, which I will walk you through in this article.

Orca comes with the Microsoft Windows SDK, which you can download from here if you don't have it already. Once you get it installed, make a copy of \Installers\Server\Softgrid Virtual Application Server.msi from the Softgrid source files because if we're going to do an edit on a file, we're going to do it on the copy, right?

Once the file is open, you will see the table in the MSI listed on the left. Click on the Condition table and the Release_SoftGrid_Management_Console condition will be displayed on the right-hand pane. Notice that the value for this condition is ((VersionNT = 501 And ServicePackLevel = 2) Or (VersionNT = 502 And ServicePackLevel = 1)); you need to click on it and add a greater than sign to the end like this: >= 1)). Here's a screen shot of what it should look like:

Now save the file and close Orca, and when you run the Softgrid install it will include the Softgrid Management Console as it will evaluate SP2 as greater than or equal to SP1.

Source: The Softgrid Team Blog

Forza Motorsport 2 - Released Tuesday 5/29!

bleary — Tue, 29 May 2007 04:30:00 GMT

Forza Motorsport 2 comes out Tuesday for the Xbox 360. Although I typically go for the arcade racers, I may end up picking this one up since it looks really cool. I'm not very good at the realistic racing games, and I usually rely on the guardrails to keep me on the track. This also crosses over into real life, where I like to bowl with the gutter guards in place.

http://www.microsoft.com/presspass/press/2007/may07/05-25ForzaMotorsport2PR.mspx

SMS 2003 SP3 Documentation

bleary — Thu, 24 May 2007 15:12:00 GMT

I noticed today that the documentation for SMS 2003 SP3 is finally up on the SMS product documentation site. They have also recently uploaded the SMS 2003 Asset Intelligence Help file as well.

http://www.microsoft.com/technet/sms/2003/library/proddocs.mspx

Integrate SMS 2003 with SoftGrid Step by Step Guide

bleary — Wed, 23 May 2007 16:32:00 GMT

Daniel Lai has published a step by step guide on his blog to setting up Softgrid to integrate with SMS. He explains how to install the SMS connector also known as the "Softricity Softgrid for Microsoft Systems Center" and configure it in SMS. The end result is that you can use the existing SMS infrastructure to push out Softgrid virtualized applications. Very cool...

http://msmvps.com/blogs/daniel/archive/2007/05/22/integrate-sms-2003-with-softgrid-step-by-step-guide-released.aspx

Best practices to use for sequencing in Microsoft SoftGrid

bleary — Tue, 22 May 2007 22:43:00 GMT

I recently passed the online Softgrid Exam and I ran across this Best Practices article when I was researching the study materials.

http://support.microsoft.com/kb/932137/en-us

Halo 3 Beta

bleary — Thu, 17 May 2007 16:24:00 GMT

I'm out traveling this week, but my son texted me and said that the Halo 3 beta came out last night. I won't be able to play it until this weekend, but it sounds really cool!

SP2 Schema Update Not Included in SP3

bleary — Tue, 08 May 2007 21:12:00 GMT

I haven't seen anything in the SMS 2003 SP3 documentation that states that you must have SP2 installed before you install SP3, so I decided to try it in a VM running SMS 2003 SP1 to see if it would upgrade. The install proceeded without any complaints or warnings, and everything seemed to be fine. However, when I checked the AD schema to make sure that it included the dNSHostName schema extension that was added in SP2, it wasn't there. Here's the screenshot from Schema Admin after upgrading SP3 from SP1:

…and this is what it should look like with the schema extension that was originally released with SP2:

Next, I checked the SMSSETUP.LOG file and it did not mention attempting to perform any schema updates. I decided to manually extend the schema by running extadsch.exe from the \smssetup\bin\i386 folder from the expanded SP3 files and it updated the schema like it was supposed to. I expected that this would have been something that was done automatically as it was in SP2, or at least some communication somewhere to tell you to do this if you’re not upgrading from SP2. Usually service pack releases have all the previous features and patches rolled into them but apparently this one got missed. So for best practices, either make sure that you have SP2 installed before you install SP3 or run extadsch.exe from the \smssetup\bin\i386 directory to manually extend the schema. I have already reported this issue to Microsoft. Special thanks to my co-workers John Stafford for helping me to investigate this issue and to Anthony Puca for leading me to try this out in the first place.

My New Job at EMC

bleary — Tue, 01 May 2007 02:35:00 GMT

I am starting my new job this week as a Solutions Architect for the EMC Microsoft Practice in Denver. With this new position I'll be traveling around a lot, but I'm hoping that I'll be able to contribute more to the myITforum community with all that extra time spent sitting around in hotel rooms. This is also going to be a great chance for me to broaden my knowledge base as I learn new stuff and get exposure to different enterprise environments. I'm really excited about this and I think its going to be a great opportunity for me!

The 10 Immutable Laws of Security Patch Management

bleary — Tue, 03 Apr 2007 13:46:00 GMT

I was going through some of my notes and I ran across this. Its a list that was presented a few years ago in a Microsoft webcast and I thought it might be good to post for those who hadn't seen it before.

The 10 Immutable Laws of Security Patch Management

Law # 1: Security patches are a fact of life

Law # 2: It does no good to patch a system that was never secure to begin with

Law # 3: There is no patch for bad judgment

Law # 4: You cannot patch what you do not know you have

Law # 5: The most effective patch is the one you do not have to apply

Law # 6: A service pack covers a multitude of patches

Law # 7: All patches are not created equal

Law # 8: Never base your patching decision on whether you have seen an exploit code … Unless you have seen an exploit code

Law # 9: Everyone has a patch management strategy, whether they know it or not

Law #10: Patch management is really Risk Management

Source: http://download.microsoft.com/download/0/1/a/01a053e8-3e18-4f73-b8e7-68d53a8232da/Russell_SBB_CA_PatchManagement.ppt

These Are a Few of My Favorite Things...

bleary — Mon, 02 Apr 2007 05:22:00 GMT

Here's a photo and list of the swag I got at MMS this year. Thanks to all the vendors for providing some really fun stuff!

Symantec Bean Bag Chair
Windows Vista Ultimate (Prize)
Altiris Head Rest
2 Kace Dartmen
MMS 2007 Shirt
TechSexy Shirt
2 Intel VPro Shirts
Powershell Shirt
MMS Alumni Shirt
Full Armor Shirt
Full Armor Rubber Knight
System Center Slinky
System Center Water Bottle
4 System Center Beach Balls
2 System Center Yo-yos
2 System Center Lighted Superballs
2 NetIQ Yo-yos
2 F5 Balls
2 Secure Vantage Balls
3 Tins of 1E Mints
Intellinet Coozi
EMC Ethernet Cable
Microsoft Forefront Badge Clip
1E Card Light
Configuresoft Lip Balm
Computer Associates Tote Bag
BMC Software Tote Bag
4 Citrix Pens
Tidalsoft Pen
F5 Pen
MyITforum Party Button
4 Novell Light Cubes
4 Intel Light Cubes

The lighted ice cubes are really cool when you put a bunch of them in the bathtub! Click here for photo.

Softgrid Sessions at MMS

bleary — Mon, 26 Mar 2007 17:54:00 GMT

My co-worker Sean McHugh and I are back for our second year at MMS, and already we can already tell this is going to be an awesome week! We're really looking forward to some of the sessions and labs we have scheduled, especially the Softgrid stuff. If you're still mulling about which sessions to take, I really encourage you to check out some of the Softgrid sessions. Once you wrap your brain around what it does along with some of the other functions of MDOP, its easy to get excited about how this will revolutionize application deployment! We are looking at deploying this at our company next month and it will cut our SMS package development time down from days (or sometimes weeks) to just several minutes. It also does other cool tricks such as updating and tracking your apps.

If you're not able to get into any of the labs, at least check out the Softgrid virtual lab that Anthony Clendenen posted about earlier in his blog here.

Download: Creating SMS Web Reports from SMS Queries

bleary — Tue, 13 Mar 2007 04:45:00 GMT

I had a couple different people e-mail me and say that they couldn't see the images for my "Creating SMS Web Reports from SMS Queries" blog posts. I thought that it might just be due to the page taking too long to load as I could see the images just fine, but it turns out they were right - the images weren't published properly. I fixed the problem, but if anyone still has trouble viewing the posts and wants to download an offline copy, I have it attached to this posting.

Creating SMS Web Reports from SMS Queries - Part I

bleary — Tue, 06 Mar 2007 04:36:00 GMT

From SMS Query to SQL Query

In the past, there have been some articles written on myITforum.com and also some forum posts describing how to use the smsprov.log file to convert SMS queries to SQL queries. This is a fairly simple procedure, but if you try to take it a step further to create a web report using this method, the query causes a 'SELECT permission denied' error when the report is run. In this series of articles, I am going to explain how to use SQL views from the SMS database in a SQL query to avoid this error, speed up the query process as well, and teach you some basic Transact-SQL.

Converting from an SMS Query to a SQL Query
The first thing we will look at is how to take an SMS query and convert it to a SQL query which will later become our web report. We'll start with a fairly simple SMS query to return the SMS Site Code, System Name, Last User Logon Name, and Add/Remove Program Display Name for any systems that have Internet Explorer 7 installed. Here's the WQL language so you can just paste it into an SMS query:

select SMS_R_System.SMSAssignedSites, SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Windows Internet Explorer 7"

Run the query in SMS and view the results. Now open up the SMS SQL provider log file named smsprov.log which resides in the \SMS\Logs directory on the site server and scroll to the bottom of the log. You will see a line that starts with "Execute WQL=" followed by the SMS query that you pasted in. Immediately below that, there is a line that begins with "Execute SQL=" followed by the SQL query that the SMS SQL Provider converted from WQL. Highlight the SQL query from "select all" to the end and copy it. The SQL query looks like this:

select all SMS_R_System.ItemKey,SMS_R_System.Name0,SMS_R_System.User_Name0,__System_ADD_REMOVE_PROGRAMS0.DisplayName00 from System_DISC AS SMS_R_System INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey where __System_ADD_REMOVE_PROGRAMS0.DisplayName00 = "Windows Internet Explorer 7"

From a SQL Query to an SMS Web Report that doesn’t work
Now we’re going to create an SMS Web Report from this SQL query so you can see what the ‘SELECT permission denied’ error looks like. In the SMS Administrator’s Console, expand the Reporting node, and then right-click on Reports. From the pop-up menu, select New -> Report and title it, “All Systems with Microsoft Internet Explorer 7” and then click on the Edit SQL Statement button. Delete the default query text in the SQL Statement window, paste in our SQL query, and then click on OK. The report properties should look like this:

Click on OK to save the report. To run the report, right-click on it in the SMS Administrator Console, and then from the pop-up menu select All Tasks -> Run -> YourSMSReportingServerName. When you run the report, you should see an error message like this:

So why is it that we can run this query in SMS which passes it to SQL flawlessly, yet when we take the same SQL query and dump it into a web report we get this error? The answer is that the SQL query references SQL tables, and the SMS SQL provider lets you access those tables through the SMS Administrator’s console. When you try to run the web report, the SQL provider is out of the picture, and in the context of permissions that you are running the web report under you don’t have permissions to the SQL tables – and that’s a good thing! You wouldn’t want your users who have access to your web reports to have direct rights to the data in your SQL tables.

The solution to this is to use SQL views in your web report, which your users will have access to and it will also speed up your querying process. In part 2 of this article, we will go though how to convert your SQL query to use SMS views.

Creating SMS Web Reports from SMS Queries - Part II

bleary — Tue, 06 Mar 2007 04:35:00 GMT

Using Query Analyzer

In part one of this article series, we saw how to convert an SMS query to a SQL query, but when we tried to take the same SQL statement and make it a web report it returned a ‘permissions denied’ error. Part two will provide an overview on how to use Query Analyzer, what SQL tables are, and how to run a SQL query.

**DISCLAIMER: The exercises in this article discuss how to read data from a SQL database using SELECT statements. As long as you stay within the confines of what is being presented, you do not run any risk of damaging or altering your SQL data. Unless you are aware of what you are doing or are instructed by someone who knows SQL, you should never alter the data in your SMS database either directly or through INSERT or UPDATE statements. That being said, please don’t blame me if you crush your database by deviating from the exercises and venturing off on your own!

Editing the SQL Query using Query Analyzer
Go to the Microsoft SQL Server program group and open the SQL 2000 Query Analyzer which allows you to run SQL queries against your SMS database. I'm using the older version of SQL for this article since not everyone is using SQL 2005 so the SQL 2000 Query Analyzer is a better common ground. (If you’re using SQL 2005 then you would use the Query Editor instead of Query Analyzer.) Now type in your SQL server name where the SMS database resides and select either Windows or SQL Server authentication depending on which option you chose when you installed SMS. If you use SQL Server authentication you will need to provide a username and password for a SQL account that has access to the SMS database. After clicking on OK, you need to select your SMS database from the drop down menu on the toolbar at the top of the screen. If you don’t do this then you will get an error stating, “Invalid object name” when the query tries to run. Now cut and paste the SQL query that we pulled from the smsprov.log file in Part 1 of this article series into the Query Analyzer window. For convenience, here’s the SQL query:

Your window should now look something like the screen shot below, but please note that I have removed my server name from the Query Analyzer window due to company policy. If you’re not seeing the object browser pane on the left side, then hit the F8 key to display it. For readability, I’ve added some line breaks into the query, which Query Analyzer will ignore when the query runs.

What are SQL Tables?

Before we proceed in modifying this query, we need to understand what it is doing. One of the first things you will need to know if you don’t already is that SQL stores its data in tables. Let’s use the Object Browser window on the left side to look at some tables in the SMS database. Expand your SMS database name, and then expand User Tables. If you scroll down, you will see a list of all the tables in your SMS database.

Now we’re going to choose a table and display the data that resides in it. Scroll down to the table named, “dbo.CD_ROM_DATA” and expand it, then expand Columns. This shows all the different CD ROM information that SMS collects on a client system during a Hardware Inventory cycle as defined in your sms_def.mof file.

Next, we’ll look at the data that resides in this table. Right click on dbo.CD_ROM_DATA. From the pop-up menu that appears, choose “Script object to new window as” then “Select” which will open a new window in Query Analyzer with a SQL query to return all the rows in the table. To run the query, you can either choose Execute from the Query menu in Query Analyzer or click on the green triangle in the toolbar. When the query runs, it will display all the CD ROM data that SMS has collected from your clients in the results pane on the bottom. If you scroll over to the right, you can see all the different columns.

When you’re finished viewing the results, close the child window in SQL Analyzer by clicking on the gray X in the upper right hand corner to go back to our original SQL query for Internet Explorer 7. We will continue with this in Part 3 of this article, where we will look at the SQL query and decipher what each line in the query is doing.

Creating SMS Web Reports from SMS Queries - Part III

bleary — Tue, 06 Mar 2007 04:34:00 GMT

Deciphering the SQL Query

Part 2 of this series showed how to use the SQL 2000 Query Analyzer, what SQL tables are, and showed how to run a SQL query. In Part 3 we will look at each line of the SQL query and explain what it is doing. From where we left off in the previous article, you should be back to having Query Analyzer with the IE 7 SQL query pasted in the query window.

Some Transact SQL Basics

So now let’s take a moment to examine the SQL query and understand what it is doing. You will notice that SQL has very similar syntax to the WQL queries in SMS that are automatically generated when you create an SMS query. Each query contains keywords that Query Analyzer recognizes and highlights in blue. There are numerous reserved keywords in SQL, but for the purpose of this article we are only examining a few as they are the most common ones that you will run into. The ones we will be learning are SELECT, FROM, AS, INNER JOIN, ON, and WHERE.

In order to explain these keywords, we will start with a basic overview of the entire SQL statement and then go back and explain each keyword in detail. This may seem confusing at first, but hang in there and it will make more sense in a minute. Let’s start with the entire SQL statement again; for readability, I will be highlighting the SQL keywords in blue:

SELECT ALL SMS_R_System.ItemKey, SMS_R_System.Name0, SMS_R_System.User_Name0, __System_ADD_REMOVE_PROGRAMS0.DisplayName00

FROM System_DISC AS SMS_R_System

INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey

WHERE __System_ADD_REMOVE_PROGRAMS0.DisplayName00 = "Windows Internet Explorer 7"

Now we’ll go into each line in further detail. SELECT is the first SQL keyword we will look at as it is the first one listed in the query, along with its secondary keyword ALL. Here’s the first line of the SQL query:

SELECT ALL SMS_R_System.ItemKey, SMS_R_System.Name0, SMS_R_System.User_Name0, __System_ADD_REMOVE_PROGRAMS0.DisplayName00

The SELECT ALL keywords are followed by references in the form of Table Name or Alias.Column Name, each of which is separated by a comma. The ALL keyword is self-explanatory, but I want to point out that you can also use SELECT * to return the same results.

In our query, SMS_R_System is an alias for the table name System_DISC which is defined in the next SQL statement using the FROM and AS keywords in the next line. Additionally, we are displaying the DisplayName00 column from a table aliased as __System_ADD_REMOVE_PROGRAMS0. This alias is defined in the INNER JOIN statement which we will explain shortly. For now, we’ll look at the FROM statement next and then tie these two lines together. The FROM line is as follows:

FROM System_DISC AS SMS_R_System

This one’s easy; we’re selecting our columns from a table named System_DISC but from now on we are referring to it using the alias SMS_R_System. Let’s take a look at what we have so far in terms of the aliases, the red arrows show where each alias is used in the SELECT statement:

Sorry about the mess; this obviously isn’t an art class, but hopefully you get the idea. I really need to stop using Microsoft Crayon and learn how to use Microsoft Paint instead!

Next we will look at INNER JOIN. There are several different types of joins in SQL, but for simplicity we will keep it limited to just this one. Basically, what an INNER JOIN does is to join two or more tables or views and display data from both of them. The ON keyword specifies which two columns that the join is linked on. Let’s examine what our INNER JOIN statement is doing in our query:

INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey

First we are taking the table named Add_Remove_Programs_DATA and aliasing it as __System_ADD_REMOVE_PROGRAMS0. The ON keyword is then linking the MachineID column from the table aliased as __System_ADD_REMOVE_PROGRAMS0 to the ItemKey column in the table aliased as SMS_R_System.

The last line with the WHERE keyword simply states that the only rows in the database that should be returned are the ones in the __System_ADD_REMOVE_PROGRAMS0 table where the data in the DisplayName00 column is equal to Windows Internet Explorer 7. This line looks like this:

WHERE __System_ADD_REMOVE_PROGRAMS0.DisplayName00 = “Windows Internet Explorer 7”

In summary, what our SQL query is saying is to display the columns named ItemKey, Name0, and UserName0 from a table called System_DISC which is aliased as SMS_R_System. We are also displaying another column named DisplayName00 from a table aliased as __System_ADD_REMOVE_PROGRAMS0, and this table is joined to the other table. This query will only return the rows where the __System_ADD_REMOVE_PROGRAMS0 table has data in the DisplayName00 column that is equal to Windows Internet Explorer 7.

So hopefully you now have at least a basic knowledge of what the SQL statement is doing, and you’re ready to start learning about SQL views which we will cover in Part 4. But before I close this article out, I have one more helpful tip: If you ever run across another keyword or want to look up more information on one of the ones that we’ve covered, Query Analyzer has context-sensitive help so you can highlight a particular keyword in a query then hit SHIFT+F1 to display help on that particular keyword. SQL Books Online has to be installed in order for this to work properly, which can be done by re-running the setup utility.

Creating SMS Web Reports from SMS Queries - Part IV

bleary — Tue, 06 Mar 2007 04:33:00 GMT

SQL Views

In Part 3, we dismantled our SQL query and explained what it is doing and the logic behind it. In this article, we will be covering SQL views.

What are SQL Views?

SQL Views are predefined SQL queries that are run against one or more tables to present data to the user without actually giving them direct access to the SQL database. Views are kind of like a sneeze guard at a buffet line. Just as that quarter-inch piece of plastic protects the food from people’s forcefully expelled germs, SQL views prevent your users from contaminating your SMS database.

To use a view in a SQL query, you would treat it just as if it was a SQL table name. SMS automatically creates SQL views for the tables that are in the SMS database, so you should always be able to find the view you need to access any of the tables. Let’s open up Query Analyzer and look at a SQL view by expanding Views in the Object Browser. Scroll down to dbo.v_R_System, expand it, and then expand Columns. Your Object Browser should now look like this:

You may have noticed that after dbo., most views start with a letter ‘v’ followed by an underscore, which makes it easier to denote them as views. Now we will examine the SQL statement that defines the view. Right-click on dbo.v_R_System and select Edit from the menu. The SQL query for the view will open in a new Query Analyzer window, and that query looks like this:

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS OFF

ALTER VIEW dbo.v_R_System AS SELECT ItemKey As 'ResourceID', DiscArchKey As 'ResourceType',Active0,AD_Site_Name0,Client0,Client_Type0,Client_Version0,CPUType0,Creation_Date0,Decommissioned0,Hardware_ID0,User_Domain0,User_Name0,Name0,Netbios_Name0,Obsolete0,Operating_System_Name_and0,Previous_SMS_UUID0,Resource_Domain_OR_Workgr0,SMS_Unique_Identifier0,SMS_UUID_Change_Date0,Community_Name0 FROM System_DISC

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

There are a lot of new SQL keywords that you hadn’t seen before, but for the purpose of this article you don’t need to be concerned with what they do. For now, just look at the ALTER VIEW statement, where you can see that it is querying FROM System_DISC. We will look at that table next and compare its columns to the ones in the v_R_System view. Just to keep things easier to read, I will be leaving the dbo. off of the beginning of the view names throughout the rest of these articles. Now, close the child window and select to not save any changes.

Next, scroll up to User Tables, and then expand SystemDisc and Columns. You will see that most of the same columns exist in both the table and its related view. Here’s a look at both of them side by side so you can see the similarities. Query Analyzer doesn’t list them in alphabetical order, but you can see how the v_R_System view is comprised of most of the columns in the System_DISC table.

In most cases, you will be able to figure out which view in your SMS database corresponds to which table just by browsing through the list of names as they are named similarly. Here are a few examples:

Table Name	View Name
System_DISC	v_R_System
ADD_REMOVE_PROGRAMS_DATA	v_GS_ADD_REMOVE_PROGRAMS
CD_ROM_DATA	v_GS_CDROM
OPERATING_SYSTEM_DATA	v_GS_OPERATING_SYSTEM
System_IP_Subnets_ARR	v_RA_System_IPSubnets
SoftwareFile	v_SoftwareFile

As you can see, it’s pretty straightforward. The table names usually end with _DATA and the aliases usually start with v_GS_, which is information that is gathered during a Hardware Inventory cycle. One thing that you do need to know is that the views that start with v_HS_ are referencing tables containing SMS history data. So unless you are writing an SMS Web Report to use historical information you will generally want to avoid using the v_HS views.

At this point, you should have enough of an idea of what the SQL statement is doing and how views are used, so we can now move on to Part 5 where we will start converting our SQL query to use SQL views.

Creating SMS Web Reports from SMS Queries - Part V

bleary — Tue, 06 Mar 2007 04:32:00 GMT

Modifying the report to use views

In Part 4, we defined what SQL views are and compared them with the tables. Now that you have all this SQL knowledge in that big brain of yours, we can finally start converting our SQL query to use SQL views instead of tables.

Switching to Views

We will start with our original SQL query which you should still have pasted in Query Analyzer. Make sure that your SMS database is selected in the database drop-down field on the toolbar before we proceed so that Query Analyzer knows that the queries you are running are against your SMS database. Here’s the original query again:

Replace double quotes with single quotes

The first thing we’re going to do is to run the query and see what it returns. Go to the Query menu and select Execute, or hit the F5 key. The results pane will appear with the following error:

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name 'Windows Internet Explorer 7'.

The problem is that SQL is expecting single quotes around the text string, ‘Windows Internet Explorer 7’ instead of double quotes. In order to use double quotes, you could use the SET QUOTED_IDENTIFIER keyword, but instead of messing around with that we’ll go ahead and give SQL what its expecting, which are single quotes.

This isn’t rocket science; it’s the same thing you’ve done in Microsoft Word or Excel or a bajillion other apps. Go to the Edit menu and select Replace. Enter a double quote in the Find field and a single quote in the Replace with field, and then click on the Replace All button. If you don’t feel like doing a Find/Replace, you can always just directly make the edit to the query.

You’ll notice that as soon as you hit that button, ‘Windows Internet Explorer 7’ will turn red in the query window. That means that Query Analyzer now recognizes that as a text string because of the single quotes. Your Query Analyzer window should now look like this:

Now that we made a change, let’s see what happens when you run the query:

OK, so we’re getting some results now, but if you tried to put this query into a web report you would still get an ‘Access denied’ error as we still need to convert it to use SQL views. This is because of the permissions context that we are running under in Query Analyzer, which has more rights than when you run a web report. Notice also that the first column is displaying ItemKey instead of the SMS Site code so we will eventually need to fix that as well, but first we will take care of the views.

Replacing the table name System_DISC

Let’s start swapping table names for views by looking at the FROM…AS line in the query, which currently looks like this:

FROM System_DISC AS SMS_R_System

We discussed this line back in Part 3 of this article series, where it was explained that this line is selecting our columns from a table named System_DISC and aliasing it as SMS_R_System. What we need to do at this point is to replace System_DISC with its corresponding view name, v_R_System. After performing the replace, the line should look like this:

FROM v_R_System AS SMS_R_System

So now we are telling Query Analyzer to select columns from the view named v_R_System and alias it as SMS_R_System.

Replace SMS_R_System with v_R_System

The next thing we’re going to do is to replace the alias name SMS_R_System. As before, so again: Edit -> Replace and populate the fields as shown below, then hit Replace All to replace the multiple instances of SMS_R_System in the query:

Just to make sure we’re on the same page, Query Analyzer now looks like this:

Since we’ve made this change, take a look at the FROM…AS line:

FROM v_R_System AS v_R_System

Who’s writing this query? Someone from the Department Of Redundancy Department? What it is now saying is to select columns from the view named v_R_System and alias it as v_R_System. The whole purpose of using an alias is to give a long table or view name a shorter or friendlier way to reference the name. Since we are now just aliasing the view for what its name is anyway, we are going to remove it. Using an alias is optional, so this won’t affect the functionality of the query. Delete “AS v_R_System” from the FROM line so that it now reads as follows:

FROM v_R_System

We are now ready to move on to the next table/view name swaps that we need to do for Add_Remove_Programs.

Adding the Views for Add_Remove_Programs

This is a lot like what we just did for v_R_System. In the INNER JOIN line, replace the table name Add_Remove_Programs_DATA with its corresponding view name v_GS_ADD_REMOVE_PROGRAMS. Next, replace the alias name __System_ADD_REMOVE_PROGRAMS0 (note that that is a double underscore before System) with the view name v_GS_ADD_REMOVE_PROGRAMS, and you‘ll end up with this:

Once again, we have a self-referencing alias in the INNER JOIN line:

INNER JOIN v_GS_ADD_REMOVE_PROGRAMS AS v_GS_ADD_REMOVE_PROGRAMS

Delete the alias, and then it becomes this:

INNER JOIN v_GS_ADD_REMOVE_PROGRAMS

Hopefully at this point you’re not completely lost, but if you are then here is what the query text should look like:

select all v_R_System.ItemKey, v_R_System.Name0, v_R_System.User_Name0,

v_GS_ADD_REMOVE_PROGRAMS.DisplayName00

from v_R_System

INNER JOIN v_GS_ADD_REMOVE_PROGRAMS

ON v_GS_ADD_REMOVE_PROGRAMS.MachineID = v_R_System.ItemKey

where v_GS_ADD_REMOVE_PROGRAMS.DisplayName00 = 'Windows Internet Explorer 7'

Let’s see how we’re faring so far; go ahead and run the query and see what it does.

Did you get a bunch of column name errors like I got? Excellent! Everything is working according to my plan! In Part 6 of this series, we will examine what caused these errors and how to clean them up.

Creating SMS Web Reports from SMS Queries - Part VI

bleary — Tue, 06 Mar 2007 04:31:00 GMT

Selecting the Proper Column Names From the Views

In Part 5, we replaced the table names with view names, and tidied up the query by removing the aliases. When we ran the query, there were several errors regarding invalid column names:

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name 'MachineID'.

Server: Msg 207, Level 16, State 1, Line 1

Invalid column name 'ItemKey'.

Server: Msg 207, Level 16, State 1, Line 1

Invalid column name 'ItemKey'.

Server: Msg 207, Level 16, State 1, Line 1

Invalid column name 'DisplayName00'.

Server: Msg 207, Level 16, State 1, Line 1

Invalid column name 'DisplayName00'.

The reason that we are getting these errors is because the columns names in the views are different than those in the tables. Let’s take a look at the columns within the views and you’ll get a better idea.

Fixing the Error for MachineID

The first error in our list was for MachineID from the INNER JOIN line so we’ll check that one out first. In Query Analyzer, expand your SMS database name in the Object Browser on the left side, and then expand Views. Next, expand v_GS_ADD_REMOVE_PROGRAMS, and then expand its columns node. Notice that MachineID is not one of the listed columns, so when the query tried to reference v_GS_ADD_REMOVE_PROGRAMS.MachineID it was unable to find the MachineID column in that view.

So here’s how to fix it. Right-click on v_GS_ADD_REMOVE_PROGRAMS and select Edit to display the query that created the view. The following query will appear in the query pane:

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS OFF

ALTER VIEW dbo.v_GS_ADD_REMOVE_PROGRAMS AS SELECT MachineID As 'ResourceID', InstanceKey As 'GroupID', RevisionID, AgentID, TimeKey As 'TimeStamp',DisplayName00 As 'DisplayName0',InstallDate00 As 'InstallDate0',ProdID00 As 'ProdID0',Publisher00 As 'Publisher0',Version00 As 'Version0' FROM Add_Remove_Programs_DATA

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

The part I want to draw your attention to is the part I highlighted in the query, SELECT MachineID As 'ResourceID'. As you may recall from Part 4 of this article series, views are created by querying tables with a SQL statement and selecting the table columns that are used in the view. What’s happening here is that the view’s SQL query is selecting the MachineID column from the Add_Remove_Programs_DATA table and aliasing it as ResourceID in the view. If you look back over at the columns listed for the view, you will see ResourceID as one of the column names. So in order to get our query to work, we will have to replace MachineID with ResourceID since that is the column that is in the view. Close the child window by clicking on the gray X in the top right-hand corner of Query Analyzer, choose not to save the query, and you will be back at the original query. Look at the INNER JOIN line and find the column name, MachineID. I have it highlighted below:

INNER JOIN v_GS_ADD_REMOVE_PROGRAMS

ON v_GS_ADD_REMOVE_PROGRAMS.MachineID = v_R_System.ItemKey

Now replace MachineID with ResourceID and run the query again. The error for MachineID is now gone, and we just have the four other errors to deal with:

Fixing the Error for ItemKey

The next error we need to correct is ItemKey, which is used in both the SELECT and INNER JOIN lines. If you edit the v_R_System view you will see this line in its query:

ALTER VIEW dbo.v_R_System AS SELECT ItemKey As 'ResourceID', …

How convenient; ItemKey is also aliased as ResourceID. This is because both ItemKey and MachineID contain the same data, which is a unique serial number that is given to each SMS client. In some tables it is put in a column named ItemKey and in others it is in a column named ResourceID. The really cool thing is that if you look at the columns for most of the views, a lot of them have ResourceID as a column. This was done intentionally by the SMS Developers so that you could use ResourceID to link different views with a join.

So logically, we need to replace ItemKey with ResourceID in our query to reference the correct column name. Once we do that and run the query, we’ll be down to the last two errors that we need to fix:

Fixing the Error for DisplayName00

This last error that we need to resolve is referenced twice in both the SELECT and WHERE lines, and it is actually very simple to fix. Expand the columns for the view v_GS_ADD_REMOVE_PROGRAMS and the change that we need to make is fairly clear:

So instead of DisplayName00 we need DisplayName0. Once the two changes have been done, our SQL query should now look like this:

select all v_R_System.ResourceID, v_R_System.Name0, v_R_System.User_Name0,

v_GS_ADD_REMOVE_PROGRAMS.DisplayName0

from v_R_System

INNER JOIN v_GS_ADD_REMOVE_PROGRAMS

ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID

where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Windows Internet Explorer 7'

When we run the query, it is finally presenting us with some results:

You’re probably thinking that we’re home free and we can go ahead and copy our SQL query into a web report, but like so many other articles in this series, this one too ends in tragedy. Look at the first column. We’re getting a list of Resource IDs, but that not what we wanted when we started this whole process. Our original goal was to write an SMS web report to return the SMS Site Code, System Name, Last User Logon Name, and Add/Remove Program Display Name for any systems that have Internet Explorer 7 installed. To accomplish this, we’re going to need to get rid of ResourceID in the SELECT line and have it return the SMS Site Code instead, which is what we will be discussing in Part 7 of this article series.