I just realized I had a subselect query in there for no reason. This one is probably better
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_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1 and DATEDIFF(dd,SMS_G_System_CH_ClientSummary.ADLastLogonTime,GetDate()) <=1 From: [email protected] [mailto:[email protected]] On Behalf Of Beardsley, James Sent: Tuesday, February 17, 2015 12:10 PM To: [email protected] Subject: [mssms] RE: Collection of machines online is last 1day? Here is one I just put together. Its all clients that are active and their last last logon (according to client check) is within 1 day. There might be a better attribute to key off of rather than using the ClientSummary.ADLastLogonTime but it might get you pointed in the right direction. 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_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1 and ResourceID in (select ResourceID from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_CH_ClientSummary.ADLastLogonTime,GetDate()) <=1) From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Matt Browne Sent: Tuesday, February 17, 2015 10:59 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Collection of machines online is last 1day? OK, Thanks. I was looking to create a collection (if anyone knows how to do that). From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: 17 February 2015 14:49 To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Collection of machines online is last 1day? It is a report. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Matt Browne Sent: Tuesday, February 17, 2015 8:14 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Collection of machines online is last 1day? Yes, thanks Mathieu. Is that a report query or a collection query? From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Nabil AIT HADDAD Sent: 17 February 2015 11:54 To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Collection of machines online is last 1day? Thanks Mathieu for your sharing ! Merci From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of LEROY Mathieu Sent: mardi 17 février 2015 12:14 To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Collection of machines online is last 1day? I’m using this one to regroupe active clients SELECT S.Name0 as 'Computer Name', S.User_Name0 as 'User Name',Case when CS.ClientActiveStatus='1' then 'Active' When CS.ClientActiveStatus='0' then 'Inactive' end as 'Client Active Status' FROM v_R_System S inner Join v_CH_ClientSummary CS on S.ResourceId=CS.ResourceID I guess you can tweak it a little to get what you want Cordialement, Mathieu Leroy Consultant T : +33 3 28 02 53 57 ― M : +33 6 21 63 74 15 ― F : +33 3 28 02 53 06 24 rue Papin - BP 50488 - 59664 Villeneuve d'Ascq Cedex email : [email protected]― web : www.osiatis.com<http://www.osiatis.com/> [http://m.c.lnkd.licdn.com/media/p/5/005/03c/191/1260f51.png][Description : Description : Description : cid:[email protected]] De : [email protected]<mailto:[email protected]> [mailto:[email protected]] De la part de Matt Browne Envoyé : mardi 17 février 2015 11:15 À : [email protected]<mailto:[email protected]> Objet : [mssms] Collection of machines online is last 1day? Does anyone have an easy way of creating a collection of machines that have been online in the last 1 day? I’m trying to use “v_CH_ClientSummary.LastOnline” but this doesn’t appear to be visible to collection queries. Does anyone know another good way to do this? Thanks Matt Browne ________________________________ Information in this message is sent in confidence and is intended only for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, any use, distribution or copying of the information is strictly forbidden. Please notify the sender immediately by return email or telephone 01823 721400. If you received this email in error please delete it and any copies of it from your system. Viridor Waste Management Limited Registered Office: Peninsula House, Rydon Lane, Exeter EX2 7HR Registered in England No. 575069 ________________________________ ________________________________ Information in this message is sent in confidence and is intended only for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, any use, distribution or copying of the information is strictly forbidden. Please notify the sender immediately by return email or telephone 01823 721400. If you received this email in error please delete it and any copies of it from your system. Viridor Waste Management Limited Registered Office: Peninsula House, Rydon Lane, Exeter EX2 7HR Registered in England No. 575069 ________________________________ ________________________________ Information in this message is sent in confidence and is intended only for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, any use, distribution or copying of the information is strictly forbidden. Please notify the sender immediately by return email or telephone 01823 721400. If you received this email in error please delete it and any copies of it from your system. Viridor Waste Management Limited Registered Office: Peninsula House, Rydon Lane, Exeter EX2 7HR Registered in England No. 575069 ________________________________ ________________________________ Confidentiality Notice: This e-mail is intended only for the addressee named above. It contains information that is privileged, confidential or otherwise protected from use and disclosure. If you are not the intended recipient, you are hereby notified that any review, disclosure, copying, or dissemination of this transmission, or taking of any action in reliance on its contents, or other use is strictly prohibited. If you have received this transmission in error, please reply to the sender listed above immediately and permanently delete this message from your inbox. Thank you for your cooperation. ________________________________ Confidentiality Notice: This e-mail is intended only for the addressee named above. It contains information that is privileged, confidential or otherwise protected from use and disclosure. If you are not the intended recipient, you are hereby notified that any review, disclosure, copying, or dissemination of this transmission, or taking of any action in reliance on its contents, or other use is strictly prohibited. If you have received this transmission in error, please reply to the sender listed above immediately and permanently delete this message from your inbox. Thank you for your cooperation.
![http://m.c.lnkd.licdn.com/media/p/5/005/03c/191/1260f51.png][Description :](http://m.c.lnkd.licdn.com/media/p/5/005/03c/191/1260f51.png][Description :){kind=link}