If you are looking for a collection to query all the machines only from a particular OU not the SUB OU .Let say ,I have Top OU called Workstations\comp(120 systems) in eskon.net domain and in turn it has 3 sub OU's OU1(10 systems),OU2(20 systems),OU3(49 systems). Here is the simple query to get machines only from TOP OU called workstations. 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.systemouname = "eskon.net/workstations/comp" /*find all machines in that OU (but also finds machines in subOUs */ AND sms_r_system.resourceID NOT IN (SELECT resourceID FROM sms_r_system where systemouname LIKE "eskon.net/workstations/comp/%") /*remove…
Author: Eswar Koneti
Are you looking for getting a report for a given KB numbers or MSID(like MS10-087),you can add few more lines to the existing code as like below and change the ID1 to ID2 also add a prompt to it.The same report is also applicable in SCCM but it doesn't give some columns though it has required information. SMS 2003 Patch Status report for given list of Specific MS ID, Q Number, title: select summ.Product, summ.LocaleID, summ.Language, COUNT(distinct case when ps.LastState=107 or ps.LastState=105 then ps.ResourceID else NULL end) as 'Distribution Successful', COUNT(distinct case when ps.LastState=102 then ps.ResourceID else NULL end) as…
I was searching for something to find the software update complaince to see if machines requires something to patch .as i couldnt find any such report from default reports ,so created one like below and gives me the compelet complaicne of software updates. SQL Statement select distinct sys.Name0, ui.BulletinID as BulletinID, ui.ArticleID as ArticleID, ui.Title as Title from v_UpdateComplianceStatus css join v_UpdateInfo ui on ui.CI_ID=css.CI_ID join v_R_System sys on css.ResourceID=sys.ResourceID join v_ClientCollectionMembers ccm on ccm.ResourceID=sys.ResourceID where css.Status=2 and ccm.CollectionID=@CollID order by sys.Name0, ui.ArticleID Prompts Name: CollID Prompt text: Collection ID Provide a SQL statement: begin if…
In my daily work routine,i used to work with some of the web report to present it to management as well for troubleshooting purpose.I come across with many reports that are required.some reports that have taken from the internet. Here i am listing all of the reports that are useful and will be posting more and more. Report for list of machines where static IP exists : select CS.Name0, NAC.IPAddress0 from dbo.v_GS_COMPUTER_SYSTEM cs JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on CS.ResourceId = NAC.ResourceId Where NAC.IPAddress0 !='' and DHCPEnabled0 = 0 Report for list of machines where and When RAM changed ? select CS.Name0,…
The below scripts works for only Win 2000 and XP not for other operating systems .Here are the simple steps that you can achive this(getting a report for local administrators).*******test it once before taking it to production***************** 1.Go to the X:\smsinboxes\clifiles.src\hinv\sms_def.mof where X is SMS installed Drive,edit SMS_DEF.MOF file (If you have already edited it ,not changes are required .If you are doing it in SCCM environment for the first time, u can still do the same in SMS_DEF.MOF file.) 2.At the very bottom ,Add these lines to the MOF file . //***********************************Local admins*********************** //#pragma namespace ("\\\\.\\root\\cimv2\\sms") [ SMS_Report …
Here is the collection that gives you a list of machines with specific staus message ID: select sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client from sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID WHERE AdvertisementID = 'GSA2035F' and LastStatusMessageID = 10009 10009 is success, replace advertisementID with your advertisement ID. With specific State name: SELECT sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client FROM sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID WHERE AdvertisementID = 'KBS00289' and LastStateName = "Failed" Below are the list of status messages with the discription: SMS Status Message ID's. 10050 Cache too small 10061 Download failed (retrying) 10003 Failed (bad environment) 0 No messages have…
Class security Level Vs Instance Level Security Rights You can configure the security rights either the class level or the instance level in SMS/SCCM. These rights basically helpful in providing the access to the SMS objects like collection,packages,reports and many more too! class security rights: It applies to the entire class like SMS site.Let say if you have collection called "All windows systems" and inturn it has child collection "All XP","All Vista" etc.If you apply class security rights on"All windows systems" it automatically applies to child collections. Instance security rights:The name itself gives the answer,it is applied to an instance.If…
some times its hard to know the systems which are having issues in reporting their inventory and even some systems doesnt reporte anything to the site server though the client is installled on it. Here is the collection which gives you all computers which doesnt report inventory for x days(taken 15days). 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 ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) > 25) or ResourceId not in (select ResourceID from SMS_G_System_WORKSTATION_STATUS) Good luck in troubleshooting why it doesnt report 🙂 Hints: solution that i tried got worked. 1.check if you…