SystemCenter Titans

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

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

delete   from  tb_Machine  where  MachineName = 'XXXX'

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'

SELECT DISTINCT p.PackageID, p.Name, 'Source Size (MB)' = n.SourceSize FROM v_Package p LEFT JOIN v_PackageStatusRootSummarizer n ON   p.PackageID = n.PackageID

To be run on client > Launch command as administrator net stop bits Delete the qmgr*.dat files. To do this, type the following command at a command prompt, and then press ENTER: Del "%ALLUSERSPROFILE%\Application Data\Microsoft\Network\Downloader\qmgr*.dat" net start bits

use tempdb go dbcc shrinkfile (2, 10) go dbcc shrinkfile (1,10)

Get-ChildItem "F:\SMSPKGF$" -recurse | Where {!$_.PSIsContainer} | Group Directory | sort-object count -descending | Format-Table Name, Count -autosize

select servername, sitecode, count(distinct name0) 'clients' from ProtectedSiteSystem_ARR PSS join v_BoundaryInfo bound on pss.BoundaryID=bound.BoundaryID join v_RA_System_IPSubnets subs on subs.IP_Subnets0=bound.value join v_R_System sys on sys.resourceID=subs.resourceID where client0=1 and obsolete0=0 group by servername, sitecode order by count(*) desc

select v_FullCollectionMembership.CollectionID, v_Collection.Name from v_FullCollectionMembership join v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID where  v_FullCollectionMembership.Name = 'machinename'

Select OfferName Name, Recieved, Failures, ProgramsStarted Started, ProgramsFailure Errors, ProgramsSuccess Success, OfferID ‘Advertisement ID’ From vOfferStatusSummarizerRoot Where ScheduleToken = ’0001128000080008′ And OfferID In (‘XXX12345′, ‘XXX67890′) Order by OfferName

Select SD.Name0, PF.DisplayName0, PF.Version0 From v_Add_Remove_Programs PF Join v_R_System SD on PF.ResourceID = SD.ResourceID Where PF.DisplayName0 = ‘McAfee VirusScan Enterprise’ Group By SD.Name0, PF.DisplayName0, PF.Version0 Order By SD.Name0

Select Count(SD.Name0) Counts, PF.DisplayName0, PF.Version0 From v_Add_Remove_Programs PF Join v_R_System SD on PF.ResourceID = SD.ResourceID Where PF.DisplayName0 = ‘McAfee VirusScan Enterprise’ Group By PF.DisplayName0, PF.Version0 Order By Counts, PF.Version0

Select SD.Name0 ‘Machine Name’, SD.Operating_System_Name_and0 NOS, SS.Name0 ‘Service Name’, SS.DisplayName0 ‘Display Name’, SS.StartMode0 ‘Start Type’, SS.Started0 Started, SS.State0 State, SS.Status0 Status From System_DISC SD Join Services_DATA SS on SS.MachineID = SD.ItemKey Where SS.Name0 In (‘W3SVC’, ‘MsFtpSvc’, ‘TlntSvr’) Order By ‘Machine Name’

Select  SD.Name0 ‘Machine Name’, SD.User_Name0 ‘Last Logged on User Name’, Convert(VarChar(10), OS.LastBootUpTime0, 101)  ‘Last Boot Date’ From v_R_System SD Join v_Gs_Operating_System OS on SD.ResourceID = OS.ResourceID Order By ‘Machine Name’

Select SD.Name0 ‘Machine Name’, SD.Resource_Domain_OR_Workgr0 ‘Resource Domain’, SD.User_Name0 ‘Login ID’, SD.User_Domain0 ‘Account Domain’, USR.Full_User_Name0 ‘Full Name’, PCB.SerialNumber0 ‘Serial Number’, CS.Manufacturer0 Manufacturer, CS.Model0 Model, SAS.SMS_Assigned_Sites0 ‘Assigned Site Code’ From v_R_System SD Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID Join v_Collection COL on FCM.CollectionID = COL.CollectionID Join v_R_User USR on SD.User_Name0 = USR.User_Name0 Join v_GS_PC_BIOS PCB on SD.ResourceID = PCB.ResourceID Join v_GS_COMPUTER_SYSTEM CS on SD.ResourceID = CS.ResourceID Join v_RA_System_SMSAssignedSites SAS on SD.ResourceID = SAS.ResourceID Where COL.Name = ‘All Systems’

Select  Value, SiteCode, ‘Boundary Type’ = Case When BoundaryType = 0 Then ‘IP Subnet’ When BoundaryType = 1 Then ‘Active Directory Site’ When BoundaryType = 2 Then ‘IPv6 Prefix’ When BoundaryType = 3 Then ‘IP Address Range’ Else ‘UnKnown’ End, DisplayName, ‘Connection’ = Case When BoundaryFlags = 0 Then ‘Fast’ When BoundaryFlags = 1 Then ‘Slow’ Else ‘UnKnown’ End, ‘Protected’ = Case When Action = 0 Then ‘No’ When Action = 1 Then ‘Yes’ Else ‘UnKnown’ End From Boundary

Select Distinct v_R_System.Name0, v_GS_SoftwareFile.FileName, v_GS_SoftwareFile.FileDescription, v_GS_SoftwareFile.FileVersion From v_R_System Join v_GS_SoftwareFile on v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID Where FileName = ‘wmplayer.exe’

Select Distinct CS.Name0 ‘Machine Name’, CS.UserName0 ‘User Name’, RU.Full_User_Name0 ‘Full Name’, UOU.User_OU_Name0 ‘Users OU’, RA.IP_Subnets0  ‘Subnet’ From v_Gs_Computer_System CS  Join v_RA_System_IPSubnets RA on RA.ResourceID = CS.ResourceID Join v_R_User RU on RU.Unique_User_Name0 = CS.UserName0 Join v_RA_User_UserOUName UOU on UOU.ResourceID = RU.ResourceID Where UOU.User_OU_Name0 = ‘DomainName.COM/OuName’ Order by  CS.Name0, CS.Username0, RU.Full_User_Name0, RA.IP_Subnets0

Select Members.Name ‘Collection Members:’ From CollectionMembers Members Join Collections Coll on Members.SiteID = Coll.SiteID Where Coll.CollectionName = ‘All Systems’

Select CC . CollectionID , CN . CollectionName ,  CC . TimeUpdated From Collection_MemberChg_Notif CC, Join Collections CN  on  CC . CollectionID  =  CN . SiteID Order  By  CollectionName

Select     SD.Name0 ‘Machine Name’, VC.Name0 ‘Video Card’, Convert(VarChar, VC.AdapterRam0 / 1024) + ‘ MB’ From v_R_System SD Join v_Gs_Video_Controller VC on SD.ResourceID = VC.ResourceID Where VC.Name0 <> ‘ConfigMgr Remote Control Driver’ Order By SD.Name0

Select  SD.Name0 ‘Machine Name’, SC.SMS_Assigned_Sites0 ‘Assigned Site’, SD.Client_Version0 Version From v_R_System SD Join v_RA_System_SmsAssignedSites SC on SD.ResourceID = SC.ResourceID Join v_GS_Operating_System OS on SD.ResourceID = OS.ResourceID Where SD.Client0 = 1 And SD.Client_Type0 = 1 Order By ‘Machine Name’