use admin
go
create procedure ProductionStorageSummary
as
set nocount on
select cast(case when grouping(s.ServerName) = 1 then 'Total All Servers' else s.Servername end as varchar(20)) [Server Name]
  , cast(case when (grouping(s.ServerName) = 1 and grouping(s.Description) = 1) 
    then '' 
    else case grouping(s.Description) when 1 then 'Total On Server' else s.Description end
    end  as varchar(20)) [Logical Drive]
  , sum(s.SizeMB) [Drive Space MB]
  , sum(s.UsedMB) [Space Used MB]
from lnkdat.WSODConfig.dbo.sqlinventoryLogicalDrives s
where s.ServerName in ('apollo','athena','zeus', 'SQ-QUERYP101','SQ-QUOTESP101','SQ-DMP101')
and (s.Description like ('E:%')
     or s.Description like ('F:%')
     or s.Description like ('G:%'))
group by s.ServerName, s.Description
with rollup
order by grouping(s.ServerName), s.Servername, grouping(s.Description), s.Description

select cast(ServerName as varchar(20)) [Server Name]
 , cast(DatabaseName as varchar(20)) [Database Name]
 , cast(FileGroup as varchar(20)) [File Group]
 , cast(case when charindex('Total',Path) = 1 then Path else Left(Path,2) end as varchar(20)) [Logical Drive]
 , DBSizeMB [Total Alloc MB]
 , DBAVailMB [Free MB]
from OPENQUERY(lnkbap, 'select @@servername [ServerName]
 , case grouping([Database]) when 1 then ''Total all DBs'' else [Database] end [DatabaseName]
 , case when grouping([Database]) = 1 and grouping(FileGroup) = 1 then ''''
   else case grouping(FileGroup) when 1 then ''Total for DB'' else FileGroup end 
   end [FileGroup]
 , case when grouping(FileGroup) = 1 and grouping(Path) = 1 then '''' 
        when grouping(FileGroup) = 1 and grouping(Path) = 0 then ''Total for FileGroup'' 
        when grouping(FileGroup) = 0 and grouping(Path) = 1 then ''Total for Drive'' 
        else Path end [Path]
 , sum(Size) [DBSizeMB]
 , sum(SpaceAvailableInMB) [DBAvailMB] 
from admin.dbo.SpaceUsedHistory
where RecCreatedDt > (select max(recCreatedDt) from admin.dbo.SpaceUsedHistory) - 1
and Server = @@servername
group by [Database], FileGroup, Path
with rollup
order by grouping([Database]), [Database], Grouping(FileGroup), FileGroup, grouping(path), Path
') bap

select cast(ServerName as varchar(20)) [Server Name]
 , cast(DatabaseName as varchar(20)) [Database Name]
 , cast(FileGroup as varchar(20)) [File Group]
 , cast(case when charindex('Total',Path) = 1 then Path else Left(Path,2) end as varchar(20)) [Logical Drive]
 , DBSizeMB [Total Alloc MB]
 , DBAVailMB [Free MB]
from OPENQUERY(lnkbat, 'select @@servername [ServerName]
 , case grouping([Database]) when 1 then ''Total all DBs'' else [Database] end [DatabaseName]
 , case when grouping([Database]) = 1 and grouping(FileGroup) = 1 then ''''
   else case grouping(FileGroup) when 1 then ''Total for DB'' else FileGroup end 
   end [FileGroup]
 , case when grouping(FileGroup) = 1 and grouping(Path) = 1 then '''' 
        when grouping(FileGroup) = 1 and grouping(Path) = 0 then ''Total for FileGroup'' 
        when grouping(FileGroup) = 0 and grouping(Path) = 1 then ''Total for Drive'' 
        else Path end [Path]
 , sum(Size) [DBSizeMB]
 , sum(SpaceAvailableInMB) [DBAvailMB] 
from admin.dbo.SpaceUsedHistory
where RecCreatedDt > (select max(recCreatedDt) from admin.dbo.SpaceUsedHistory) - 1
and Server = @@servername
group by [Database], FileGroup, Path
with rollup
order by grouping([Database]), [Database], Grouping(FileGroup), FileGroup, grouping(path), Path
') bat


select cast(ServerName as varchar(20)) [Server Name]
 , cast(DatabaseName as varchar(20)) [Database Name]
 , cast(FileGroup as varchar(20)) [File Group]
 , cast(case when charindex('Total',Path) = 1 then Path else Left(Path,2) end as varchar(20)) [Logical Drive]
 , DBSizeMB [Total Alloc MB]
 , DBAVailMB [Free MB]
from OPENQUERY(lnkbDM1, 'select @@servername [ServerName]
 , case grouping([Database]) when 1 then ''Total all DBs'' else [Database] end [DatabaseName]
 , case when grouping([Database]) = 1 and grouping(FileGroup) = 1 then ''''
   else case grouping(FileGroup) when 1 then ''Total for DB'' else FileGroup end 
   end [FileGroup]
 , case when grouping(FileGroup) = 1 and grouping(Path) = 1 then '''' 
        when grouping(FileGroup) = 1 and grouping(Path) = 0 then ''Total for FileGroup'' 
        when grouping(FileGroup) = 0 and grouping(Path) = 1 then ''Total for Drive'' 
        else Path end [Path]
 , sum(Size) [DBSizeMB]
 , sum(SpaceAvailableInMB) [DBAvailMB] 
from admin.dbo.SpaceUsedHistory
where RecCreatedDt > (select max(recCreatedDt) from admin.dbo.SpaceUsedHistory) - 1
and Server = @@servername
group by [Database], FileGroup, Path
with rollup
order by grouping([Database]), [Database], Grouping(FileGroup), FileGroup, grouping(path), Path
') bDM1

select cast(ServerName as varchar(20)) [Server Name]
 , cast(DatabaseName as varchar(20)) [Database Name]
 , cast(FileGroup as varchar(20)) [File Group]
 , cast(case when charindex('Total',Path) = 1 then Path else Left(Path,2) end as varchar(20)) [Logical Drive]
 , DBSizeMB [Total Alloc MB]
 , DBAVailMB [Free MB]
from OPENQUERY(lnkbDM2, 'select @@servername [ServerName]
 , case grouping([Database]) when 1 then ''Total all DBs'' else [Database] end [DatabaseName]
 , case when grouping([Database]) = 1 and grouping(FileGroup) = 1 then ''''
   else case grouping(FileGroup) when 1 then ''Total for DB'' else FileGroup end 
   end [FileGroup]
 , case when grouping(FileGroup) = 1 and grouping(Path) = 1 then '''' 
        when grouping(FileGroup) = 1 and grouping(Path) = 0 then ''Total for FileGroup'' 
        when grouping(FileGroup) = 0 and grouping(Path) = 1 then ''Total for Drive'' 
        else Path end [Path]
 , sum(Size) [DBSizeMB]
 , sum(SpaceAvailableInMB) [DBAvailMB] 
from admin.dbo.SpaceUsedHistory
where RecCreatedDt > (select max(recCreatedDt) from admin.dbo.SpaceUsedHistory) - 1
and Server = @@servername
group by [Database], FileGroup, Path
with rollup
order by grouping([Database]), [Database], Grouping(FileGroup), FileGroup, grouping(path), Path
') bDM2

select cast(ServerName as varchar(20)) [Server Name]
 , cast(DatabaseName as varchar(20)) [Database Name]
 , cast(FileGroup as varchar(20)) [File Group]
 , cast(case when charindex('Total',Path) = 1 then Path else Left(Path,2) end as varchar(20)) [Logical Drive]
 , DBSizeMB [Total Alloc MB]
 , DBAVailMB [Free MB]
from OPENQUERY(lnkbque, 'select @@servername [ServerName]
 , case grouping([Database]) when 1 then ''Total all DBs'' else [Database] end [DatabaseName]
 , case when grouping([Database]) = 1 and grouping(FileGroup) = 1 then ''''
   else case grouping(FileGroup) when 1 then ''Total for DB'' else FileGroup end 
   end [FileGroup]
 , case when grouping(FileGroup) = 1 and grouping(Path) = 1 then '''' 
        when grouping(FileGroup) = 1 and grouping(Path) = 0 then ''Total for FileGroup'' 
        when grouping(FileGroup) = 0 and grouping(Path) = 1 then ''Total for Drive'' 
        else Path end [Path]
 , sum(Size) [DBSizeMB]
 , sum(SpaceAvailableInMB) [DBAvailMB] 
from admin.dbo.SpaceUsedHistory
where RecCreatedDt > (select max(recCreatedDt) from admin.dbo.SpaceUsedHistory) - 1
and Server = @@servername
group by [Database], FileGroup, Path
with rollup
order by grouping([Database]), [Database], Grouping(FileGroup), FileGroup, grouping(path), Path
') bque

select cast(ServerName as varchar(20)) [Server Name]
 , cast(DatabaseName as varchar(20)) [Database Name]
 , cast(FileGroup as varchar(20)) [File Group]
 , cast(case when charindex('Total',Path) = 1 then Path else Left(Path,2) end as varchar(20)) [Logical Drive]
 , DBSizeMB [Total Alloc MB]
 , DBAVailMB [Free MB]
from OPENQUERY(lnkbquo, 'select @@servername [ServerName]
 , case grouping([Database]) when 1 then ''Total all DBs'' else [Database] end [DatabaseName]
 , case when grouping([Database]) = 1 and grouping(FileGroup) = 1 then ''''
   else case grouping(FileGroup) when 1 then ''Total for DB'' else FileGroup end 
   end [FileGroup]
 , case when grouping(FileGroup) = 1 and grouping(Path) = 1 then '''' 
        when grouping(FileGroup) = 1 and grouping(Path) = 0 then ''Total for FileGroup'' 
        when grouping(FileGroup) = 0 and grouping(Path) = 1 then ''Total for Drive'' 
        else Path end [Path]
 , sum(Size) [DBSizeMB]
 , sum(SpaceAvailableInMB) [DBAvailMB] 
from admin.dbo.SpaceUsedHistory
where RecCreatedDt > (select max(recCreatedDt) from admin.dbo.SpaceUsedHistory) - 1
and Server = @@servername
group by [Database], FileGroup, Path
with rollup
order by grouping([Database]), [Database], Grouping(FileGroup), FileGroup, grouping(path), Path
') bquo

select cast(ServerName as varchar(20)) [Server Name]
 , cast(DatabaseName as varchar(20)) [Database Name]
 , cast(FileGroup as varchar(20)) [File Group]
 , cast(case when charindex('Total',Path) = 1 then Path else Left(Path,2) end as varchar(20)) [Logical Drive]
 , DBSizeMB [Total Alloc MB]
 , DBAVailMB [Free MB]
from OPENQUERY(lnkbz, 'select @@servername [ServerName]
 , case grouping([Database]) when 1 then ''Total all DBs'' else [Database] end [DatabaseName]
 , case when grouping([Database]) = 1 and grouping(FileGroup) = 1 then ''''
   else case grouping(FileGroup) when 1 then ''Total for DB'' else FileGroup end 
   end [FileGroup]
 , case when grouping(FileGroup) = 1 and grouping(Path) = 1 then '''' 
        when grouping(FileGroup) = 1 and grouping(Path) = 0 then ''Total for FileGroup'' 
        when grouping(FileGroup) = 0 and grouping(Path) = 1 then ''Total for Drive'' 
        else Path end [Path]
 , sum(Size) [DBSizeMB]
 , sum(SpaceAvailableInMB) [DBAvailMB] 
from admin.dbo.SpaceUsedHistory
where RecCreatedDt > (select max(recCreatedDt) from admin.dbo.SpaceUsedHistory) - 1
and Server = @@servername
group by [Database], FileGroup, Path
with rollup
order by grouping([Database]), [Database], Grouping(FileGroup), FileGroup, grouping(path), Path
') bz