NEW Query: Machines that have multiple versions of the same application installed

[SJimenez]

I'm having trouble creating a query to show machines that have multiple versions of the same application installed on it.

This is what I've tried.

select SMS_R_System.ResourceId
from SMS_R_System
where SMS_R_System.ResourceId in
(select Count(ARPDisplayNameSMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName) from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "Workspot Client%" and Count(SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName) >1)

When trying to run the previewer in the device collection I get the error:

"ConfigMgr Error Object:
instance of SMS_ExtendedStatus
{
Description = "Failed to parse WQL string select SMS_R_System.ResourceId
\nfrom SMS_R_System
\nwhere SMS_R_System.ResourceId in
\n (select Count(ARPDisplayNameSMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName) from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like \"Workspot Client%\" and Count(SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName) >1)";
ErrorCode = 1078464256;
File = "..\\sspobjectquery.cpp";
Line = 1807;
ObjectInfo = "select SMS_R_System.ResourceId
\nfrom SMS_R_System
\nwhere SMS_R_System.ResourceId in
\n (select Count(ARPDisplayNameSMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName) from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like \"Workspot Client%\" and Count(SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName) >1)";
Operation = "ExecQuery";
ParameterInfo = "select SMS_R_System.ResourceId
\nfrom SMS_R_System
\nwhere SMS_R_System.ResourceId in
\n (select Count(ARPDisplayNameSMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName) from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like \"Workspot Client%\" and Count(SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName) >1)";
ProviderName = "WinMgmt";
StatusCode = 2147749889;
};

-------------------------------
Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlQueryException
The SMS Provider reported an error.


Stack Trace:
at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlQueryResultsObject.<GetEnumerator>d__75.MoveNext()
at Microsoft.ConfigurationManagement.ManagementProvider.QueryProcessorBase.ProcessQuery(Object sender, DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

-------------------------------

System.Management.ManagementException
Generic failure


Stack Trace:
at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlQueryResultsObject.<GetEnumerator>d__75.MoveNext()
at Microsoft.ConfigurationManagement.ManagementProvider.QueryProcessorBase.ProcessQuery(Object sender, DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

-------------------------------"

 

[Garth]

Count is not a valid WQL function. There isn't going to be a simple way to do this using collections. At best you can create a collection with the Current version, Then create a collection with any version and excluded those computers from the first collection.