Do you find the Support WebCast transcripts helpful?
Let us know!

Note This document is based on the original spoken Support WebCast transcript. It has been edited for clarity.

Vic Reavis: Hello, everyone. My name is Vic Reavis. I work in the Charlotte office supporting MicrosoftÒ SMS and I want to go ahead and cover some of the most requested queries that we've had in product support over the last two years since the SMS tool was released.

Let's go ahead and move on to our second slide. I want to briefly cover our session agenda. We're going to be covering primarily three queries. The first query, as we see in the session agenda, is machines that have a file installed. I think this is a query that probably everyone is familiar with. It's straightforward enough so I'm going to spend just a little bit of time on it because we do need to have that foundation for the second topic that we're going to cover, machines that don't have a file installed. This, because of the way the query is structured and the way that it is set up, is a little difficult conceptually to understand. I know I had a lot of difficulty with learning how to construct this query when the product was initially released, so it should not make anyone feel bad because it is a difficult query to build. The last queries that we're going to cover are the OR queries and queries that contain wildcards. So let's go ahead and move on to our third slide and let's begin the presentation.

In this slide we're covering predominantly the screen you see first when you go into the SMS Administrator console, you scroll down under Site Database for your site, and you choose the Queries node. You right-click on the Queries node and choose New to create a new query. This is the first screen that appears, so go ahead and give the query a name. Here we have named the query SoftwareYES because we want to find all machines that, yes, have a particular file installed. We're leaving the object type of System Resource alone. There are several options here that you can choose, but for this one we're choosing System Resource.

Comment. You can put in any type of comment describing the query you create. I would recommend this, especially if there are going to be others using this particular query. This helps everyone else to understand what exactly you're looking for in this query, and also, someone else may find a particular problem with the query if it is not returning results as expected based on the description in the comment.

In the Collection Limiting area down at the bottom half of this screen, you can choose a collection to limit this to. By default, Not collection limited is selected. You can choose all systems or some other collection that you've created, or the third radio button, which prompts you for a collection.

Before moving on, I want to let everyone know that to create this statement, we're going to click the Edit Query Statement button. This is where we're going to do our work. This is where we'll specify our criteria and specify any joins that we need, anything like that. So let's move on to the next slide and go over that.

On this fourth slide titled "Machines That Have a File Installed (2)," we see three tabs across the top: the General tab, the Criteria tab, and the Joins tab. In the General tab, we see in the first check box at the top, Omit duplicate rows (select distinct). Essentially, it is going to call out any duplicates, combine any rows, and consolidate the information returned from the query. In this particular instance, it's okay to do this. When we get to the SoftwareNOT piece, this will end up causing us problems. So if the intent is to build a query that looks for all systems that do not have a file installed, then when we build our SoftwareYES query, do not check this check box. It will cause a syntax error.

Down at the bottom where we see the Class, Attribute, and Sort selections, is where we'll specify the information that we want returned. Now, we need to get information in here. Just above, we see the word Results and then five buttons; the star, the first button on the left, is for creating a new query. So we'll go ahead and hit that. That will allow us to specify what we want. Here we've specified the class, System Resource. In that class, we've chosen the attribute of Netbios Name because we want to return the machine name since we're going to look for all machines that have a particular file. We don't want to sort this so we've left it unsorted.

The second button; if we want to edit the properties of an already existing query then we would hit this button. This will allow us to change, for example, the attribute. If we don't want to search for the Netbios name, we can change that. Or if we want to change the class entirely to a software file, we can change it using the edit button, the second button over to the right.

The middle button, the black X; I think we all know this is the delete button. If we highlight one of the results that are going to be displayed and we hit this X, it's going to remove it. So I think this is self-explanatory.

The two buttons on the right are for changing our display order. Now, this is actually the display order of what we have selected in the results. In this case, we only have one selected and that's System Resource - Netbios Name. If we had multiple results, say, for example, we wanted to do the software file name, then we could move it to display either before System Resource or after. So the fourth button from the left allows you to move it up, and the button on the far right allows you to move it down.

At this point, let's go ahead and move to the fifth slide and let's begin to cover the actual details of what builds this query for us and gives us the information that we need.

We're going to be populating the Select Attribute dialog box shortly. That will include all of our specific information for the criteria that we're looking for. As we'll see in the next couple of slides, it will become self-explanatory as far as what we include. The Select button allows us to populate that with the classes and the attributes for the information that we're looking for. Again, we're not looking to sort this. This particular screen and the following one is what we would use to populate what we saw in the previous slide in the Results section. So let's move forward to the sixth slide.

When we hit the Select button that we saw in the previous slide, this is what we're shown. We're shown an Attribute class drop-down list box, we're shown an Alias as drop-down list box, and we're shown the Attribute drop-down list box. The Attribute Class drop-down list box is going to pretty much correlate to what you would see if you opened up the SMS_desktop MOF and took a look at the classes there. We've got essentially the same classes here.

In this case, we're looking for a system resource because we want to pull the machine names again. We don't want to create an alias, but we could if we wanted to make this query more readable to, say, help desk personnel. For example, if this query is used predominately for help desk personnel who do nothing but create packages to send out that particular file that is missing from the machines that we're going to look for, this would be how we would make it easily read for them; in the Alias as list, just type the name that you would prefer that they see as the caption.

The third and final attribute is the actual, specific instance within that class, in this case, the system resource class. Depending on the class, there could be as many as 70 or more. For example, the modem class has a large number or very few attributes that could be selected for a class. So this will change depending on what you have chosen in the attribute class. So let's move on to the seventh slide now.

What we're going to show here in the criteria is what we're actually looking for. In this case, we see the criterion that we're looking for is Software Files, File Name is equal to "MYNOTEPAD.EXE." We're going to use this very first button beside Criteria to create a brand new criterion for this query. If we already had criteria entered in here, or we wanted to modify an existing query, then we would choose the second button (again, that's the modify button that we saw in the previous screens). I think we all know what the third button is. The big black X we know is delete.

The fourth button that we see there is going to group our criteria. If we had multiple criteria selected here, we would highlight two of them and we would group them together by clicking on this button. If you have the Admin console beside you and you're following along trying to create these queries with us, as practice you may want to go ahead and add another criterion of any type just to group them. You'd highlight both at the same time, click on the fourth button (the parentheses with the star), and that would group them. When you don't want to group them any longer, you click on that same grouping, click the fifth button over (it has the parentheses with the X over it), and that's going to drop the grouping.

Now, the last two buttons are going to be our operators. The button with the ampersand and the pipe (& |) to the right side of it changes an AND to an OR. If we have multiple criteria and we want to find all machines with, for example, MYNOTEPAD.EXE and MYAPPLICATION.EXE, then the AND is the logical operator that we're going to be using. So in this case, we'd highlight both of them and click on the ampersand with the pipe symbol. That would make sure that there's an AND between them. By default, AND is used.

If you have two criteria selected and AND is the logical operator and you want to find all machines that have MYNOTEPAD.EXE or MYAPPLICATION.EXE, then you would click on that button again and it will change the operator from an AND to an OR. This will allow you to find machines that have either the first file name selected or the second file name specified.

The last button is a logical operator that's going to add a NOT. So in this case, if we highlighted this criterion, Software File, File Name is equal to "MYNOTEPAD.EXE," and then we clicked on that exclamation point (that's our logical NOT operator), that's going to make it Software File, File Name NOT equal to "MYNOTEPAD.EXE."

Now, we'll see later in the presentation where using the logical NOT operator is not necessarily the right way to go. It's not going to give us all machines that don't have this file and I'll explain a little bit later why that is. What I'd like to do now is explain the last button on the bottom before we move to the next slide.

The Show Query Language button at the bottom. If we click that once, we've created our criteria that's going to display the WBEM\WMI query language as it would be sent to WBEM before being translated to SQL and sent to SQL.

Once we've clicked Show Query Language and brought up the WBEM query language information, there will be another button that will allow us to come back to the design view, which we see here. So let's move on to the eighth slide.

This slide is where we're specifying the criteria. In the very first one, the Criterion type, we want to do just a Simple value. There are a couple of options that we could choose from, but for this instance, Simple value is pertinent. I would recommend that, when you have time and want to investigate a little bit more into how queries work, spend some time hitting each of these drop-down boxes and see what the myriad of options are. The Where field is where we're going to specify Software Files - File Name.

Now, that's going to be specified when we click the Select button. The screen that appears when we click the Select button is very much like the screen we saw a couple of slides ago that showed the attribute class and the attribute name. That was an alias, so that's why it's not displayed here. We've seen that and I think it's quite familiar to everyone. The operator is is equal to. It's what we're using because we want to find all of the machines that have this particular file.

Now, the Value field is an interesting one. We can either type it in if we know the exact file name that we're looking for, or if we're not sure exactly what it is, we can click the Values button. There's a little bit of danger with the Values button, depending on how much we inventory for. If we've inventoried for more than just, let's say, .exe files, then it may take this particular query a lot of time to return. If we've added DLLs, for example, we all know that machines typically have hundreds if not thousands of DLL files. Clicking Values then is probably going to take several minutes for it to return because it is going to pull all of those files. So beware of that. If you know the file name, that would be preferable and would likely save you time.

Now, let's move on to slide 9. Let's take a look at the actual results. The first screen caption at the top is showing the results of what we have just created. We've just created a query that polls all machines that have MYNOTEPAD.EXE installed. We're also showing here the query results for the NOT, which we're getting ready to create. Because we have not created it yet, it didn't want to show the results of both. Just to let you know, the queries we're showing here do return different results. I only have two machines in my database and they are the ones listed.

I now want to move on to slide 10. Let's begin to cover the information I think almost everyone is interested in. How in the world do you create a query that pulls all machines that don't have a particular file installed? I think these screens will probably be very familiar to you. We're not going to cover the initial part where we go in and specify the information, like we saw in slides 3 to 5. Since that's been covered and I think everyone understands how to create the basic query, we're going to move beyond that.

When we get to specifying the criterion properties, in a previous query we specified Simple value (we wanted to define a simple value, Software File, and the file name was MYNOTEPAD.EXE), but in this case, we want to do a subselect because we're going to use that query and compare the results against all other machines that we have inventoried. We want to find out whoever is not in that other query, the SoftwareYES query. Who is remaining? Who is left behind? So in this case, the criterion type that we would use is a longer set of values, it's Subselected values.

We want to go ahead and for the Where field, we'll click the Select button and specify the attribute of System Resource - Netbios Name. We're choosing these two, and they're identical to what we saw in slide 6. They do need to be identical because if we choose different attributes, then we're not going to have anything to compare against. We do have to have some overlap at this point. In slide 6, if we had chosen some other attributes than what we see here, just System Resource - Netbios Name, then any of those attributes could be specified here. But we do have to have some overlap so that we have an attribute to essentially join the two queries on. We're going to cover the subselect next, so let's move on to slide 11.

The operator is also key. We're choosing is not in because we're doing the subselect. The subselect is essentially going to be, if you'll recognize the syntax, the SoftwareYES query that looked for MYNOTEPAD.EXE. If we had clicked the Show Query Language button in the design view, this is essentially what we would have seen, this particular statement here.

Now, what I did to bring this in, because I'm not good enough to memorize all of the SQL syntax, is I just clicked the Browse button. It showed me a list of all of the other queries that I've already created and I just simply chose the SoftwareYES query. It made it easier for me and I'm hoping it will make it easier for you. I think the Browse button is most important here because it takes too long to write all of this stuff down.

Let's move on to the next slide, slide 12. This is where we're going to get into the different set of queries beyond what we saw with the SoftwareNOT. Now, the SoftwareNOT query that we left, that, as you saw, would work, because, here's the logic behind it. We chose SoftwareYES initially. We found all of the machines (one of my two) that had MYNOTEPAD.EXE in them. From those, because it only found one, we took and removed that one machine from the list of all machines in my database. So we took that one machine from all two of them, which left us one. The subselect showed us that one that was left behind. That's how we knew which machine did not have the MYNOTEPAD.EXE file installed on it. So at this point, if we wanted to, we could go ahead and create an advertisement or something to send out MYNOTEPAD.EXE and satisfy that requirement. It also helps if we're trying to upgrade for service packs and things like that.

Now, on slide 12 we're going to cover the OR and wildcard queries. These are pretty straightforward and we'll get to use some of these other buttons that we discussed earlier.

In this first screen, in the General tab, we're actually choosing a couple of different things. Previously, we had chosen System Resource - Netbios Name. Now, for the class, we're choosing Computer System class, and the attributes that we're choosing are Manufacturer and Model. We'll see why shortly.

So let's move ahead to slide 13. I'm skipping a couple of screens that we would see in the middle only because we have covered them previously and I think everyone has a thorough understanding of these. If not, please feel free to ask me a question when we get to the question and answer session.

Here, we're choosing, again, Simple value because we want to use the wildcards in this case. In the Where field, we're going ahead and choosing Computer System - Model. Now, for the operator, we want to use is like. This is where we're going to be able to use the wildcards, the percent signs, to pull information.

Now, in this case, we've got a machine out there. We know its model is M300, but we're not entirely sure what all of it is, so we're working with the understanding that this will help us find specifically how many M300 machines we have out there. So what we've done is simply typed in %M300% because we're not sure where in the model information it's stored. So we're not sure who made it or anything like that. We're hoping that this is going to give us the information that we need.

So let's move on to slide 14 and let's choose the OR as well because we also want to find all of our machines that have M700 in the model, but we're not really sure exactly what their model is. So we want to find all of the machines that are either an M300 model or an M700 model. How did we create this? Notice that the second button from the right, the one that has the ampersand and the pipe symbols (& |), is highlighted now. Earlier in the presentation it was not highlighted because we did not have multiple criteria selected. This is only going to be available if we have at least two criteria specified. Since we do have two, we went ahead and highlighted the AND Computer System - Model and clicked this button. It changed the AND to an OR since AND is the default. Now that we've clicked that and we have OR, our query now reads that we want to find all systems whose model number is like M300 or all computer systems whose model is like M700, because we're not really sure exactly what their models are.

Let's go ahead and move to slide 15. We want to take a look and see if our query worked, if it returned any information. We see here that yes, sure enough, it did. We found a model of Armada M300, so we do have an M300 somewhere in our system, and we also found Compaq Armada M700. Evidently, we have a couple of machines (we didn't realize they were Compaqs, but now we know they're Compaq machines) that have a model of M300 or M700.

The wildcard, the percent sign, doesn't necessarily have to surround the string of text we're looking for. We can put it either before or after depending on where we suspect that string exists. Or we can put it in the middle. Let's use Microsoft as an example. If we knew the first letter and the last letter, the M and the T, but we weren't sure of the middle, we would query on M%T and it would find all words that begin with an M and end with a T. So that's what we're going to use the percent sign on. I'm hoping that seeing the percent sign and how it might be used is going to help you in the future with creating your own queries.

At this point, my presentation is done. Primarily, what I wanted to do was cover the SoftwareYES and SoftwareNOT queries, and I wanted to cover the OR and wildcard queries. I hope that I've done this satisfactorily and I hope that everyone has learned something from this. I'm going to turn it back over to Heidi for any questions.

Heidi Moeller: Excellent. Thank you so much, Vic. It is time now to move on to the Q&A portion of our Support WebCast. First, we have a couple of reminders about the Q&A. The Q&A portion of the Support WebCast is intended to encourage further discussion of the Support WebCast topic; however, one-on-one product support issues are outside the scope of what we're able to address during these broadcasts. If you do have a technical support issue, we would encourage you to either phone in to product support or submit an incident on the Web.

Okay, with all of those details aside, let's go ahead and start the Q&A. We have Wally Mead here with us today to assist with the Q&A, so I want to thank Wally for coming over.

The first question is, Can you discuss techniques or tips to simplify building queries in the Web Report tool?

Vic: I'll go ahead and field this one. Essentially, this WebCast was designed or intended to cover creating queries in the Admin console within the SMS MMC. It's beyond the scope of what was intended by this WebCast. I think Wally has more to offer on this one.

Wally Mead: Yes. This WebCast, again, was on the Admin console itself, not the Web Reporting tool. On May 17 we will have our presentation on the SMS Web Reporting tool and I'll cover that at that point. We want to keep the focus today on the queries that Vic was working on and the topics for that.

Heidi: Excellent. Once again, that is on May 17 at 10 A.M. Pacific time. Next in line, Can the SMS Extract utility be modified to prompt for a collection?

Vic: I believe it can. In both the Microsoft Access and the Microsoft Excel versions, I believe it would take some modifications to the utility, but I do believe that it could be done. I have not done it myself so it's not something that I have firsthand experience on, but I believe the macros could be modified to allow that.

Wally: Yes. There are macros in Excel that you can download off of the support bundle or the Access utility so you have all of the source code there. So you should be able to easily take that and modify it to have it prompt for the query just like you can do in the query function itself online.

Heidi: Okay. Why can't we manually add new joins when designing a query? When the query is saved the new joins are ignored.

Vic: That I don't know, I have not come across that.

Wally: I have never heard that as being an issue before either. I've talked to numerous people that have created queries and had joins in their system and they're there. One thing you have to watch for when you create the query with the joins is (if I remember correctly) that they don't appear in the standard console, so you won't see the joins there. But if you click the Show Query Language button you'll see them in the actual WL query language and syntax there, but not in the GUI portion of it. So if you're looking at the GUI where you're seeing the criteria that are created and all of the different classes and attributes and so on, you won't see any joins there. But if you go to the Joins tab and then click Show Query Language, you do see them at that point. So verify that and if that's not working, go ahead and open up a case with PSS because I know you can create joins in queries. That should not be an issue at all.

Vic: Agreed.

Heidi: Excellent. I assume these are queries for inventory. Would it be better to query the Add/Remove registry keys on each computer?

Vic: That's going to be dependent on whether we have pulled that information into the database. Since these queries are pulling information through WMI directly from the SQL database, we would need to have that information in the database.

Wally: Yes. It would also depend upon how your program was installed. If you're looking for a single file like MYNOTEPAD.EXE, most likely that's not going to have been registered in Control Panel under Add/Remove Programs, so the Query Net registry key wouldn't pull the data you need. If it was a standard application that was assigned or published through Microsoft IntelliMirrorâ or advertised through SMS, that went to Add/Remove Programs, then certainly you could query that information and find the class of application you're looking for; however, Vic's focus was looking at individual files. Oftentimes, the customer says, "Well, I want to install this application, but this application requires this one specific DLL to be installed prior to this application working properly, so I want to verify that all of my computers have their proper DLL installed." You wouldn't be able to find that in the Add/Remove Programs. Instead, you'd be using the query that Vic showed you. Searching through the software inventory to find that DLL that was inventoried provided, again, that you had done inventory on that specific file type, in this case, the DLL files.

Heidi: Okay. Before we go on to the next question, I do want to encourage all of you who are listening in today to take just a few moments to submit some feedback. You can submit feedback to the alias feedback@microsoft.com. If you do use that alias, be sure to include "Support WebCast" in the subject line. We're very interested in your feedback regarding this Support WebCast, as well as any comments you have about the program overall or any suggestions for sessions that you'd like to see in the future.

The next question is What is the difference between overwriting the alias attribute and filling out a description in the very first screen?

Vic: In the first screen, the comment is going to describe exactly what the intent of this particular query is. In the Alias as field, what we're doing is changing the name of what is displayed. What would normally be displayed by default in the individual columns or fields is their attribute or class, period, and the specific instance that we've queried for; for example, System_Resource.Netbios_Name. If we alias that as simply Machine_Name, Machine_Name would appear as the column heading instead of System_Resource.Netbios_Name.

Wally: I think this is actually referring to how the results are displayed. The comment is not seen.

Heidi: Okay. The next question is actually more in the form of a comment, and it would be great to get your feedback on it. I think you should mention that you should use the "like" operator when looking for file names because the evaluation is case sensitive. If you use "equal to" and someone's machine has the file name saved as MYNOTEPAD.EXE, they wouldn't be returned in your query results. If you use "like," then it looks for a non-case sensitive MYNOTEPAD.EXE. Do either of you have some comments on that?

Vic: Wally, do you care to comment?

Wally: Well, this question came in during Vic's presentation and later on you saw that we did go with a like clause as the operator. But yes, if you do use an is equal to, I want to say it depends upon your SQL sort order whether your SQL is in case sensitive order or not.

Vic: It does.

Wally: Okay. Then if that's the case, it may or may not make any difference whether you do an is equal to or is like; however, oftentimes when you're looking at file names, as Vic specified, with the M300 and M700, you're not exactly sure how it's spelled and you're not exactly sure if there's other characters at the end of it, so using is like or using the wildcards can help you out because it can give you a greater capability for retrieving the information you're looking for.

Vic: Yes. I'll apologize. I probably didn't cover that very well or at all. My particular database, for example, does have a case sensitive sort order and that's why I was using all caps in this.

Heidi: Okay. Next in line, In the criteria screen software values list, is there any way to sort the list of values?

Vic: By default, I believe, it is sorted alphabetically. I don't know of a way beyond that to sort it. No.

Wally: I've seen them non-sorted and not being returned and I've talked to developers and there's no way to have that list sorted by default other than the way we just retrieved that data for you.

Heidi: Okay. The next question might be a little bit tough to address. I'm not sure if either of you are going to be able to address it right here, but we'll give it a go. In KB article Q238169 "SMS: Queries using SMS_R_SYSTEM Return Inaccurate Results," Microsoft instructs us to avoid using the SMS_R_SYSTEM table when creating queries. What is the current status on this? Will there be a fix in an upcoming service pack?

Vic: This is not an issue that I'm personally familiar with. I'm not familiar with the article off the top of my head either. To my knowledge, though, the results that I have seen per SP3 from an SP3-upgraded database appear to be accurate. I have not seen an occurrence of this at an SP3-level site.

Wally: I'm not aware of that Q article either and have not heard that other than making sure your inventory is up-to-date and making sure that, with the data you're pulling, you have the correct criteria and you've sorted everything else properly. I'm not aware of this. I'd have to go back and look up the Q article.

Vic: The only other thing that I can think of is, essentially, if you had a problem with either duplicate resource IDs or duplicate GUIDs in your database; that would be the only other thing I can think of.

Heidi: Can you export the results of a query to an Excel spreadsheet?

Vic: From within the MMC Admin console, I don't believe so. I believe that's why we have the utilities for the Access database, the SMS Extract, made available for the Excel spreadsheet because those allow you to create the reports and send those to a printer and organize them in some way that's suitable or presentable for you. But I don't know of a way that they can be exported directly from the Admin console.

Wally: If you're running on Microsoft Windowsâ 2000 or if you're running MMC 1.2, MMC 1.2 does have the ability of doing an export list so you can take the results, if you're generating from a query or a collection, and export them out to a file whether it's tab delimited or comma delimited or Unicode, and then take that file and import that into Excel. So you can use that method, but one of the things that we're aware of in the SMS Admin console is the lack of printing ability or exporting ability outside of what MMC gives you. In MMC 1.1, if you're using Microsoft Windows NTâ 4.0, using the original MMC does not provide that capability, but MMC 1.2, which is the default with Windows 2000, does provide that capability.

Heidi: Excellent. Is there a good source for getting prebuilt queries that you can just customize?

Vic: I was sent an e-mail earlier this morning by one of my peers about a Web site where someone has gone through and created several SMS queries that do searches for all sorts of information from the database and they are stored in self-extracting executables. The URL is http://www.smsql.0pi.com/.

Wally: Yes. There's nothing that Microsoft provides that I'm aware of. There's nothing up on our SMS Web site that gives you a list of queries that people have generated and lets you download them and use them. If you find queries that you have created and you like, you can go ahead and use the rescue utility Query Edit and that will let you export your queries to text files and then import them back in, so you can reinstall or import them to a new site. So if you know somebody that's created a query that you like, you can have them export it to a text file, e-mail it to you, and then you can import it into your database and make any customizations.

Heidi: Is there any way to get the value list in sorted order?

Vic: Not that I know of.

Wally: That's the one we answered a little while ago. Sometimes it's sorted and sometimes it doesn't seem to be sorted. I mentioned that I talked to a developer and they weren't aware of a way to have that sorted by default or have you doing anything with that.

Heidi: Okay. On to the next question, I'm a little bit confused. It seems as though there are some steps that are missing in between slide 10 and 11. Would you mind reviewing that?

Vic: Absolutely. With slides 10 and 11, because we were moving on to the SoftwareNOT query, I did go through and skip several slides and several steps in the creation of this query. Now, I think, for clarity's sake, I could have reversed these and shown the results from slide 11 first, which would have shown all of the fields completed, and then shown slide 10 to show how they are filled in. But I did leave steps out just to try and save space and to save time because we had already covered it previously. I also didn't want to go over redundant data.

But in slide 11 we see it completed. If you click the Select button by the Where field in slide 11, you see the drop-down list box from slide ten. Select Attribute is the name of the dialog box. This is where you'll fill in your information with System Resource attribute class and Netbios Name. After you click OK on that dialog box, then it populates the Where field. Then there's the Operator drop-down list box, which probably has two-dozen or so options; is not in is near the top.

The Browse is just that. If you click Browse, it will bring up a list (I'm not sure if it's ordered or not; I believe it's ordered by the way it displays in the Admin console) of all of the queries that you've created, which in this case, we just simply chose the SoftwareYES query, and it imports only the WQL into it as we see here.

Heidi: Okay. Well, the next question sounds like it's leaning toward support, but I'm going to go ahead and ask it in case there's a general answer for everybody. What causes an error when creating an alias in the query? I often get a cryptic message that there needs to be a resolution with inner or outer joins and so on.

Vic: I've seen a couple of different things. If you leave a space, for example, earlier in the presentation I think I used the alias of Machine_Name. I left the underscore in there only because it would have created a space between the two words and we do have some problems there. I think that may attribute to it. Also, be careful of the characters used, if you do use any special characters, but spaces are the most prevalent that I've seen. If you alias something, make sure that it is aliased consistently throughout the query. If you create multiple criteria, then it may depend on how you join them; because you'll have to have matching criteria on opposite sides of the join, make sure you alias them the same.

Heidi: Is there a life cycle on queries? If a query is not used for a length of time, will it be deleted?

Vic: To my knowledge, no, there's not any life cycle on anything within the SMS database other than what you can set within the database maintenance tasks. Collections will stay there until you delete them. As far as I'm aware, packages, advertisements, as well as queries will also remain there until you decide to delete them.

Wally: Yes. That's my understanding as well. I've never heard of queries or anything else being deleted other than, like Vic said, whatever's in the database maintenance tasks, which is inventory, history, age discovery data, and so on, but not anything with queries.

Heidi: Sometimes I need to find out whether there is a certain entry in a log file on each of my boxes. Can this also be accomplished with SMS queries?

Vic: This is going to be a little more difficult to accomplish because you may end up having to create a no-ID myth that populates the database with log entries. We do have a status message viewer, which is going to pull the events, essentially. It's not going to show you verbatim what you would find in the logs, but it would still show you a particular event. It's going to be difficult if not near impossible (but it can be done) to actually create some sort of utility or script that will pull log information directly into the database so that you can create a query from it. I would recommend sticking with just the status message viewer for something like that.

Wally: Yes. Just to add on to what Vic said, the key there is that the log file information is not stored in the database. So what the queries are pulling out is information that's in the SMS database. If they're not in the database, then you obviously can't query that information. So use either ID myth or write a separate utility. Just looking for status messages for the information in the status system is very verbose in a lot of cases. Or you can use other third-party utilities just to query on your SMS log files if they're enabled.

Heidi: Okay. Moving right along to the next question. Are we able to determine the version of a file using the file name? For instance, Word.exe for Office 97 is a different size .exe than Word.exe for Office 2000. Any easy suggestions for determining version from the file name?

Vic: Using the file name only, you can't. What you would end up having to do is create a query and have one of the criteria not only be the file name, but either the file date, the file size, or the file version. In the software file class there are about 18 to 20 or so attributes, and I do know that there are file version and file size and file timestamp options in there. So you can use one of those along with the file name and combine the two criteria to find specifically what you're looking for.

Heidi: I know that, for example, with Word, there are all sorts of different components that you could install that change the size of the actual file. Does that make a difference or is do you simply choose different criteria if that were the case?

Vic: I'm not really sure on the answer here, and I'm not 100 percent sure on the question either, but I'm hoping I'm close. Please let me know if I'm not. If you are aware of a particular DLL or something that can clue you into a component within Word that is installed, that would be the way to go because our software inventory pulls information from the header of the executable or whichever file. So that's how we're going to pull who the manufacturer is, what the version is, and information like that. If the header is not populated, then that may be why you see that information missing in the database.

Now, if we go through and modify the executable and change its size and do some other things to it, depending on what options are selected at install time, that I'm not really sure on. That's going to end up being more for the Word support group to find out if we change it and what other files would clue us in to a specific component within Word or whatever the application is that we could inventory on.

Wally: Yes. Heidi, the way your question was going was if we add additional components, do we change our Winword.exe executable, and we wouldn't. It wouldn't change that at all. So it would still be Word 2000 or Word 6.0 or Word 7.0 or whatever the version is, and then you'd just have to query to see if additional components, such as graph or chart or whatever else, had been installed.

Heidi: Okay. Moving on to the next question. Is the OR command inclusive or exclusive?

Vic: That's a good question. I like that question. The OR command is inclusive. Let's do another example. Let's use MYNOTEPAD.EXE and let's also use MYAPPLICATION.EXE. If we want to find all machines that have either or, then essentially, we're going to go through the first criterion, "Do you have MYNOTEPAD.EXE?" If you do, then we pull you in. We also ask if you have MYAPPLICATION.EXE. Then we'll pull that information in as well. So we're going to pull results from both and combine them into the results set. So we're probably going to get all machines in a case like that. But it is inclusive.

Heidi: Excellent. Next in line, When you use different classes, for example, system resources and computer systems, on the query results, you get a very large name for the headers. Do you know why this is?

Vic: I'm not sure on the question. Wally?

Wally: Yes. I believe it's because of the fact that you have different classes that you're going to. So in your headers that get displayed in the query results properties, we go ahead and show you the class name and then the attribute name. So we do something like System_Resource.Computer_Name and then Computer_System.Model_Number or whatever it is. So because of the fact that you're going with different classes for your attributes, we go ahead and display the class name as well as the attribute name. So the column headings (I think that is what they're really getting at) get to be quite large because of the fact that we do show both of those at the same time.

Vic: Those can be aliased and shortened.

Heidi: Could you explain "Omit duplicate rows" under the General tab?

Vic: Let's go back to our OR query. We want to find all machines that have MYNOTEPAD.EXE or we want to find all machines that have MYAPPLICATION.EXE. If a machine happens to have both, it's going to show up twice in the results. If you are, for whatever reason, trying to create, for example, an advertisement to update those particular files on all those machines, then there is the potential that that machine could be targeted twice.

If you do the omit duplicates, it's going to call out all of the duplicates. Because my PC is my machine name, it's normally going to show up twice until you select that check box. Then after you select that check box, it's going to show up only one time so it's easier to read. You don't have to worry about whether it appeared three times or four times. The total rows returned will be an accurate count of how many machines have this file or that file. So it makes the results slim down and makes them easier to read.

Wally: If you look at slide 15, which is Vic's next to last slide, you will see that it shows Compaq with an Armada M300 three times and it shows the educational testing service with a Compaq Armada M700 twice. If these were the only two attributes that were displayed and if the query had selected Omit duplicate rows, you'd only see one entry for each of those computers. So you'd see two results returned instead of five results. You'd see Compaq Armada M300 and Educational Testing Service Compaq Armada M700. So if the data presented is exactly the same, then Omit duplicate rows will crunch that down to a single entry for each physical box.

Sometimes, if you go in there and try to display queries, a very common thing that people do is they create a query to show all computers that don't have a piece of software installed. The way they create the query (and Vic kind of alluded to it when he talked about the NOT clause) is they search for the file and then they click the NOT button and say, "Not that file." What that does is it returns a list of every piece of software you have installed that's not the one you're looking for, and every computer has a lot of software installed. So if you did that and you were displaying each of the files that you were querying on, you couldn't omit that because you're displaying a different attribute; however, if you're just showing the computer name and it showed the same computer 7,000 times because you had 7,000 files that weren't MYNOTEPAD.EXE, and if you selected Omit duplicate rows, then it would only show you that computer name one time. So that's what the Omit duplicate rows query syntax is for.

Heidi: Okay. We have had a couple of customers who sent some feedback about a source for queries so I want to let all of you know what that source is. I want to make sure you do realize that we are not endorsing this site, but we just want to provide it for your information. It is http://www.swynk.com/. Apparently, there are a lot of queries that are available from there.

Okay. Moving on to the next question. Is there a way, maybe using an additional SMS snap-in, to use SMS to monitor network devices and servers - service availability, network connections, etc.?

Vic: I did have one case not too long ago where a customer was doing a query on the services that had been installed on machines and, just to keep within the scope of this particular WebCast, he was looking to do a query of which machines had a particular service installed so that he could make sure that those were up and running, make sure their state was running, and make sure the server was installed with the proper software so that it could do the job that was intended for it. So we just simply built a query using, essentially, the SoftwareNOT query to build a ServicesNOT query. The service that he wanted was a particular network monitored type of service that ran automatically in the background. It was a third-party service, I'm not familiar with what the name was, but it was just to find all of the servers that did not have that particular monitoring service installed, but I don't know of any snap-ins.

Wally: Yes. I'm not aware of anything that SMS provides either for doing network communication-type box monitoring, other than the SMS trace function. There's a trace function, or network trace as we call it, inside SMS, that allows you to view SMS servers, including CABs, logon points, distribution points, software metering servers, site servers, database servers, whatever, and the network path between the site server and those remote servers that may include routers. That has the ability, with the network trace, of letting you see, specifically for SMS, what the state is, whether the SMS service is up or down, running or not running, as well as testing the communications line between the SMS site server and this remote computer through a TCP/IP ping command. It will check whether it's pinging the entire path between a site server and that remote box or the router in between or a specific computer itself to see if you can get to it. So that's what we have within SMS for that type of monitoring. You might look at something like MOM that can help out a little bit more, but again, it's not focused on SMS, but it also is not focused on network devices themselves. So it's Windows NT, Windows 2000 servers, their services, and availability.

Heidi: Could you please explain joins and their purpose?

Vic: Joins are predominately used when you want to join two tables of dissimilar or different information that have a common criterion within them. The most common criteria that we use throughout the SMS database is just a particular key value that represents an individual machine instance. For example, my PC is instance number 1 in my database. Then, if I wanted to do a join, it would use the instance of 1 in my first table, let's say, Software File, and then the instance value of 1 in my second table, whatever it has in it, and it would combine those criteria.

Now, what it's going to do is, wherever the rows match up, wherever there's an instance key of 1 in the first table and an instance key of 1 in the second table, it's going to use those to tie those two tables together. It's beyond the scope of this WebCast to go into the different types of joins, but there's very good information in the SQL books online in regards to the types of joins, and they would probably do a lot better job and explain in a lot more detail than we have time for here.

Heidi: Okay. The next question is with regards to querying on non-existent fields. Inventory fields may vary from client to client depending on whether or not they have a no-ID myth file. How can I query on systems that do not contain a field? The value is not NO, it does not exist. Is there any mechanism to identify a record that does not have a field?

Vic: That logic is going to be along the lines of the SoftwareNOT query logic in terms of, first of all, you're going to have to think, "What query do you have that currently exists out there that would return all resources?" Then you have to look at your second query criteria that's going to return a limited scope, and essentially do a NOT between the two so that you remove whomever is left over. If the no-ID myth information is your determining factor for who has it, then you're going to basically go through all your systems collection, for example, and if they have a no-ID entry, you'll remove them until you get to the end. Whomever is left over, you know those machines do not have that particular no-ID myth entry or any information at all that you were looking for.

Heidi: Okay. Excellent. Next in line, Is there any way to use advertisement status queries in collections?

Vic: Not that I know of. I have not played with that part of it so I'm not really sure.

Wally: I'm not aware of anything either. The rest of the question is trying to query on failed distributions and redeploy your software so that would be, like Vic showed, creating a query that looks for all computers that don't have this piece of software installed. So whatever you're trying to deploy, find a file that would be unique for that piece of software and then find all your computers that don't have that piece of software installed. Then you know those are computers that either did not receive the advertisement, weren't targeted for the advertisement, they hadn't run the advertisement yet, or they had the advertisement fail. Then you can go ahead and re-advertise and kick that back off. In the release notes for Service Pack 2 and Service Pack 3, we talked about how you can re-advertise failed advertisements - by creating new assignments for them or a new program or whatever. So that is discussed in the release notes for Service Pack 2 as well as Service Pack 3.

Vic: One thing I did not cover, which I think a brief statement will do justice to, is that what was originally intended for SMS and the queries, as well as the collections, was that you could create a collection. We'll use Office 97 as an example. You want to find all machines that have Office 97 on it. Then you create a second query with all machines that have Office 97 SR1. Then you add a third one that has SR2 installed on it. You would create collections based on those, and with your first collection that just has Office 97, you would target all of those machines with the recurring advertisement and upgrade them to SR1. Then you'd go to your SR1 collection and you would target a reoccurring advertisement to upgrade them to SR2. That way, whoever comes on your network is going to get the advertisements that would eventually get them to the most up-to-date version no matter where they are when they originally join in.

Heidi: Okay. Excellent. I want to once again quickly encourage you to please submit some feedback to us. It definitely helps us make sure we're hitting the mark with what we're offering through our program.

The next question is, Do the NOT and NO queries need a YES query? If I cut and paste the WMI query text into the subselect query box instead of using the browser, will it need the YES part of the query in the future? Can I delete the query if I don't really need to know which machines have MYNOTEPAD.EXE, or is there a dependency for the YES query to remain in existence?

Vic: Very good question. Fortunately, there is not a dependency. When we go and browse for that particular SoftwareYES query, we browse merely to pull the WQL language information and syntax into the SoftwareNOT query. You do not need to keep that query. Once that syntax is in there, if you take a look at the query language of the SoftwareNOT query, you'll notice the subselect within and then in parentheses you'll see the entire SoftwareYES query. So there is not a dependency. After it is pulled in it is becomes a stand-alone query.

Heidi: Okay. Moving on to the next question with regards to collections. After creating a query I make a collection. Why doesn't the collection show the same information as the query, and why won't it sort by the rules in the query? It doesn't show IP address and last user. Is that something that you can address or is that a product support issue?

Vic: That's probably going to get into a product support issue. Generally, I have seen the same results in collections as I've seen in the queries that I've created, so if you're seeing a conflict, then that's likely something that would need to be addressed by product support. Wally, do you have any comments?

Wally: The only thing I'd say is that, just like Vic was talking about with the subselect, after you take the subselect and you browse it in or type it in manually, it gets embedded into the new queries syntax. The same thing happens with the collection. Once you use a query as the basis of a collection as your membership rule, if you go back out and modify that query itself independently of the collection, it does not remodify the collection itself and the query it was using so that there are two different queries now. So make sure you haven't changed that.

Also, make sure that your query is not running in a collection limited space or maybe when you're running your collection you can run a collection that's built out of or based on the contents of a previous collection, so maybe use a different subset of criteria. Other than that, you do get to specify what columns you have with a query. So when you create a query, you get to create the attributes that you want to display. When you create a collection, you're not given that opportunity. We have a default set of attributes that we are going to display and you don't get to control those, but you do get to control the attributes that are returned with a query. So make sure that it's not that that you're looking at and being confused by.

Heidi: Okay. Moving right along. The amount of data returned increases processing time of a query, but are there any operations inside a query that cause it to run longer, for example, in joining classes or many operators?

Vic: Absolutely. The more criteria and the more operators that you add, essentially, the more complex the query becomes and most definitely the more CPU cycles it's going to require. A simple one like the SoftwareYES query where we were simply looking through a table to find this particular file identifier and then going through and finding all of the machines that have this file identifier attached to it, that's going to be a very fast query. If we go through and start adding other criteria and then AND-ing some of those, OR-ing some of those, and building joins around others, then yes, that's definitely going to cause queries to run longer.

Heidi: Okay. How long before the PC inventory database will not contain entries for PCs that are no longer available? Does SMS perform deleting of old entries automatically? The reason that I've asked this question is due to pulling old information from an SMS database that is old. Does that make sense?

Vic: It does. Essentially, that is something specified in the database maintenance cleanup tasks. By default, they will have different values. I think some of them are 60 days. Some of them are 90 days. You would need to take a look at what they are set on for your particular site. I think I've also heard, recently, that there may be an issue with a couple of those not completely cleaning the database. This one I'll defer to Wally. He may have better or more up-to-date information than I have.

Wally: Yes. The task that Vic refers to is the Delete Aged Inventory History task, and that's going to remove history for systems that have not been updated more than 90 days. That's a default value. There is another task called Delete Aged Discovery Data. That means that the discovery data for your resources that have not been updated within 90 days (again, that's by default, but you can change that) will be removed from the database. When we remove the discovery data from the database, we're removing all references to that client computer out of the database, which includes its inventory as well, hardware and software inventory.

So that's one of the things that we've talked about before in other WebCasts to ensure that you keep some sort of discovery method available and running to keep your database updated or refreshed. Otherwise, we will age out that data. For example, if you turn off Heartbeat Discovery or if you set your Heartbeat Discovery to an interval longer than your Delete Aged Discovery Data task runs, then we'll say, "Oh, we don't need that computer anymore because it hasn't been updated in 30 days (or whatever it is)," and we'll remove it, which includes your inventory data as well.

So those are the two tasks that you would be looking at to make sure that you have them set properly: the Delete Aged Inventory History for cleaning up your old history for existing clients, and the Delete Aged Discovery Data for removing systems that are no longer active on your network.

Heidi: Is there an easy way to inventory Java applications on PCs without inventorying the .bat files that launch them?

Vic: The only way that I know of is if you have a particular file extension or attribute that you're inventorying after. Unfortunately, I'm not well-versed in the Java realm of things. That one I'll defer to Wally.

Wally: I'm not familiar with Java either, so if you have a batch file that's launching them off, you could look at that. If you have metering installed and have been using metering, you could see who has been running that batch file or whatever utility has been kicked off, but you'd have to find something specific to the Java environment there. The only thing that's unique there is how they were launched off, whatever the execution engine is, and in this case you're mentioning a batch file. Inventorying that would be the only thing I would know. Again, like Vic, I'm not familiar with Java at all.

Heidi: Okay. When you use system resource IP addresses, why does SMS keep all of the different IP addresses that a PC has had? Sometimes it seems that there are up to 10 different ones on, for example, a laptop computer.

Vic: That's a good question. Only because, if you have, for example, DHCP, the lease is very short and you also have an inventory cycle that runs pretty quickly, say once or twice a week. Then, if your lease is short and your DHCP lease is every two weeks, for example, then there's a possibility that if your user goes on vacation or something happens and they lose their IP address and they have to get a new one, the next time that we go through and we do an inventory we're going to pick up that new one.

Also, if you have machines (this is most likely to happen to servers) that are multi-homed, then we're going to have an occurrence for each of them in the database as well. So that's another common occurrence of why that information is there. What you're essentially looking at, though, is discovery data. As Wally mentioned, if you age out discovery data, when you age out the discovery it removes all of the information from the hardware inventory, software inventory, and the collection information. It goes through and cleans those tables. But essentially you are looking at discovered IP address information off of these machines, which again, could change because of DHCP leases and dial-in. Where do you dial-in from? Where do you dial-in to? Things like that.

Heidi: Excellent. Next in line with regard to three-tier architecture and queries, I have a three-tier architecture. I have a central site, a European primary, and a Paris primary. I noticed that collections that trickle down the hierarchy referenced queries from the central site. The query does not come down the tree. Wouldn't it make more sense for the query to flow down the tree as well? Is there any limit to how deep a collection can be separated from the query?

Vic: I'm not so sure it would be good to replicate the queries down because we do replicate all of the data that is stored in the child databases up the hierarchy, so the central site, by default, should be the largest database with absolutely the most information in it. Now, it depends on the criteria that you specify in the query - you can set the criteria to something like the site code, it's particular installed site, different information that is site-specific or IP boundary-specific, things like that. So if the query is created at the central site and that's where you're doing your primary source management, then because you can filter out or query on IP ranges, site-specific information, and things like that, I don't know that it would be needed.

Now, you can use utilities like SMSSPMAN.EXE to export queries. It does export site properties as well, but it can also be used to export collections and queries and things like that. So you can export the queries from your central site and send them to, say, the one in Paris or in Europe or wherever around the world that particular query may be needed as well.

Wally: Additionally, with that, you can also use the Query Edit utility, which is part of the resource kit, to take a query and export it to a text file (I mentioned that earlier) and then e-mail it down to your child site or whatever. I may be mistaken here, but I thought for sure when we took a query as a membership rule for a collection, we embedded the query syntax into that collection so that when the collection gets down to your child site, the query syntax is already there so you don't really need that query to go down as well because the query syntax is already embedded into the collection at the time that you did that importing and adding that query as a membership rule.

So like Vic said, you don't really need the query down there because of the fact that it is embedded into that collection so your collection should work fine at your child site and there's no limit on how deep your collection can be because, again, it's not really separated from the query because the query does get embedded. As I mentioned before, when you do that importing or embedding of the query, if you go out and modify that query in the Queries node, it does not modify the collection query that was used because that query wasn't embedded at that point and time. So they just took a snapshot of it and put the language in their SQL syntax in the collection so it's two distinct things. So that's why you don't really need to send the query down because you've got the data there already inside that collection.

Now, unless I'm mistaken, I believe that's the way it works. But if you did want to get the query down, you could use the Site Properties Manager that Vic mentioned comes with the resource kit, or the Query Edit utility that I mentioned comes with the resource kit. You could cut and paste your queries and edit them the way you wanted to in a text file and then give them to your child site and you can import them through either of those utilities so they can have the exact same queries as you have. That would be my information on that one.

Heidi: With that question answered we have cleared the queue of all questions that were submitted during today's broadcast. We certainly had an active Q&A. I want to thank all of you for joining us. I do hope you found the content valuable. As a reminder, our next SMS session is about the Web Reporting tool and it is scheduled for May 17 at 10 A.M. Pacific Time. We do hope you have an opportunity to join us. Please submit some feedback if you have a moment. We sincerely appreciate it. Have a great day and goodbye.