Enhansoft

Enhansoft Support and Announcement Blog

October’s Free SSRS Report is Count of Office SKUs (Office 2010)

Thank you to everyone who voted in September’s poll! With 92% of the vote Count of Office SKUs (Office 2010) becomes October’s free SSRS report.

Email Info AT Enhansoft for your copy today! This report is only free in October.

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

October'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.

Count of Office SKUs (Office 2010)

Stay tuned because tomorrow we will preview the reports in October’s poll!

Last Day to Request September’s Free SSRS Report - Computer Software Update Status

Get your copy of Computer Software Update Status today by emailing Info AT Enhansoft. This popular report is only free in September and today is the last day of 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! We’ll announce October’s free report tomorrow.

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.

Last Reminder to Vote for October’s Free SSRS Report

The report choices in this month’s poll are: Count of Office SKUs (Office 2010) OR Computer Sound Card Details. Right now, Count of Office SKUs (Office 2010) has 93% of the vote. If you would like to see Computer Sound Card Details become October’s free SSRS report make sure to vote, and get your friends and colleagues to vote today!

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, you only have until tomorrow 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.

Subselect WQL Query to Find PCs That Do Not Have Either x86 or x64 Versions of Software Installed

By Garth Jones

In my blog post series about how queries are processed, I showed you yesterday how to create a subselect WQL query to find all PCs without Microsoft Project 2010 installed. As promised at the end of that post, I will show you how to query for either x86 or x64 versions of a software title.

If you would like to read more about how queries are processed, here is a link to the first post in this 6-part series.

Subselect WQL Query to Find PCs That Do Not Have Either x86 or x64 Versions of Software Installed

Again, I will use Microsoft Project 2010 for my example. The key element to this query is knowing that x86 and x64 software titles are found in different WQL views.

These views are:

X86

SMS_G_System_ADD_REMOVE_PROGRAMS

 

X64 
SMS_G_System_ADD_REMOVE_PROGRAMS_64 
 

The positive query should look similar to this example:

Select distinct

  SMS_R_System.ResourceId

From 

  SMS_R_System

Where

  SMS_R_System.ResourceId in

     (

        Select

          SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID

        From

          SMS_G_System_ADD_REMOVE_PROGRAMS

        Where

          SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Project 2010"

      )

  or SMS_R_System.ResourceId in

     ( 

        Select

            SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID

          From

            SMS_G_System_ADD_REMOVE_PROGRAMS_64

          Where

            SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Project 2010"

      )

 

The subselect query combines the above positive query with a negative query:

 

Select distinct

                SMS_R_System.Name

From 

                SMS_R_System 

Where

                SMS_R_System.ResourceId not in

                (

                                Select

                                                SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID

                                From

                                                SMS_G_System_ADD_REMOVE_PROGRAMS

                                Where

                                                SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Project 2010"

                )

                And SMS_R_System.ResourceId not in

                ( 

                                Select

                                                SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID

                                From

                                                SMS_G_System_ADD_REMOVE_PROGRAMS_64

                                Where

                                                SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Project 2010"

                )

 

Having one query to find either x86 or x64 software titles is useful when creating a collection of all PCs that do not have a specific software title installed. This collection can be used to install software without having to worry if the PC is x86 or x64. This query can also be helpful when the software display name changes between x86 and x64.

This is my last blog post in this series. I welcome your comments, so send them to my twitter handle @GarthMJ.

Subselect Query for SCCM Queries or Collections (WQL)

By Garth Jones

Today’s blog post will show you how to create a subselect query for WQL, and we will find which PCs do not have Microsoft Project 2010 installed. For more information about how queries are processed, please start from the first blog post in this series.

Subselect Query for SCCM Queries or Collections (WQL)

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 WQL views.

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

Select distinct  

                SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID

From

SMS_G_System_ADD_REMOVE_PROGRAMS

Where

SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Project 2010" 

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

Select

                SMS_R_System.Name

From 

                SMS_R_System

Where

                SMS_R_System.ResourceId not in

                                (

                                )


Now here is the subselect query which combines the above positive query with the negative query:

Select

                SMS_R_System.Name

From 

                SMS_R_System

Where

                SMS_R_System.ResourceId not in

                                (

                                                Select distinct 

                                                                SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID

                                                From

                                                                SMS_G_System_ADD_REMOVE_PROGRAMS

                                                Where

                                                                SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Project 2010"

                                )


 

In tomorrow’s blog post, I will show you how to write one WQL query in order to find PCs that have either x86 or x64 versions of Microsoft Project 2010 installed.

I’m sure that you can see how useful the subselect query can be for targeting PCs that do not have a specific software title installed.

Reminder to 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. Right now, Count of Office SKUs (Office 2010) is ahead with 90% of the vote. If you would like to see Computer Sound Card Details become October’s free SSRS report make sure to vote today!

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.

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.