You might have different operating Systems installed on the computers.One fine day your boss might request you to get list of computer with Operating system and language installed on the computers.Here is the Report to list all the computers with this information. select distinct a.name0,c.Caption0 as 'Operating System',c.CSDVersion0 as Version,b.SystemDefaultLCID as Language from v_R_System a, v_GS_WORKSTATION_STATUS b,v_GS_OPERATING_SYSTEM c where a.ResourceID=b.ResourceID and a.ResourceID=c.ResourceID group by a.name0,b.SystemDefaultLCID,c.Caption0,c.CSDVersion0 Here is the link to refer for Locale IDs, Input Locales, and Language Collections for Windows XP and Windows Server 2003 http://msdn.microsoft.com/en-us/goglobal/bb895996.aspx
Author: Eswar Koneti
2 weeks back,working on patching issues in SMS 2003 which are having some trouble.there are lot of systems which failed the patches due to windows update agent doesnt exisit on the machines(identified the cause because of this) more information look here .It is bit hard to find the systems by looking at resource explorer and finding if it has latest windows update agent or not ? so just create a collection and advertise the package( windows update agent) on to this collection to get the good success rate for patching. select ResourceId, ResourceType, Name, SMSUniqueIdentifier, ResourceDomainORWorkgroup, Client from SMS_R_System where ResourceId not in…
In my last post http://eskonr.com/2010/01/sccm-report-for-applications-installled-on-computers-with-without/ showed how to create sccm report to list computers with different versions of application installed on computers. This is to list the computers in collection rather report. Create a collection and edit the query ,paste the following into it. select * from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Office1%" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Office2%" Please replace the bold letters with the correct application name displayed from add and remove programs.
All advertisements with specific status : This report gives you all percentage count with specific status for all advertisement in your environment. In order to get the list of machines where it is advertised with specific status,this report has to be linked to default report i.e 106.It works with SCCM , if you want to run the report in SMS 2003 ,see below after this code. SELECT AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS 'Number of clients with this Status', ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS 'Percent with this Status', SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1…
Here are the list of windows update error codes http://inetexplorer.mvps.org/archive/windows_update_codes.htm
if you want find out the computers that doesnt have particualr software installed in,you may write query where applications!=adobe which will not return the correct result as you would expect. you will have to use not in condition to get the correct result. here is you go: Select Distinct sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0 FROM v_R_System sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID WHERE sys.ResourceID not in (select sys.ResourceID from v_R_System sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID where DisplayName0 = @displayname) Prompt for Displayname: select DisplayName0 from v_Add_Remove_Programs
SELECT c.Name0, a.DisplayName0 FROM v_GS_ADD_REMOVE_PROGRAMS a, v_R_System c WHERE a.ResourceID = c.ResourceID AND a.DisplayName0 like ‘%Adobe acrobat%‘ GROUP BY c.Name0, a.DisplayName0 ORDER BY c.name0
In this blog post ,we will talk about how to get list of all applications installed on specific computer or at collection level excluding software updates . Running the report on collection level will be huge report because each client will have X number of applications and if there are 100 clients in collection ,it will be 100*X will be huge list of excel spread sheet. You can also customise to add other sec updates which you dont want them in report by adding AND A.DisplayName0 NOT LIKE "name" at the end of report. SELECT A.DisplayName0,A.InstallDate0 A.Version0,A.Publisher0 FROM v_GS_ADD_REMOVE_PROGRAMS A,…