SQL Query SCCM – Menampilkan Type Disk SSD

SQL Query SCCM – Menampilkan Type Disk SSD

Read Time:1 Minute, 5 Second

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

Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Tinggalkan Balasan

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Situs ini menggunakan Akismet untuk mengurangi spam. Pelajari bagaimana data komentar Anda diproses.