2 Replies - 1500 Views - Last Post: 21 March 2014 - 05:21 AM Rate Topic: ***-- 2 Votes

#1 Sylvain_  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 04-August 13

WQL to get systems where last logon user is particular value for attr

Posted 21 March 2014 - 02:20 AM

WQL for getting systems where last logon user is particular value for 'office' attribute

I need to make a membership rule for a device collection in System Center Configuration Manager 2012 R2, using WQL. I want to get all the devices where the last logon user has a given value for the 'office' AD attribute. I'm stuck.

There is another query I also can't get to work, though I don't understand why not. This query has to get all the systems where the bios date is under 30/09/2008. Here is what i tried.

SELECT SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, 
SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, 
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client

from SMS_R_System inner join SMS_G_System_PC_BIOS 
on SMS_G_System_PC_BIOS.ResourceId = SMS_R_System.ResourceId

where SMS_G_System_PC_BIOS.ReleaseDate <= "30/09/2008 0:00:00"


Is This A Good Question/Topic? 0
  • +

Replies To: WQL to get systems where last logon user is particular value for attr

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: WQL to get systems where last logon user is particular value for attr

Posted 21 March 2014 - 04:03 AM

View PostSylvain_, on 21 March 2014 - 02:20 AM, said:

There is another query I also can't get to work, though I don't understand why not. This query has to get all the systems where the bios date is under 30/09/2008. Here is what i tried.

SELECT SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, 
SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, 
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client

from SMS_R_System inner join SMS_G_System_PC_BIOS 
on SMS_G_System_PC_BIOS.ResourceId = SMS_R_System.ResourceId

where SMS_G_System_PC_BIOS.ReleaseDate <= "30/09/2008 0:00:00"

That's becuase may be you filter the ReleaseDate Less than "30/09/2008"
where SMS_G_System_PC_BIOS.ReleaseDate <= "30/09/2008 0:00:00"

if you cnange it i think it will work

and for the first query you need to show what you are doing in your part please read the forum rules

This post has been edited by thava: 21 March 2014 - 04:03 AM

Was This Post Helpful? 0
  • +
  • -

#3 Sylvain_  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 04-August 13

Re: WQL to get systems where last logon user is particular value for attr

Posted 21 March 2014 - 05:21 AM

[/quote]
That's becuase may be you filter the ReleaseDate Less than "30/09/2008"
where SMS_G_System_PC_BIOS.ReleaseDate <= "30/09/2008 0:00:00"

if you cnange it i think it will work

and for the first query you need to show what you are doing in your part please read the forum rules
[/quote]

I fixed this query, the date format in the query I posted was the output from the SCCM Membership Rules Wizard, which gives a wrong datetime format for some reason. It has to be "2008-09-30 00:00:00.000" (went looking in the DB).

For the other query I'm really not sure how to do this. The best I can do is get the last logon user from a computer with this query I made:

select * from SMS_R_System inner join
SMS_G_System_COMPUTER_SYSTEM on
SMS_G_System_COMPUTER_SYSTEM.ResourceId =
SMS_R_System.ResourceId where
SMS_G_System_COMPUTER_SYSTEM.UserName = "INTRANET\\svanste"


But what i would like to accomplish, is to get the computers where the ActiveDirectory 'office'-attribute from the last logonuser is a particular value.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1