SystemCenter Titans

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.Version0 from v_Add_Remove_Programs inner join v_R_System on v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID where v_Add_Remove_Programs.DisplayName0 like 'Microsoft Lync 2010'

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 = 9 There are several other enforcement states you can use instead by changing the number after ‘LastEnforcementMessage ID =’ at the end of the query. 1 – Enforcement started 3 – Waiting for another installation to complete 6 – General failure 8 – Installing update 9 – Pending system restart 10 – Successfully installed update 11 – Failed to install update 12 – Downloading update 13 – Downloaded update

select distinct * from v_R_System where Creation_Date0 > '2014-12-01 06:22:10.000' and Resource_Domain_OR_Workgr0 = 'domainname' and Obsolete0 not like 1

select pkg.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 BranchDP from v_Package pkg join v_DistributionPoint dp on pkg.PackageID=dp.PackageID join v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPath and dp.PackageID=stat.PackageID where stat.State!=0 and DP.SiteCode in ('sitecode ')

select * from tb_Application select * from tb_Application where Enabled = 1 select * from tb_Application where Enabled = 0

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 

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')