SCCM SQL Query : Find Lync Versions

select  distinct v_R_System.User_Name0,v_r_system.Name0,v_R_System.Active0,v_Add_Remove_Programs.DisplayName0,v_Add_Remove_Programs.TimeStamp,v_Add_Remove_Programs.InstallDate0,v_Add_Remove_Programs.Version0from v_Add_Remove_Programsinner join v_R_System on v_Add_Remove_Programs.ResourceID = v_R_System.ResourceIDwhere v_Add_Remove_Programs.DisplayName0 like 'Microsoft Lync 2010'

Advertisements

SCCM SQL Query : Computers with Pending Restart or other Update Enforcement States

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_UpdateComplianceStatus ON SMS_UpdateComplianceStatus.machineid=sms_r_system.resourceid WHERE SMS_UpdateComplianceStatus.LastEnforcementMessageID = 9There are several other enforcement states you can use instead by changing the number after ‘LastEnforcementMessage ID =’ at the end of the query.1 – Enforcement started3 – Waiting for another installation to complete6 – General failure8 – … Continue reading SCCM SQL Query : Computers with Pending Restart or other Update Enforcement States

SCCM SQL Query : Query to find package pending replication

selectpkg.Name as C062,pkg.PackageID,SUBSTRING(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath) + 2, CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath) - 3 ) AS C070, dp.SiteCode,dp.LastRefreshTime,stat.SourceVersion,stat.LastCopied,stat.SummaryDate,stat.InstallStatus,case when dp.IsPeerDP=1 then '*' else '' end as BranchDPfrom v_Package pkgjoin v_DistributionPoint dp on pkg.PackageID=dp.PackageIDjoin v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPathand dp.PackageID=stat.PackageIDwhere stat.State!=0 and DP.SiteCode in ('sitecode')

1E Shopping Database : Query for applications and their categories in Shopping

Select opsys.applicationref, SYS.PackageID, OPSYS.ApplicationId, OPSYS.DisplayName,opsys.Enabled, OPSYS.SMSInstallProgram, OP.ApplicationGroupID, AG.GroupName,opsys.Comment from tb_Application OPSYS join tb_SMSProgram SYS on SYS.SMSProgramID=OPSYS.SMSInstallProgram join tb_Applications_ApplicationGroups OP on OPSYS.ApplicationID=OP.ApplicationID join tb_ApplicationGroup AG on OP.ApplicationGroupID=AG.ApplicationGroupID 

1E Shopping Database : Query to find applications on a machine based on ip address

select  usr.FullName,mac.MachineName,mac.DomainName,mac.IPAddress,app.DisplayName,comp.RequestedTimestamp,comp.DateInstalled,  DeliveryStatus = CASE comp.DeliveryStatus            WHEN '1' THEN 'Completed'         WHEN '2' THEN 'Failed'         WHEN '3' THEN 'Pending'         WHEN '' THEN 'Unknown' END from tb_CompletedOrder comp join tb_Machine mac on comp.MachineId=mac.MachineId join tb_User usr on comp.UserId=usr.UserId join tb_Application app on comp.ApplicationId = app.ApplicationId where  mac.IPAddress in ('10.0.0.0')

1E Shopping Database : Query to check app installed by a machine in Shopping

select  usr.FullName,mac.MachineName,mac.DomainName,mac.IPAddress,app.DisplayName,comp.RequestedTimestamp,comp.DateInstalled,  DeliveryStatus = CASE comp.DeliveryStatus            WHEN '1' THEN 'Completed'         WHEN '2' THEN 'Failed'         WHEN '3' THEN 'Pending'         WHEN '' THEN 'Unknown' END from tb_CompletedOrder comp join tb_Machine mac on comp.MachineId=mac.MachineId join tb_User usr on comp.UserId=usr.UserId join tb_Application app on comp.ApplicationId = app.ApplicationId where MachineName like '%ABC123%'

1E Shopping Database : Sql query to find apps which failed in appstore -sort by time

select usr.FullName,mac.MachineName,mac.DomainName,mac.IPAddress,app.DisplayName,comp.RequestedTimestamp,comp.DateInstalled,DeliveryStatus = CASE comp.DeliveryStatus            WHEN '1' THEN 'Completed'         WHEN '2' THEN 'Failed'         WHEN '3' THEN 'Pending'         WHEN '' THEN 'Unknown' END from tb_CompletedOrder comp join tb_Machine mac on comp.MachineId=mac.MachineId join tb_User usr on comp.UserId=usr.UserId join tb_Application app on comp.ApplicationId = app.ApplicationId where comp.DeliveryStatus ='2' and RequestedTimestamp > '2014-01-30 00:00:00.700'