Dalam SCCM Query sangat berbeda dengan Query SQL, untuk mendapatkan nilai data type Disk setiap perangkat dalam SCCM hanya disediakan kode 0.3.4.5 tanpa identifikasi type, jd kita perlu untuk membuat query SQL nya untuk dapat menampilkan data tersebut.
Berikut SQL Query untuk menampilkan data type Disk dalam bentuk SSD atau HDD dalam SQL
SELECT DISTINCT
ResourceID,
SystemName0 AS ComputerName,
Model0 AS Model,
DeviceID0 AS DeviceID,
Index0 AS [Index],
InterfaceType0 AS Interface,
Manufacturer0 AS Manufacturer,
MediaType0 AS MediaType,
Partitions0 AS Partitions,
Size0 AS Capacity,
manufacturer0 AS Make
FROM
dbo.v_GS_DISK
WHERE
Model0 LIKE ‘%’
=====================
DECLARE @CollectionID NVARCHAR(8);
SET @CollectionID = ‘SMS00001’ –All Systems
SELECT [t0].[Name],
[t1].[Model0] AS [Model],
[t1].[SerialNumber0] AS [SerialNumber],
[t1].[FirmwareVersion0] AS [FirmwareVersion],
CASE [t1].[MediaType0]
WHEN 0 THEN ‘Unspecified’
WHEN 3 THEN ‘HDD’
WHEN 4 THEN ‘SSD’
WHEN 5 THEN ‘SCM’
END AS [MediaType],
[t2].[Model0] AS [Model2],
[t2].[DeviceID0] AS [DeviceID],
[t2].[Index0] AS [Index],
[t2].[InterfaceType0] AS [Interface],
[t2].[Manufacturer0] AS [Manufacturer],
[t2].[MediaType0] AS [MediaType],
[t2].[Partitions0] AS [Partitions],
[t2].[Size0] AS [Capacity],
[t2].[Manufacturer0] AS [Make]
FROM [dbo].[v_FullCollectionMembership_Valid] AS [t0]
LEFT JOIN [dbo].[v_GS_PHYSICAL_DISK] AS [t1] ON [t0].[ResourceID] = [t1].[ResourceID]
LEFT JOIN [dbo].[v_GS_DISK] AS [t2] ON [t0].[ResourceID] = [t2].[ResourceID]
WHERE [t0].[CollectionID] = @CollectionID
AND [t0].[ResourceType] = 5 –Devices Only
Average Rating