Sure, they're all the same query essentially but with the -days number changed. After reading that the lastlogontimestamp may be out by 9-14 days I may go back and adjust the numbers a bit, though what I currently have we found quite well. What we find is that there will be a larger number of systems that have logged on in the last week, then the numbers go down going out a couple weeks, then go back up for systems that haven't been on in at least 42 days.
So the all missing clients last logged in between 7 and 14 days query is: 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 where SMS_R_System.LastLogonTimestamp < DateAdd(dd,-7,GetDate()) and SMS_R_System.LastLogonTimestamp >= DateAdd(dd,-14,GetDate()) These collections are then just limited to one collection for all missing clients (just workstations in this instance) Missing Clients Collection query: 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 where SMS_R_System.Client is null and SMS_R_System.OperatingSystemNameandVersion like "%Windows NT Workstation%" From: [email protected] [mailto:[email protected]] On Behalf Of Iacaruso, Mike Sent: Thursday, January 15, 2015 8:33 AM To: [email protected] Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query Jeff would you mind sharing the query string for those missing clients collections? Mike Iacaruso Enterprise Desktop Engineer Office of Technology Services Towson University 410-704-3965 [email protected]<mailto:[email protected]> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Wednesday, January 14, 2015 2:42 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query Ah I see where you were looking, in the column view for some reason it doesn't show the data for that attribute on active clients , but if you look at the properties of a client you'll see it listed in the discovery data. Kinda weird why they did it that way. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Iacaruso, Mike Sent: Wednesday, January 14, 2015 2:15 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query My fault! I am seeing Last Logon Timestamp in the query but in the collection this field on shows up for inactive clients. This might be what I need. Thanks for all of the help Jeff. Mike Iacaruso Enterprise Desktop Engineer Office of Technology Services Towson University 410-704-3965 [email protected]<mailto:[email protected]> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Wednesday, January 14, 2015 1:36 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query No, it shows up for all computers. There may be something off in your AD environment? I use this for the exact same reasons you're looking at. [cid:[email protected]] Btw, if you're using the client push install, check out the reports under Client Push, found this to be really useful for tracking down errors. Found machines where the install account didn't have rights on the machine. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Iacaruso, Mike Sent: Wednesday, January 14, 2015 1:23 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query Sorry, I should have the read the article better. The timestamp only shows up for inactive clients. This helps for the inactive systems but I also need systems without a client installed but is connected to the network. I have an issue where systems are not getting the SCCM client installed for whatever reason. These are valid systems that I want to see but the systems that do not have a client and are not connected to the network, these are the ones I need to filter out. I thought the best route was to use the password last set attribute. Mike Iacaruso Enterprise Desktop Engineer Office of Technology Services Towson University 410-704-3965 [email protected]<mailto:[email protected]> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Wednesday, January 14, 2015 1:13 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query Are you not seeing the info on the sccm side or from the AD side of things? If you lookup a PC in AD admin center and look at the properties in the attribute editor tag, is it blank there? LastLogonTimestame is one of the default attributes in system discovery. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Iacaruso, Mike Sent: Wednesday, January 14, 2015 1:07 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query I also found this article and my concern is that the majority of the systems do not have a date/time in Last Logon Timestamp. Mike Iacaruso Enterprise Desktop Engineer Office of Technology Services Towson University 410-704-3965 [email protected]<mailto:[email protected]> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Wednesday, January 14, 2015 12:46 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query You know, I just had a feeling I had to double check something and go figure learned something new. So the LastLogonTimeStamp is updated in real time, it will be within a range of 9-14 day behind the current date. It would still be more accurate than the last pwd change date as that could be a range of up to 30 days typically. Check out this blog post http://blogs.technet.com/b/askds/archive/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works.aspx<https://urldefense.proofpoint.com/v2/url?u=http-3A__blogs.technet.com_b_askds_archive_2009_04_15_the-2Dlastlogontimestamp-2Dattribute-2Dwhat-2Dit-2Dwas-2Ddesigned-2Dfor-2Dand-2Dhow-2Dit-2Dworks.aspx&d=AwMFAg&c=aLnS6P8Ng0zSNhCF04OWImQ_He2L69sNWG3PbxeyieE&r=pQGVi_ygWZb0EWR_EeMFzgKJCQ8AFTQI7Ck6iiIPItI&m=46s_Xil3AZeCYyAQN20olnh_9lnBkplY6kcK2kqW97M&s=BFlMyJEbG0jUDhayojKufvR4GuveYiZc13tWsoGjyhk&e=> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Wednesday, January 14, 2015 12:27 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query Actually it is the last time the machine itself authenticated to AD as captured during the AD system discovery, so depending on the discovery schedule it may be out by a week if your discovery only runs weekly, but the same would apply to the any attribute collected by the discovery. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Iacaruso, Mike Sent: Wednesday, January 14, 2015 12:13 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query Jeff this attribute is equivalent to the password last set? Mike Iacaruso Enterprise Desktop Engineer Office of Technology Services Towson University 410-704-3965 [email protected]<mailto:[email protected]> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Wednesday, January 14, 2015 11:16 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Create SCCM 2012 collection based on a SQL query I've created several collections based on the last logon time attribute, that would probably be better than last password set. Try this: 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 where SMS_R_System.LastLogonTimestamp >= DateAdd(dd,-7,GetDate()) From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Iacaruso, Mike Sent: Wednesday, January 14, 2015 11:10 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] Create SCCM 2012 collection based on a SQL query I need to use the AD attribute pwdLastSet in SCCM to filter out stale systems. I added the attribute to the AD system discovery, but I need to convert pwdlastset to a date readable format. I need to find systems that are older than 45 days. I have found that I cannot convert this using WQL and have used SQL to convert this but I need to create a collection with these results. I need this collection as part of a report for management. Is there a way to create a collection using this SQL query? Is there a better way to accomplish this? Thanks Mike Iacaruso Enterprise Desktop Engineer Office of Technology Services Towson University 410-704-3965 [email protected]<mailto:[email protected]> ________________________________ CONFIDENTIALITY NOTICE: This email contains information from the sender that may be CONFIDENTIAL, LEGALLY PRIVILEGED, PROPRIETARY or otherwise protected from disclosure. This email is intended for use only by the person or entity to whom it is addressed. If you are not the intended recipient, any use, disclosure, copying, distribution, printing, or any action taken in reliance on the contents of this email, is strictly prohibited. If you received this email in error, please contact the sending party by reply email, delete the email from your computer system and shred any paper copies. Note to Patients: There are a number of risks you should consider before using e-mail to communicate with us. See our Privacy & Security page on www.henryford.com<http://www.henryford.com> for more detailed information as well as information concerning MyChart, our new patient portal. If you do not believe that our policy gives you the privacy and security protection you need, do not send e-mail or Internet communications to us.
