SMS SQL for Last Discovery

The following SQL Query will return last discovery date for a specific computer. Code is based on the client health queries by Paul Thomsen.

-- Last discovery date

select Name0, AgentTime as 'Discovery'
from v_R_System sys full join v_RA_System_SMSAssignedSites ass ON ass.resourceID=SYS.resourceID full join (select ResourceId, MAX(AgentTime) as AgentTime from v_AgentDiscoveries where agentname<>'SMS Discovery Data Manager' AND agentname not like '%!_AD!_System%' ESCAPE'!' group by ResourceId) disc on disc.resourceid=sys.resourceid
where client0=1 --and IsNULL(AgentTime,@NullVal)>@olddate
and Name0 = 'computername'

Comments

Post a Comment

Popular posts from this blog

SMS "Program failed (run time exceeded)" 10070

When a program fails due to error 10070, it does so because the Run Time was Exceeded. SMS Allows a program to run for 12 hours, afterwhich it will issue the failed status message if the program hasn't completed successfully. Note: The Maximum allowed run time within program properties is generally a guide for users running the program themselves, and is not enforced.
Read more

SMS "Waiting for User Condition" 10036

Status Message ID 10036, or "Waiting for User Condition" can occasionally grind advertisements to a halt. Make sure the environment and user condition settings for a particular program have been configured appropriately and are not excluding a large portion of your intended audience. Related Articles SMS WQL for All Clients where Last Status is Waiting for User Condition SMS Status Message ID's
Read more

SMS "Waiting for content" 10035

Status Message ID 10035, or "Waiting for content" is a common error which can drive you up the wall when testing a package deployment. Have you checked your site boundaries? Often a client is located in a IP Subnet which hasn't been configured within Site or Roaming Boundaries. Has the software been deployed to the clients local DPs? Check the reports and console, make sure the package has been deployed and is available to the clients. Is the advertisement configured for download and execute? Check the DP, make sure BITS is enabled, and the web site is functioning properly. Have you checked the logs? Start with the cas.log and execmgr.log on the client computer. Related Articles Clients do not send a status message when an advertised program is disabled in Systems Management Server 2003 SMS Status Message ID's
Read more