SQL Queries

Patching Related End to end – Collections and reports :-

by

Below is for a compliance report based on SQL declare @CollectID AS  varchar(8)SET @CollectID= ‘SMS0001’ declare @CollectionListID AS  varchar(90)SET @CollectionListID=’ScopeId_5432f432-F885-4A98-B666-5432134122/AuthList_F15C63EA-B655-4940-A250-654323fd432′ declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID=@CollectionListID declare @CollCount int, @NumClients int; select @CollCount = count(*), @NumClients=isnull(sum(cast(IsClient as int)), 0) from v_ClientCollectionMembers ccm where ccm.CollectionID=@CollectID select     CollectionName=vc.Name,    ‘Update List’=al.Title,    Status=sn.StateName,    … Read more

Computers Bright Mail

by

SELECT   distinct dbo.v_R_System.Name0 AS [Computer Name], dbo.v_Add_Remove_Programs.DisplayName0 AS [Software Name],                       dbo.v_Add_Remove_Programs.Publisher0 AS Publisher, dbo.v_Add_Remove_Programs.Version0FROM         dbo.v_Add_Remove_Programs INNER JOIN                      dbo.v_R_System ON dbo.v_Add_Remove_Programs.ResourceID = dbo.v_R_System.ResourceIDWHERE     dbo.v_Add_Remove_Programs.DisplayName0 like ‘%Bright%’

SQL Query to find the collections Hierarchy : SCCM Colleections

by

SQL Query to find the collections Hierarchy   WITH folderHierarchy (CollectionID,[Name],ParentCollectionID,[Path])AS(       SELECT             CollectionID,             [Name],             ParentCollectionID,            CAST(‘/’+[Name]+’/’ AS VARCHAR(MAX)) AS [Path]      FROM            (SELECT                   CollectionID,                   [Name],                   ParentCollectionID              FROM v_Collection                  INNER JOIN v_CollectToSubCollect                   ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS V_1      WHERE            ParentCollectionID = ‘COLLROOT’     UNION ALL   SELECT      child.CollectionID,      child.Name,      … Read more

Use Full SQL Tables / Views

by

UseFull SQL Tables/ Views Description / Use v_Add_Remove_Programs   v_Advertisement   v_AdvertisementInfo   v_ClientCollectionMembers   v_Collection   v_Collection   v_ConfigurationItems   v_DistributionPoint   v_DistributionPointGroup   v_GS_ADD_REMOVE_PROGRAMS   v_GS_ADD_REMOVE_PROGRAMS_64   v_GS_COMPUTER_SYSTEM   v_GS_DISK   v_GS_Memory_Details0   v_GS_OPERATING_SYSTEM   v_GS_PATCHSTATE   v_GS_SERVICE   v_GS_SoftwareProduct   v_GS_SYSTEM   v_GS_WORKSTATION_STATUS   v_GS_X86_PC_MEMORY   v_OS_Details   v_Package   v_PackageStatus   … Read more

Day to Day Operations SCCM / ConfigMgr 2007 Reports

by

I recommend below ConfigMgr Reports to be familiar and these might be useful in day to day operations   Asset Intelligence:- License 03A – Count of licenses by license statusHardware 07A – USB devices by manufacturer Software 02C – Software by Category and Family Software 01A – Summary of installed software in a specific collection … Read more

SQL Server 2008 Express Edition Limitations

by

SQL Server 2008 Express Edition is a fantastic option for those seeking the power of a full-featured relational database but with low performance requirements. It replaces the Microsoft Data Engine (MSDE) as the free version of SQL Server for application development and lightweight use. It remains free and retains the limitations of MSDE with respect … Read more

Where Name0 in

by

Deleting Machines Directly From The SMS Database To delete an individual machine from the SMS database using the SMS console it is necessary to create a collection using the direct membership rule wizard or base your new collection on a newly created Query. The first method is slow and can be time consuming, the second … Read more

how to move the Site Database in Microsoft System Center Configuration Manager 2007 from a computer

by

INTRODUCTION This article describes how to move the Site Database in Microsoft System Center Configuration Manager 2007 from a computer that is running Microsoft SQL Server 2005 to another drive on the same computer, or to another computer that is running SQL Server 2005. MORE INFORMATION In certain situations, you may have to move the … Read more