Enhansoft

Enhansoft Support and Announcement Blog

Subselect Query for Reports (SQL)

By Garth Jones

This post is a continuation of my series about how queries are processed. Please start with the first blog post in this series for more details.

Subselect Query for Reports (SQL)

Today’s blog post will show you how to create a subselect query in order to discover which PCs do not have Microsoft Project 2010 installed using T-SQL (generally called SQL) for use in SQL Server Reporting Services (SSRS) reports.

Here are some subselect query writing tips to always keep in mind:

· For the positive query don’t return the PC name because of the possibility of duplicate PC names; instead return the ResourceID information.

· Remove any unnecessary SQL views.

Below is the positive SQL query to find all PCs that have Microsoft Project 2010 installed.

Select

                ARP.ResourceID

From

                dbo.v_ADD_REMOVE_PROGRAMS ARP

Where

                ARP.DisplayName0= 'Microsoft Project 2010'

Below is the negative SQL query to find all PCs that have Microsoft Project 2010 installed.

Select   

                R.Name0

From

                dbo.v_R_System R

Where

                R.ResourceID not in

                (

            ) 

Now, here is the subselect query where I combine the above positive query with the negative query to find all PCs that do not have Microsoft Project 2010 installed.

Select   

                R.Name0

From

                dbo.v_R_System R

Where

                R.ResourceID not in

                (

                                Select

                                                ARP.ResourceID

                                From

                                                dbo.v_ADD_REMOVE_PROGRAMS ARP

                                Where

                                                ARP.DisplayName0= 'Microsoft Project 2010'

            ) 

Hopefully this gives you more insight into creating and using subselect queries for SQL reports.

Next week, I will show you how to create a subselect query for collections (WQL).

The Subselect Query

By Garth Jones

In my blog posts from last week I talked about how queries are processed and how a Not Equal To operator is processed.

In this blog post I will help you to write a subselect query in order to determine what PCs are missing a specific type of software.

A subselect query is made up of two parts: the positive query and the negative query. This might seem backwards, but in order to ensure that you get the results that you are looking for you need to exclude all PCs with a positive result.

Let’s use the same table from the previous blog posts for all of our example queries. This table is comprised of 4 PCs and their add/remove program (ARP) details.

PC Name

PC1

PC2

PC3

PC4

1

Adobe Reader XI

Microsoft Project 2010

Visio 12

Adobe Reader X

2

Visio 12

CorelDraw

ITunes

Microsoft Project 2010

3

ITunes

Office 2010

WordStar

DataStar

4

Microsoft Office 2013

 

 

Lotus123

5

 

 

 

Kix 2010


Using pseudocode I will write the negative query this way:

     Find all PCs where the ARP display name is Not Equal To Microsoft Project 2010

A lot of people would expect to get PC1 and PC3 returned in the result set, but that is not the case as I demonstrated in my previous blog post.

However, in order to achieve the desired results we need to break the query into two parts as I explained earlier; a positive query and a negative query.

Here’s the pseudocode for the positive query:

     Find all PCs where the ARP display name is Equal To Microsoft Project 2010

If you think that PC2 and PC4 should be returned to the result set then you would be correct, but we only want to know what PCs do not have Microsoft Project 2010 installed.

This brings us to the subselect query. After using the positive query, we know which PCs have Microsoft Project 2010 installed. Now, combining it with the negative query we will find out which ones do not have Microsoft Project 2010 installed.

I re-write the pseudocode combining both the positive and negative queries:

     Find all PCs where the PC is not in (Find all PCs where the ARP display name is Equal To Microsoft Project 2010)

First this query will find all of the PCs with Microsoft Project 2010 installed and then it will exclude those PCs from the list of all PCs. Therefore the end result will only display those PCs which are not in the bracketed part of the query. Finally, PC1 and PC3 show up in the result set!

Up until this point, we have talked about how to write queries in a generic sense, but these same principles can be applied to either SQL or WQL queries. Now that you know more about how queries are processed, the next step is to create queries for customized reports (SQL) or collections (WQL).

In my next two blog posts, I will show you how to create queries for both of these items.

The Subselect Query

Computer Software Update Status is September’s Free SSRS Report

Get your copy of Computer Software Update Status today by emailing Info AT Enhansoft. This popular report is only free in September!

Computer Software Update Status provides the overall software update compliant status for a PC. This information is useful to help ensure that your computers are up-to-date with all software updates.

Computer Software Update Status

Also, make sure to vote in our poll to help determine which report will become October’s free SSRS report!

September's free SSRS report has the Role-Based Administration (RBA) feature enabled, so if you are using System Center 2012 R2 Configuration Manager this report will work with all RBA clients! Don’t worry if you are not using SCCM 2012 R2 because you can still run this report as usual on SCCM 2012 and SCCM 2007.

How a “Not Equal To” Query is Processed

How a “Not Equal To” Query is Processed

By Garth Jones

Building on yesterday’s blog post about how a query is processed this post will show you how a query processes the Not Equal To operator.

As I explained in my last post, when a query is run it will process each row within a view or a table. Using the following table as our example, let’s look at the how the Not Equal To operator is processed in a query. This table is comprised of 4 PCs and their add/remove program (ARP) details.

PC Name

PC1

PC2

PC3

PC4

1

Adobe Reader XI

Microsoft Project 2010

Visio 12

Adobe Reader X

2

Visio 12

CorelDraw

ITunes

Microsoft Project 2010

3

ITunes

Office 2010

WordStar

DataStar

4

Microsoft Office 2013

 

 

Lotus123

5

 

 

 

Kix 2010


Don’t forget, we’re trying to find all of the PCs that do not have Microsoft Project 2010 installed.

1. Starting at PC1 the query will review each row to look for Microsoft Project 2010.
a. Row 1 is added to the result set since it is not equal to Microsoft Project 2010.
b. Row 2 is added to the result set since it is not equal to Microsoft Project 2010.
c. Row 3 is added to the result set since it is not equal to Microsoft Project 2010.
d. Row 4 is added to the result set since it is not equal to Microsoft Project 2010.

2. The process is repeated for PC2.
a. Row 2 is added to the result set since it is not equal to Microsoft Project 2010.
b. Row 3 is added to the result set since it is not equal to Microsoft Project 2010.

3. The process is repeated with PC3.
a. Row 1 is added to the result set since it is not equal to Microsoft Project 2010.
b. Row 2 is added to the result set since it is not equal to Microsoft Project 2010.
c. Row 3 is added to the result set since it is not equal to Microsoft Project 2010.

4. The process is repeated with PC4.
a. Row 1 is added to the result set since it is not equal to Microsoft Project 2010.
b. Row 3 is added to the result set since it is not equal to Microsoft Project 2010.
c. Row 4 is added to the result set since it is not equal to Microsoft Project 2010.
d. Row 5 is added to the result set since it is not equal to Microsoft Project 2010.

5. Finally the query will return PC1, PC2, PC3 and PC4 to the result set as each of them has a row that is Not Equal To Microsoft Project 2010.

As you can see above the Not Equal To operator will not give us the results that we are looking for, so in next week’s blog post I will show you the importance of using a subselect query.

How Queries Are Processed

By Garth Jones

A question that comes up from time to time is about how to locate PCs that do not have a specific type of software installed.

A common mistake is to think that you need to use either Not Equal To or Not Like within a query. However, by using these operators in your query the end result will show every PC within System Center Configuration Manager. I will explain why this is true, but let’s first talk about how queries are processed.

How Queries Are Processed

Before looking at queries that contain Not Equal To or Not Like we need to understand what Equal To or Like means in a query. As a general rule, when a query is run a query will process each row within a view or a table.

For our example we will query to find all PCs with Microsoft Project 2010. Using the following table, let’s see how this query with the Equal To operator is processed. This table is comprised of 4 PCs and their add/remove program (ARP) details.

PC Name

PC1

PC2

PC3

PC4

1

Adobe Reader XI

Microsoft Project 2010

Visio 12

Adobe Reader X

2

Visio 12

CorelDraw

ITunes

Microsoft Project 2010

3

ITunes

Office 2010

WordStar

DataStar

4

Microsoft Office 2013

   

Lotus123

5

     

Kix 2010

Remember, our main concern is to find all of the PCs with Microsoft Project 2010 installed.

1. Starting with PC1, the query will review each row to look for Microsoft Project 2010. The software is not found in any of PC1’s rows.

2. The process is repeated with PC2. This time Microsoft Project 2010 is found and PC2 is added to the result set.

3. The process is repeated with PC3. The software is not found in any rows for PC3.

4. The process is repeated with PC4. This time Microsoft Project 2010 is found in row 2 and PC4 is added to the result set.

5. Finally, the result set for the query will return PC2 and PC4.

Looking at the table above, this information is exactly what we expected to see in our result set.

Over my next five blog posts, I will explain how the Not Equal To query is processed and how to use a subselect query to locate PCs that do not have a specific type of software installed.

How a “Not Equal To” Query is Processed: http://be.enhansoft.com/post/2014/09/11/How-a-Not-Equal-To-Query-is-Processed.aspx
The Subselect Query: http://be.enhansoft.com/post/2014/09/17/The-Subselect-Query.aspx
Subselect Query for Reports (SQL): http://be.enhansoft.com/post/2014/09/18/Subselect-Query-for-Reports-(SQL).aspx

Remote Server Administration Tools (RSAT) for Windows 8.1

By Garth Jones

I don’t need to tell you that Configuration Manager administrators wear many hats and perform several tasks. One of those tasks is managing users and computers in Active Directory (AD). When I needed to perform this task, I noticed that Remote Server Administration Tools (RSAT) were not installed on my Windows 8.1 x64 PC, so this blog post will show you how I set-up RSAT. Hopefully, these steps will save you some time if you encounter a similar situation.

First, download RSAT 8.1 from Microsoft’s site to your source folder. http://www.microsoft.com/en-us/download/details.aspx?id=39296

RSAT-Create Package

Under Software Library, select Packages and then right click on Create Package.

RSAT-Package Details

Fill in the details for RSAT and click Next.

RSAT-Program Type

Select Standard program, and then click Next.

RSAT-Standard Program

Fill in the program Name and Command line. Select the Allow users to view and interact with the program installation check box, and then click Next.

RSAT-Requirements

Since this version of RSAT is only for Windows 8.1 x64, select the All Windows 8.1 (64-bit) option and also set the Estimated disk space to 70 MB and the run time to 20 minutes.

RSAT-Summary

Click Next.

RSAT-Completion

Click Close.

Treat this as you would any other deployment by distributing the content and deploying the advertisement to the appropriate collection. I would normally make this an Available Advertisement, so that I can pick and choose which PCs it will get installed on.

Help Choose Enhansoft’s Next Free SQL Server Reporting Services (SSRS) Report for the Month of October

The report choices in September’s poll are: Count of Office SKUs (Office 2010) OR Computer Sound Card Details.

VOTE HERE!

a) Count of Office SKUs (Office 2010) will show you all of the Microsoft Office 2010 products installed in your environment by stock-keeping unit (SKU).

Count of Office SKUs (Office 2010)

In the full-feature set, this report will drill through to List of PCs by Office SKU (Office 2010), which in turn will drill through to a further report, Microsoft Office 2010 Details (for a PC). 

b) The Computer Sound Card Details report will show you important details about sound devices installed on an individual PC. 

Computer Sound Card Details

In the full-feature set, this report is the final drill through report from List of PCs by Sound Device Manufacturer and List of PCs by Sound Device Status.

All of our reports have the Role-Based Administration (RBA) feature enabled. This means that if you are using System Center 2012 R2 Configuration Manager, these reports will work with all RBA clients. Don’t worry if you are not using SCCM 2012 R2 because you can still run these reports as usual.

Don’t forget to contact Info AT Enhansoft to request September’s free SSRS report, Computer Software Update Status.

If you would like to suggest future SSRS report give-aways, please email ewrwish At Enhansoft with your ideas.

September’s Free SSRS Report is Computer Software Update Status

Thank you to everyone who voted in last month’s poll. The winning report with 80% of the vote is Computer Software Update Status!

Get your copy today by emailing Info AT Enhansoft. This report is only free in September.

Computer Software Update Status provides the overall software update compliant status for a PC. This information is useful to help ensure that your computers are up-to-date with all software updates.

Computer Software Update Status

September’s free report has the Role-Based Administration (RBA) feature enabled, so if you are using System Center 2012 R2 Configuration Manager this report will work with all RBA clients! Don’t worry if you are not using SCCM 2012 R2 because you can still run this report as usual on SCCM 2012 and SCCM 2007.

Stay tuned because tomorrow we will preview the reports in this month’s poll.

Final Reminder for August’s Free SSRS Report–Computer Service Details

Computer Service Details is only available for free in August, so make sure to request your copy by sending an email to Info AT Enhansoft today!

The Computer Service Details report is useful when you need to quickly review the status of each service installed on a PC. For example, if you need to track down PCs that don’t have McAfee service installed, you can review the list of services that are installed on an individual PC to see if it has McAfee (or an alternate anti-virus software) before taking any action.

Computer Service Details

August's free SSRS report has the Role-Based Administration (RBA) feature enabled, so if you are using System Center 2012 R2 Configuration Manager this report will work with all RBA clients. Don’t worry if you are not using SCCM 2012 R2 because you can still run this report as usual on SCCM 2012 and SCCM 2007.

Also, make sure to vote in our poll to help decide which report will become September’s free report. We’ll announce the winner next week!

Last Reminder to Vote for September’s Free SSRS Report

You only have a few more days left to vote. The report choices are: Computer Software Update Status OR List of PCs by Manufacturer (System Enclosure). Currently, Computer Software Update Status has 81% of the vote. If you would like to see List of PCs by Manufacturer (System Enclosure) become September’s free report make sure to vote and get your friends and colleagues to vote too! 

VOTE HERE!

We’ll announce September’s free SSRS report next week.

Here is some information about each report to help you decide which one to vote for:

a) Computer Software Update Status provides the overall software update compliant status for a PC. This information is useful to help ensure that your computers are up-to-date with all software updates.

Computer Software Update Status

b) List of PCs by Manufacturer (System Enclosure) returns a list of PCs by a particular manufacturer. This handy report allows you to determine which users have a particular manufacturer, such as HP, within a collection. This information will help you replace or update these PCs as needed.

List of PCs by Manufacturer (System Enclosure)

In the full-feature set, this report will drill through to Computer System Enclosure Details.

All of our reports have the Role-Based Administration (RBA) feature enabled. This means that if you are using System Center 2012 R2 Configuration Manager, these reports will work with all RBA clients. Don’t worry if you are not using SCCM 2012 R2 because you can still run these reports as usual.

Don’t forget to contact Info AT Enhansoft to request August’s free SSRS report, Computer Service Details.

Suggest future SSRS report give-aways by emailing ewrwish At Enhansoft with your ideas.