Post 11 – Creation of queries and building query based collections

In the previous post we collected inventory and created simple collections.

In this post we will create Queries and build query based collections.

Creation of Queries and Building Query-based Collections

All of the data collected in stored in the SQL database.There is a built in Query builder in SCCM, but it can be easier to build the query using SQL Management Studio.

Go to Monitoring, and Queries. There is a large amount of predefined queries. Selecting the query Systems by Last Logged On User Requires you to have made changes to Active Directory in order to audit logged on events.

Enable Active Directory Auditing

Go to the domain controller, in this case DC1. Open Group Policy Management. Select the Default Domain Policy and select Edit. Go to Computer Configuration, Policies, Windows Settings, Security Settings, Local Policies, and Audit Policy.

Audit policy selected

Audit policy selected

Select Audit logon events, and select Success. Select Apply and close.

audit logon success

Audit logon event

Now close out Configuration Manager, and log out of your system.

 

Go to your configuration manager server, CONFIGMGR.

Open cmd and do a gpupdate/force to force the new group policy to the server. Once you log back in your successful logon should be tracked.

Now open Configuration Manager and go to Monitoring. Select Queries and select Systems by Last Logged On User. 

systems by last

Last logged on user

Select Edit Query Statement to see how this query is working.Select the criteria tab.

last logon query info

last logon query info

Click show Query language displays how the query is built:

SELECT Name, LastLogonUserName, SMSAssignedSites, Client, IPAddresses, IPSubnets, MACAddresses, OperatingSystemNameandVersion, ResourceDomainORWorkgroup, LastLogonUserDomain, AgentName, AgentSite, AgentTime, SNMPCommunityName, SystemRoles, SMSUniqueIdentifier, ResourceId, ResourceNames, ResourceType, NetbiosName from sms_r_system   where LastLogonUserName = ##PRM:sms_r_system.LastLogonUserName##

You don’t edit the default queries but seeing how it’s built is important for figuring out how it works.

You can scroll down and see the attributes of the query.The Criteria defined for the query is: System Resource.Last Logon User is equal to <prompted value>. All of the various buttons define the constraints for the query. Often you will join multiple tables together in order to build your query.

Select Close. Queries are written in the WQL language which looks a lot like SQL but isn’t exactly the same thing. Double click on the System Resource by Last Logon user and enter the user account SMSadmin. You should see the following:

Query works!

Query works!

You can now track the last logged on user’s into your domain.

Running resource explorer you can determine all of the responses for all of the machines, and gather them into a report.

Creating first query

Go to Monitoring, Queries, and right click to Create a Query.

create query

Call the Query: Systems with Microsoft Word installed, as we want to find what systems have Microsoft word installed. On collection limiting, select the Limit to collection checkbox and select browse, select All Users.

You can select individual user or user groups and devices or device groups to limit the collection. We won’t limit to any collection in this example. .

Click on Edit Query Statement to define the query.Click on the criteria tab and select the little star icon. This will allow the adding of a criteria or restraint to this query. Select simple value, and click on the Select button.This will define the attribute. Select the Attribute class: Software Files, and the Attribute: File Name and select OK.

Setting attributes

Setting attributes

As the installed name of Microsoft Word may be something else, winword.exe has been the same for a long time. On the criterion properties select Simple value, on the operator select is equal to, type in value: winword.exe

criterion properties

The criteria has now been created. It should read: Software Files.File Name is equal to “winword.exe”

query statement

query statement

Select the General Tab. This tab defines what do you want to see when the query is run. Select the Star icon. Now select the Select button. This will define the attribute you are looking for.

Select Attribute class: System Resource

On Attribute select Name. Click OK.

system resource attribute

Sort by Ascending.

This will include the System name in the results of the query.

Now add the System Resource and property Last Logon User Name. Also add the file version (in Software Files class) and use the property File Version.

adding properties to query

adding properties to query

This will make the query a lot more useful. The joins tab will now show you that you are joining the system resource tab with the Software Files table.

joins

Click OK to close the query statement definition. Select Next. Set the collection to not collection limited, select Next again and close to create the query. You now have a query defined that will find which machines have Microsoft Word installed.

completed query

Completed query

I see that I have Word installed on the computer named XPClient.

 

If you don’t see any installed software ensure that the Hardware Inventory portion of the client agent settings is set to query installed software:

HW inventory Classes

If you don’t see any information populated, go to Configuration Manager, Assets and Compliance, Devices, All Systems to make sure you can see your Windows XP clients in the collection and the client activity is set to Active. Run Resource Explorer, by clicking on the menu, selecting Start and then Resource Explorer. In Resource Explorer go to the software tab and check File Details, and last Software scan. If they are blank run the following Visual Basic Script on your client PC:

In order to force Software Inventory on SCCM Client, run the following VBScript:

sInventoryActionID = "{00000000-0000-0000-0000-000000000002}"
Set oLocator = CreateObject("WbemScripting.SWbemLocator")
Set oServices = oLocator.ConnectServer( , "root\ccm\invagt")
oServices.Delete "InventoryActionStatus.InventoryActionID=""" & sInventoryActionID & """"
wscript.sleep 3000
Set cpApplet = CreateObject("CPAPPLET.CPAppletMgr")
Set actions = cpApplet.GetClientActions
For Each action In actions
If Instr(action.Name,"Software Inventory") > 0 Then
action.PerformAction
End if
Next

The software inventory process can take a very long time.

I have also had success using the SCCM software tool located at: http://sourceforge.net/projects/smsclictr/ in order to publish the Software Inventory.

There is no last logon user information returned from the query as sometimes the query builder (although simple) has left out something. You can build more powerfull and detailed queries by querying the Config Manager database directly.

 Query the database directly

You can also use the SQL management studio to create queries. Or even easier use views to use a pseudo query. Open the SQL Management Studio. I am using SQL Server 2008R2 so my view may be a little bit different from yours.

Go to Databases, CM_BP1. Click on Views, dbo.v_GS_SoftwareFile view. This software file view shows all of the available information on software files.

software file view

 

Right click on dbo.v_GS_SoftwareFile and Select Top 1000 Rows. You can now see everything Configuration Manager has stored in it’s database about Software File.

db view

db view

Right click and select  New View

We are looking for v_R_System and select Add. Now add v_GS_SoftwareFile. Now select Close. 

tables added

tables added

Select ResourceID in both tables and drag it from the first table to the second table. This creates a join relationship.

Tables are joined

Tables are joined

Now you select different attributes from both tables. Select Filename in the V_GS_Software table.

You are interested in Filename, in the filter field enter =winword.exe. Change Sort Order to 1 (which applies the ascending tag)

winword

Config Manager doesn’t actually use SQL it uses WQL. So the translation isn’t exactly direct.

Device collection Creation Per Query

 

Go to Assets and Compliance, Device Collections, right click to create device collection.

Call the collection Systems with Microsoft Word Installed.

On limiting collection select browse and select All Systems.

ms word installed computers

Select Next. Select Add Rule and Select Query Rule. On the Query Rule properties, select Import Query Statement and select Systems with Microsoft Word Installed. On the Name of the query rule use Systems with Microsoft Word.

query rule word

Select OK. On the create device collection wizard turn on Use incremental updates for this collection. Select Next, and next again to create the collection. 

collection completed

 

Creation of a user collection

You can include users and devices together. For example, you can deploy Microsoft office to a device a user is working at. As the user may have more than one device.

Go to User Collections , and right click to create new Collection. Call the collection People who need office. On limiting collection select All users, and select Next.

On adding rule, select Query Rule.

On Query Rule Properties call it People.Select Edit Query Statement, and select the star icon. Select the Select button

Make sure Resource Class selects: User Resource, and the query statement says: Select * from SMS_R_User.

On select Attribute, select Attribute Class: User Resource, Attribute: Security Group Name and select OK.

security group name

Select OK again.

Select the Criteria tab, and select the Star.

On select Attribute, select Attribute Class: User Resource, Attribute: Security Group Name and select OK.

security group name

On select value enter COMPANY\Domain Users. This will define which users will get Microsoft office. Click OK once this has been selected.

defining criterion

Select OK and OK again.

 

Select the checkbox for Use incremental updates for this collection. Select Next.

Select Next.

The user collection has now been created.

group who needs office define

We can add other groups such as people who should have 7zip or who need other files.

 

The next post will be about building Reports.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s