Jump to content


Sign in to follow this  
cantgetanythingtowork

WQL error when trying to add 'where'

Recommended Posts

Hello.

I'm having trouble making up a query for software metering. The query was adopted from some site and then modified to suit my purposes. I am however having problems adding a simple where-clause to this query.

 

The query is as follows:

Select SMS_R_System.ResourceID,SMS_R_System.Name,SMS_R_User.UniqueUserName
FROM SMS_R_System
JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName
JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName
INNER JOIN SMS_G_System_SoftwareFile ON SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceID
INNER JOIN SMS_MeteredFiles ON SMS_G_System_SoftwareFile.FileID = SMS_MeteredFiles.MeteredFileID
INNER JOIN SMS_MeteredProductRule ON SMS_MeteredProductRule.RuleID = SMS_MeteredFiles.RuleID
	WHERE SMS_MeteredFiles.RuleID = 16777317 AND DateDiff(day, SMS_G_System_SoftwareFile.ModifiedDate, GetDate()) > 1 AND DateDiff(day, SMS_MeteredProductRule.LastUpdateTime, GetDate()) > 1 AND SMS_R_system.OperatingSystemNameAndVersion LIKE '%Workstation%' AND SMS_G_system_SoftwareFile.ResourceID NOT IN (SELECT DISTINCT SMS_MonthlyUsageSummary.ResourceID FROM SMS_MonthlyUsageSummary INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFile.MeteredFileID WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 2 AND SMS_MeteredFiles.RuleID = 16777317)

This works fine for the purpose, but I want to incorporate device affinity along with it. And to do that, I want to add 'WHERE SMS_UserMachineRelationship.RelationActive = '1'' somewhere but I don't know where. All my guesses have resulted in invalid query.

 

This is for user collection. This was relatively easy to do for device deployments but I'm having hard time getting it to work for when a deployment is for user - and this is essential because the install deployment collection membership must first be deleted before the uninstall can be done.

Share this post


Link to post
Share on other sites


Sorry if I caused confusion :)

 

I'm trying to do a user query.

The query above returns all users who have logged on to a workstation where the software metering rule matches. It also returns devices but that is of no consequence now since I only need the user for the final result.

However, only one of those relationships is active and thus I want the where-clause to get a singular username who I can then remove from the application deployment collection and deploy application uninstall.

The applications are deployed initially with requirement that the device it is installed on is users primary device.

 

I am trying to achieve software-metering-uninstallation-automation for deployments that are user-targeted rather than device targeted.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...