Перейти к содержимому

Как сделать отчёт (запрос) в sccm по Cpu, DISK, Memory, IP в одной табличке.

Надо сделать отчёт из sccm по всем серверам, для переезда в другой дата центр.  В отчёте должно содержаться cpu (сколько сокетов, ядер ), оперативная память , сколько места на дисках , сколько суммарно занимает информация на дисках и ip адреса .

Как сделать отчёт (запрос) в sccm по Cpu, DISK, Memory, IP в одной табличке.

Declare  @variable varchar(max)

 set @variable='SHR%'


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]

--select * from #Temp
 
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 @variable
--or sys.Name0 like 'msk%' 
--and sys.Name0 not like '%msk-%'
and ld.Size0   is not null
 

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 
 
[Name0],[Make],[Model],[Number of CPUs],[CPU Model],[Speed GHz],[Number of Cores per CPU],[Logical CPU Count],[socet]
--,[sum cores]
,[sum Logical CPU Count]
,[Total Memory GB],[Sum Used Hard gb],[IPs],[C: free],[C: used],[C:],[D: free],[D: used],[D:],[E: free],[E: used],[E:],[F: free],[F: used],[F:],[G: free],[G: used],[G:],[H: free],[H: used],[H:],[I: free],[I: used],[I:],[J: free],[J: used],[J:],[L: free],[L: used],[L:],[M: free],[M: used],[M:],[N: free],[N: used],[N:],[O: free],[O: used],[O:],[Q: free],[Q: used],[Q:],[R: free],[R: used],[R:],[S: free],[S: used],[S:],[T: free],[T: used],[T:],[U: free],[U: used],[U:],[W: free],[W: used],[W:],[Z: free],[Z: used],[Z:]

 
 from 
 
 
 
 (
 SELECT 
    CS.Name0,
  CS.Manufacturer0 AS [Make],
  CS.Model0 AS [Model],
  COUNT(CPU.ResourceID) AS [Number of CPUs],
  CPU.Name0 AS [CPU Model],
  CPU.NumberOfCores0 AS [Number of Cores per CPU],
  CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count],
    CPU.NormSpeed0 as [Speed GHz],
  
  

   COUNT(CPU.DeviceID0) as [socet],
 --sum(CPU.NumberOfCores0) as [sum cores],
 sum(CPU.NumberOfLogicalProcessors0) as [sum Logical CPU Count],
 v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024/1024 as [Total Memory GB]
 --,sum(ld.Size0)/1024/1024 as 'sum Drive gb'
  --v_GS_DISK.Caption0,
  --sum(v_GS_DISK.Size0)
 FROM  
  dbo.v_GS_COMPUTER_SYSTEM CS  inner join v_GS_PROCESSOR CPU on CPU.ResourceID = CS.ResourceId
  inner join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID = CS.ResourceId




  --inner join v_GS_DISK on v_GS_DISK.ResourceID = CS.ResourceId
WHERE CS.Name0  like @variable
group by 
CS.Name0,

  CPU.Name0,
  CS.Model0,
  CS.Manufacturer0,
  CPU.NumberOfCores0,
  CPU.NumberOfLogicalProcessors0 ,
    CPU.NormSpeed0,
  CS.TotalPhysicalMemory0,
  v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 ) A

  left join (
  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) B

on A.Name0=B.Name

  left join (

  SELECT  [Name], sum([Used Hard gb]) as [Sum Used Hard gb] FROM #Temp 

  group by [Name]
  ) G

  on A.Name0=G.Name


left join (

SELECT DISTINCT
SYS.Name0 as [Name],

IPs = Stuff ((select ', ' + NA1.IPAddress0 from v_GS_NETWORK_ADAPTER_CONFIGUR as NA1
where NA1.ResourceID = NA2.ResourceID FOR XML PATH ('')) , 1, 1, '')


FROM v_R_System AS sys INNER JOIN
 v_GS_NETWORK_ADAPTER_CONFIGUR NA2 on NA2.ResourceID = sys.ResourceID) F

  on A.Name0=F.Name




drop table #Total
drop table #Free
drop table #Used
drop table #Temp

 

Similar Posts:

Метки:

Добавить комментарий

Ваш адрес email не будет опубликован.