Как сделать отчёт (запрос) в sccm для получения информации по жестким дискам

Задача сделать отчёт по жестким дискам на серверах . Сколько свободного места и сколько занято .

SELECT
 
DISTINCT
SYS.Name0 [Name],
ld.Name0 AS [Drive Letter],
 
ld.Size0/1024 as [Total Drive gb],
  
ld.FreeSpace0/1024 as [Free Hard gb],
((ld.Size0)-(ld.FreeSpace0))/1024 as [Used Hard gb]
 
 
INTO #Temp
 
FROM v_R_System AS sys INNER JOIN
v_GS_COMPUTER_SYSTEM AS cs ON sys.ResourceID = cs.ResourceID INNER JOIN
v_GS_LOGICAL_DISK AS ld ON sys.ResourceID = ld.ResourceID inner join
v_gs_Disk as vdisk on sys.resourceid = vdisk.resourceid
right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
 
WHERE
(vdisk.Model0 NOT LIKE '%USB%')
AND (vdisk.Model0 NOT LIKE '%SD MEMORY%')
AND (vdisk.Model0 <> 'SMART')
AND (sys.Active0 = 1) AND (sys.Decommissioned0 = 0)
and OS.Caption0 like '%server%'
 
and sys.Name0 like 'otr%'
or sys.Name0 like 'msk%' 
and sys.Name0 not like '%msk-%'
and ld.Size0   is not null
 
--select * from #Temp
 
 
 
--drop table #Temp
 
 
 
 
 
 
--select * from ##Temp
select *
INTO #Total
from
(   SELECT
[Name]  ,
[Drive Letter],
[Total Drive gb]
--[Free Hard gb],
--[Used Hard gb],
--[Drive free]=  [Drive Letter] +' free',
--[Drive Used]=[Drive Letter] +' used' 
FROM #Temp ) as tr
  PIVOT
(AVG([Total Drive gb])
FOR [Drive Letter]
IN ([I:],[O:],[U:],[G:],[R:],[A:],[D:],[J:],[W:],[F:],[H:],[S:],[E:],[T:],[M:],[Z:],[N:],[Q:],[L:],[C:]))
 AS pivot_id
  group by [Name],[I:],[O:],[U:],[G:],[R:],[A:],[D:],[J:],[W:],[F:],[H:],[S:],[E:],[T:],[M:],[Z:],[N:],[Q:],[L:],[C:]
 
 
 
  --select * from ##Temp
select *
INTO #Free
from
(   SELECT
[Name]  ,
--[Drive Letter],
--[Total Drive gb]
[Free Hard gb],
--[Used Hard gb],
[Drive free]=  [Drive Letter] +' free'
--[Drive Used]=[Drive Letter] +' used' 
FROM #Temp ) as tr
  PIVOT
(AVG([Free Hard gb])
FOR [Drive free]
IN ([I: free],[O: free],[U: free],[G: free],[R: free],[A: free],[D: free],[J: free],[W: free],[F: free],[H: free],[S: free],[E: free],[T: free],[M: free],[Z: free],[N: free],[Q: free],[L: free],[C: free]))
   AS pivot_id
  group by [Name],[I: free],[O: free],[U: free],[G: free],[R: free],[A: free],[D: free],[J: free],[W: free],[F: free],[H: free],[S: free],[E: free],[T: free],[M: free],[Z: free],[N: free],[Q: free],[L: free],[C: free]
   
 
 
  select *
INTO #Used
from
(   SELECT
[Name]  ,
--[Drive Letter],
--[Total Drive gb]
--[Free Hard gb],
[Used Hard gb],
--[Drive free]=  [Drive Letter] +' free'
[Drive Used]=[Drive Letter] +' used' 
FROM #Temp ) as tr
  PIVOT
(AVG([Used Hard gb])
FOR [Drive Used]
IN ([I: used],[O: used],[U: used],[G: used],[R: used],[A: used],[D: used],[J: used],[W: used],[F: used],[H: used],[S: used],[E: used],[T: used],[M: used],[Z: used],[N: used],[Q: used],[L: used],[C: used]))
   AS pivot_id
  group by [Name],[I: used],[O: used],[U: used],[G: used],[R: used],[A: used],[D: used],[J: used],[W: used],[F: used],[H: used],[S: used],[E: used],[T: used],[M: used],[Z: used],[N: used],[Q: used],[L: used],[C: used]
  
 
 select
  
Total.[name], Free.[C: free],Used.[C: used],Total.[C:],Free.[D: free],Used.[D: used],Total.[D:],Free.[E: free],Used.[E: used],Total.[E:],Free.[F: free],
 Used.[F: used],Total.[F:],Free.[G: free],Used.[G: used],Total.[G:],Free.[H: free],Used.[H: used],Total.[H:],Free.[I: free],Used.[I: used],
 Total.[I:],Free.[J: free],Used.[J: used],Total.[J:],Free.[L: free],Used.[L: used],Total.[L:],Free.[M: free],Used.[M: used],Total.[M:],
 Free.[N: free],Used.[N: used],Total.[N:],Free.[O: free],Used.[O: used],Total.[O:],Free.[Q: free],Used.[Q: used],Total.
 [Q:],Free.[R: free],Used.[R: used],Total.[R:],Free.[S: free],Used.[S: used],Total.[S:],Free.[T: free],Used.[T: used],Total.[T:],
 Free.[U: free],Used.[U: used],Total.[U:],Free.[W: free],Used.[W: used],Total.[W:],Free.[Z: free],Used.[Z: used],Total.[Z:]
 from #Total as Total
LEFT OUTER JOIN #Free as Free  ON  Total.name = Free.name
LEFT OUTER JOIN #Used as Used  ON  Used.name = Free.name
 
/*
drop table #Total
drop table #Free
drop table #Used
drop table #Temp
*/

Similar Posts:

• Как сделать отчёт (запрос) в sccm по Cpu, DISK, Memory, IP в одной табличке.
• Как сделать отчёт (запрос) в sccm для получения информации по жестким дискам №2
• Как сделать запрос отчёта по жёсткому диску ssd в SCCM Microsoft Endpoint Configuration Manager.
• Вывести список баз в sql и как узнать когда был последний бекап + где лежит физически сама база
• Как узнать какие запросы грузят диск