Query to list all the systems that never ran an advertisement successfully

Here is a query that can be used in SCCM report to list all the systems that never an advertisement successfully.


select distinct count(v_ClientAdvertisementStatus.resourceid) as [Number of Adverts],sys.Active0,sys.Client0,sys.Client_Version0,sys.Netbios_Name0,
sys.Operating_System_Name_and0,sys.Obsolete0,
sys.Resource_Domain_OR_Workgr0,sys.User_Domain0,
sys.User_Name0  from v_R_System sys right join v_ClientAdvertisementStatus on sys.resourceid=v_ClientAdvertisementStatus.resourceid where sys.ResourceID not in (select  distinct ResourceID from v_ClientAdvertisementStatus where v_ClientAdvertisementStatus.LastStateName = 'Succeeded') group by sys.Active0,sys.Client0,sys.Client_Version0,
sys.Netbios_Name0,sys.Operating_System_Name_and0,
sys.Obsolete0,sys.Resource_Domain_OR_Workgr0,
sys.User_Domain0,sys.User_Name0

Query to list the hardware and software inventory dates of all the SCCM clients

SELECT DISTINCT SYS.Netbios_Name0, SYS.Operating_System_Name_and0,
  HWSCAN.LastHWScan, SWSCAN.LastScanDate, SWSCAN.LastCollectedFileScanDate 
FROM v_R_System SYS
LEFT JOIN v_GS_LastSoftwareScan SWSCAN on SYS.ResourceID = SWSCAN.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID

WQL Collection queries to list Vista, Windows 7, Windows 2008 and Windows 2008 R2 systems

All Windows Vista Systems
Select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.0%"

All Windows 7 systems

select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.1%"

All Windows 2008 Systems

Select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Server 6.0%"

All Windows 2008 R2 Systems

select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Server 6.1%"

Re-running advertertisement on Failed machine

Under most circumstances, a Microsoft Systems Management Server (SMS) client does not re-run an advertisement for a package and program that was previously attempted. This article describes how to force clients to run an advertisement, even if the client has previously run the same package and program.

Back to the top

MORE INFORMATION

By default, SMS clients do not re-run advertised programs unless the advertiseme...

By default, SMS clients do not re-run advertised programs unless the advertisement is on a recurring schedule. Because it is possible for clients to receive multiple advertisements or assignments for the same package/program, it is not recommended that the client install the same package/program more than once. For this reason, SMS uses the package/program combination to determine whether or not a package has been previously run.

However, there may be situations when you need to have certain clients re-run a previously advertised program (for example, if you advertise a program to 20 clients and only 10 clients run it successfully). In this example, if you want to force all 20 clients to run the advertisement again, add a second time-based assignment to the existing advertisement. Do not delete the first assignment; the second assignment causes the advertisement to be treated by clients as a recurring assignment.

NOTE: Event-based assignments do not support rescheduling. Recurring assignments are always run on their schedule regardless of any previously run status. This forces all 20 clients to re-run the advertisement on the second schedule. Any clients added to this collection should only run the second assignment (not both), unless both assignment times to that client are scheduled in the future. Recurring assignments are run by clients when they are received, so the client only runs the last scheduled assignment and does not run the advertisement again unless it is scheduled again.

If you want to use only the 10 clients that did not successfully run the advertisement the first time, create an additional assigned advertisement that targets the same collection. This creates two assignments from two separate advertisements. In this situation, clients run the second assignment if the previous assignment was unsuccessful and the second advertisement's schedule occur after the previously unsuccessful attempt.

Another alternative is to create either a new package\program or a new program for the existing package, and advertise it to the client. Even if the client has successfully installed the same software previously, it runs because the package and/or program ID is now different.

If you have to rerun the advert on the 10 systems where it was failed, remove old assignment of advert and add another assignment.

Situation 1:     To rerun the advert on failed systems (10 nos) only out of 20.

Solution:    Remove the Mandatory assignment from Advertisement, add another schedule of mandatory assignment into it. Click OK.

The replaced assignment will cause an advert to run only on Failed systems not the succeeded ones.

Situation 2:    To rerun the advert on all systems irrespective of success rate.

Solution:         By keeping the existing mandatory assignment into advert create an additional assignment so here you will see 2 schedules assignment into an advert.

The second assignment causes the advertisement to be treated by clients as a recurring assignment and will run on all the systems irrespective of success earlier.

Create a collection to list all the systems with a particular KB

Use the following queries to build the collection to list all the systems with a particular KB.

For finding x86 systems:

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%KB123456%"

For finding x64 systems:

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_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB123456%" 

 

Replace KB123456 with the KB number of require security update.

SCCM SQL Query to list all the systems with their BIOS details

SELECT sys.Name0, BIOS.SMBIOSBIOSVersion0,BIOS.ReleaseDate0, BIOS.Manufacturer0
FROM v_r_system sys, v_GS_PC_BIOS BIOS
WHERE sys.ResourceID = BIOS.ResourceID

SCCM Report for 2 softwares registered in Add/Remove Programs

This report creates the output in the following format.

Computer 1	Software 1	Software 2
Computer 2	Software 1	Software 2
Computer 3	Software 1	Software 2
Computer 4	Software 1
Computer 5	Software 1	Software 2
Computer 6	Software 1	Software 2
Computer 7		Software 2
Computer 8	Software 1	Software 2

Query:

select sys.name0,(select distinct 'Software ARP1' from v_Add_Remove_Programs ARP where (ARP.ResourceID=SYS.ResourceID) and exists (select ARP.DisplayName0 where ARP.DisplayName0='Software ARP1') ),(select distinct 'Software ARP2' from v_Add_Remove_Programs ARP where (ARP.ResourceID=SYS.ResourceID) and exists (select ARP.DisplayName0 where ARP.DisplayName0='Software ARP2') )  from v_r_system sys inner join  v_FullCollectionMembership on sys.ResourceID=v_FullCollectionMembership.ResourceID where  v_FullCollectionMembership.CollectionID=@CollID

Prompt Query: Create a prompt with the variable CollID using the following query.

select CollectionID,Name from v_Collection

Here, replace Software ARP1 and Software ARP2 with your application add/remove programs name.