SQL Queries

SQL query for Patches required systems as per collection with the SIZE of each update

by

Below is sql query for  Patches required systems as per collection with the SIZE of each update   SELECT DISTINCT                       TOP (100) PERCENT SYS.Name0 AS [Machine Name], UCS.Status AS [Patch Status Code], UI.BulletinID, UI.ArticleID, UI.Title,                       dbo.v_FullCollectionMembership.ResourceID, dbo.v_Collection.CollectionID, UI.CI_ID, dbo.CI_Contents.SourceSize /(1024.0*1024) AS SizeinMBFROM         dbo.v_UpdateContents INNER JOIN                      dbo.v_FullCollectionMembership INNER JOIN                      dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID … Read more

SQL Report with the systems Names and Architecture for specific collection

by

finding the systems Names and Architecture for specific collection   SELECT     dbo.v_Collection.CollectionID, dbo.v_GS_COMPUTER_SYSTEM.Name0, dbo.v_GS_COMPUTER_SYSTEM.SystemType0,                       dbo.v_GS_COMPUTER_SYSTEM.UserName0, dbo.v_R_System.Operating_System_Name_and0FROM         dbo.v_Collection INNER JOIN                      dbo.v_FullCollectionMembership ON dbo.v_Collection.CollectionID = dbo.v_FullCollectionMembership.CollectionID INNER JOIN                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN                      dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceIDWHERE     (dbo.v_Collection.CollectionID = ‘CollectionID’)

Required Patches based on collection ID

by

SELECT DISTINCT                       TOP (100) PERCENT SYS.Name0 AS [Machine Name], UCS.Status AS [Patch Status Code],                       CASE WHEN UCS.Status = ‘2’ THEN ‘Applicable’ WHEN UCS.Status = ‘3’ THEN ‘Installed’ ELSE ” END AS ‘Patch Status’, UI.BulletinID, UI.ArticleID, UI.Title,                       dbo.v_FullCollectionMembership.ResourceID, dbo.v_Collection.CollectionIDFROM         dbo.v_FullCollectionMembership INNER JOIN                      dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN                      dbo.v_R_System AS SYS LEFT … Read more

sccm For Desktops only report

by

For Desktops only report   SELECT     TOP (100) PERCENT dbo.v_R_System.Name0 AS [Computer Name], dbo.v_R_System.User_Name0 AS [User Name],                       dbo.v_R_System.User_Domain0 AS [Domain Name], dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer,                       dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number],                       dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type], dbo.v_GS_SYSTEM.SystemType0 AS [System Type]FROM         dbo.v_GS_SYSTEM_ENCLOSURE INNER JOIN                      dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN                      dbo.v_GS_SYSTEM ON … Read more

SCCM report for hardware specs of all desktops and laptops on the domain

by

      —select * from v_GS_SYSTEM_ENCLOSURE SELECT  distinct CS.name0 as ‘Computer Name’, CS.domain0 as ‘Domain’, CS.UserName0 as ‘User’, BIOS.SerialNumber0 as ‘Bios serial’, SE.SerialNumber0 as ‘System Enclosure serial’, CS.Manufacturer0 as ‘Manufacturer’, CS.Model0 as ‘model’, OS.Caption0 as ‘OS’, RAA.SMS_Assigned_Sites0 as ‘Site’, RAM.TotalPhysicalMemory0 as ‘Total Memory’, sum(isnull(LDisk.Size0,’0′)) as ‘Hardrive Size’, sum(isnull(LDisk.FreeSpace0,’0′)) AS ‘Free Space’, CPU.MaxClockSpeed0 as ‘Max … Read more

Single system required Patch’s report from SQL

by

—Single system required Patch’s report from SQL   SELECT DISTINCT SYS.Name0 AS [Machine Name], UCS.Status AS [Patch Status Code], CASE WHEN UCS.Status = ‘2’ THEN ‘Applicable’ WHEN UCS.Status = ‘3’ THEN ‘Installed’ ELSE ” END AS ‘Patch Status’, UI.BulletinID, UI.ArticleID, UI.Title FROM v_R_System AS SYS LEFT OUTER JOIN v_Update_ComplianceStatusAll AS UCS ON SYS.ResourceID = UCS.ResourceID … Read more

SQL Add-on Tool like SCCM Right Click tool : SQL Right Click Tool “SSMS Tools” https://www.ssmstoolspack.com/

by

SSMS Tools Pack is an add-in for Microsoft SQL Server Management Studio (SSMS) 2005, 2008, 2008 R2, 2011 (Denali) CTP1 and their respective Express versions.It contains a few upgrades to the SSMS IDE that I thought were missing. To see what’s new and what got fixed in a release check the News page. The current … Read more

SQL Tutorial – SELECT Statement

by

SQL Tutorial SELECT Statement — Extended Query Capabilities This subsection details the remaining features of SELECT statements. The basics are at SELECT Statement Basics. The extended features are grouped as follows: Sorting Query Results — using the ORDER BY clause Expressions — in the SELECT clause and WHERE clause Literal — self-defining values Function Call … Read more