SQL Queries

SQL query for packages and Source path of the packages

by

Below is the SQL query for a Task sequence ID” ‘CE100053’” with referenced packages and Source path of the packages SELECT     TOP (100) PERCENT ps.Name AS C062, ps.SourceVersion, ps.SourceDate, ps.Targeted AS NumberOfDPsTargeted0, ps.Installed AS NumberOfDPsInstalled0,                       ps.Retrying AS NumberOfDPsRetrying0, ps.Failed AS NumberOfDPsFailed0, ps.SourceSite, ps.SourceSize, ps.SourceCompressedSize, ps.PackageID,                       dbo.v_Package.PkgSourcePath AS [Pkg Source Path] FROM         (SELECT DISTINCT … Read more

is computer is 32 bit or 64 bit ?

by

–Finding computer is 32 bit or 64 bit is easy from SCCM… here is SQL query………… SELECT Distinct SYS.Netbios_Name0, CS.Model0,CPU.Name0 AS [CPU Name], CASE  WHEN CPU.Is64Bit0 = 1 THEN ‘Yes’ ELSE ‘No’ END AS [CPU 64-Bit],ROUND(CONVERT(FLOAT,CPU.MaxClockSpeed0), -2)/1000  AS [CPU (GHz)],ROUND(ROUND(CONVERT(FLOAT,MEM.TotalPhysicalMemory0) / 1048576, 2) , 1)AS [RAM (GB)],[TPM Chip]=(SELECT  v_GS_SYSTEM_DEVICES.Name0 FROM  v_GS_SYSTEM_DEVICES WHERE  v_GS_SYSTEM_DEVICES.ResourceID=SYS.ResourceID AND   v_GS_SYSTEM_DEVICES.Name0 LIKE ‘%Trusted … Read more

C drive disk space information on my all machine

by

SELECT TOP (100) PERCENT dbo.v_R_System.Name0 AS [System Name], dbo.v_GS_LOGICAL_DISK.Name0 AS [C Drive], dbo.v_GS_LOGICAL_DISK.Size0 AS [Total Size], dbo.v_GS_LOGICAL_DISK.FreeSpace0 AS FreeSpace, dbo.v_GS_LOGICAL_DISK.Size0 AS Used FROM dbo.v_R_System INNER JOIN dbo.v_GS_LOGICAL_DISK ON dbo.v_R_System.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID INNER JOIN dbo.v_FullCollectionMembership ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_R_System.ResourceID WHERE (dbo.v_GS_LOGICAL_DISK.DriveType0 = 3) AND (dbo.v_FullCollectionMembership.CollectionID = ‘sms00001’) AND (dbo.v_GS_LOGICAL_DISK.Name0 = ‘c:’) ORDER BY FreeSpace

For a specific Advertisement status for last 3 Days…

by

—for a specific Advertisement status for last 3 Days… if we include the Set   @AdvName = ‘%’ line then and remove the above line in red color will show for all advertisements with last 3 days status — To include all we need to give % in SQL this is a tip Declare     @AdvName Varchar(256)Set   … Read more

HeartBeat Discovery Status of specific collection of systems

by

select     CS.Name0,     max(AD.AgentTime) as ‘Date/Time’ from     dbo.v_AgentDiscoveries ad     JOIN dbo.v_GS_COMPUTER_SYSTEM cs on AD.ResourceID = CS.ResourceId     join dbo.v_FullCollectionMembership FCM on FCM.ResourceID = CS.ResourceId Where     AgentName = ‘Heartbeat Discovery’     and FCM.CollectionID = ‘SMS00001’ Group by     CS.Name0

AD computer Numbers vs SCCM Computer Numbers

by

read here more https://smsug.ca/blogs/garth_jones/archive/2008/12/03/how-to-add-ad-data-to-configmgr-reporting.aspx AD it just another database, just like SQL server is. With that in mind there is nothing stopping you from using SQL to link to AD to give you data about your AD environment! 1) Create Linked Server using SSMS exec master.dbo.sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘<DC Name FQDN>’ … Read more

SQL Query to Show Any missing Boundaries in the SCCM Hierarchy

by

SELECT DISTINCT v_R_System.Name0, v_R_System.Client0, v_RA_System_IPAddresses.IP_Addresses0, v_RA_System_IPSubnets.IP_Subnets0, v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 FROM v_R_System LEFT OUTER JOIN v_RA_System_IPSubnets ON v_R_System.ResourceID = v_RA_System_IPSubnets.ResourceID LEFT OUTER JOIN v_RA_System_IPAddresses ON v_R_System.ResourceID = v_RA_System_IPAddresses.ResourceID LEFT OUTER JOIN v_RA_System_SMSAssignedSites ON v_R_System.ResourceID = v_RA_System_SMSAssignedSites.ResourceID WHERE (v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 IS NULL) AND (NOT (v_RA_System_IPAddresses.IP_Addresses0 IS NULL)) AND (v_R_System.Client0 IS NULL) AND (NOT (v_RA_System_IPSubnets.IP_Subnets0 IS NULL)) order by v_RA_System_IPSubnets.IP_Subnets0

SCCM Report: Server or Workstation Uptime Report

by

  —-This report will give you server uptime information: SELECT os.Caption0 AS ‘Operating System’, cs.Name0 AS Name, DATEDIFF(hour, os.LastBootUpTime0, ws.LastHWScan) AS ‘Uptime (in Hours)’, CONVERT(varchar(20), os.LastBootUpTime0, 100) AS ‘Last Reboot Date/Time’, CONVERT(varchar(20), ws.LastHWScan, 100) AS ‘Last Hardware Inventory’ FROM v_GS_WORKSTATION_STATUS AS ws LEFT OUTER JOIN v_GS_OPERATING_SYSTEM AS os ON ws.ResourceID = os.ResourceID INNER JOIN v_GS_COMPUTER_SYSTEM … Read more

SCCM State ID’s from Client end error (Focused on Patching )

by

TopicType StateID StateName StateDescription 300 0 Compliance state unknown Compliance state unknown 300 1 Compliant Compliant 300 2 Non-compliant Non-compliant 300 3 Conflict detected Conflict detected 301 0 Enforcement state unknown Enforcement state unknown 301 1 Installing update(s) Installing update(s) 301 2 Waiting for restart Waiting for restart 301 3 Waiting for another installation to … Read more

Move SCCM Database to remote SQL server

by

Move SCCM Database to remote SQL server   a. Back up the site database on the current site database server and restore it on the new site database server computer using the SQL Server Management Studio. b. Ensure the primary site server computer account has administrative privileges over the new site database server computer. c. … Read more