powershell write output table format

       
       
   


$data1="C"
$data2="OS"
$data3="187768.00"
$data4="143228.00"
$data5="44540.00"
$data6= "23.72"

New-Object psobject -Property ([ordered]@{data1=$data1;data2=$data2;data3=$data3;data4=$data4})

#Write-Host "$data1         $data2             $data3           $data4       $data5      $data6 "




$data1="D"
$data2="DATA"
$data3="267348.00"
$data4="207608.00"
$data5="44540.00"
$data6= "23.72"

#Write-Host "$data1         $data2             $data3           $data4       $data5      $data6  "

           
    New-Object psobject -Property ([ordered]@{data1=$data1;data2=$data2;data3=$data3;data4=$data4})

Powershel to read TSQL

function sql($sqlText, $database = "master", $server = ".")
{
    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database");
    $cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $connection);

    $connection.Open();
    $reader = $cmd.ExecuteReader()

    $results = @()
    while ($reader.Read())
    {
        $row = @{}
        for ($i = 0; $i -lt $reader.FieldCount; $i++)
        {
           # $row[$reader.GetName($i)] = $reader.GetValue($i)

           write-host $reader.GetValue($i)



        }
        $results += new-object psobject -property $row          
    }
    $connection.Close();

    $results
}



$squery =Get-Content "e:\pws\Drivespace1.sql"
write-host $squery
sql $squery "master" "WIN-SHFP3JMVG4E\SQLSERVER3"

Read windows drives info without SysAdmin permssion

if  @@microsoftversion / power(2, 24) >= 9
begin

declare @oleautomationprocedures bit
declare @isshowadvancedoptions bit


select @isshowadvancedoptions = convert(int, isnull(value, value_in_use))
from  sys.configurations
where  name = 'show advanced options'
if @isshowadvancedoptions = 0
begin
exec sp_configure 'show advanced options', 1;
reconfigure;
end


select @oleautomationprocedures = convert(int, isnull(value, value_in_use))
from  sys.configurations
where  name = 'ole automation procedures' ;
if @oleautomationprocedures = 0
begin
exec sp_configure 'ole automation procedures', 1;
reconfigure;
end

end

set nocount on
declare @hr int;
declare @fso int;
declare @driveletter char(1);
declare @odrive int;
declare @totalsize varchar(20);
declare @volumename nvarchar(256);
declare @mb numeric;
set @mb = 1048576;
set @driveletter = '';

if object_id('tempdb..#fixeddrives') is not null
drop table #fixeddrives;

create table #fixeddrives (
[Drive] varchar(10) primary key,
[Drive Label] nvarchar(256),
[Total Capacity MB] numeric(10,2),
[Space Used MB] numeric(10,2),
[Free Space MB] numeric(10,2),
[% Free] numeric(10,2)
);

insert into #fixeddrives([Drive], [Free Space MB])
exec master..xp_fixeddrives;

exec @hr=sp_OACreate 'scripting.filesystemobject',@fso out
if @hr <> 0 exec sp_oageterrorinfo @fso

while exists (select 1 from #fixeddrives d where d.Drive > @driveletter)
begin
select top 1 @driveletter = d.Drive
from #fixeddrives d
where d.Drive > @driveletter
order by d.Drive asc

exec @hr = sp_OAMethod @fso,'getdrive', @odrive out, @driveletter;
if @hr <> 0 exec sp_oageterrorinfo @fso;

exec @hr = sp_OAGetProperty @odrive,'totalsize', @totalsize out;
if @hr <> 0 exec sp_oageterrorinfo @odrive;

exec @hr = sp_OAGetProperty @odrive,'volumename', @volumename out;
if @hr <> 0 exec sp_oageterrorinfo @fso;

update #fixeddrives
set [Total Capacity MB]= @totalsize/@mb ,
[Drive Label]= @volumename
where
Drive = @driveletter;

end

exec @hr=sp_OADestroy @fso;

if @hr <> 0
exec sp_oageterrorinfo @fso;

update #fixeddrives
set [Space Used MB]= [Total Capacity MB] - [Free Space MB],
[% Free] = [Free Space MB]* 100/[Total Capacity MB];

select [Drive]
, [Drive Label]
, [Total Capacity MB]
, [Space Used MB]
, [Free Space MB]
, [% Free]
from #fixeddrives


if  @@microsoftversion / power(2, 24) >= 9
begin
if (select convert(int, isnull(value, value_in_use)) from sys.configurations
where  name = 'ole automation procedures') <> @oleautomationprocedures
begin
exec sp_configure 'ole automation procedures', @oleautomationprocedures;
reconfigure;
end

if (select convert(int, isnull(value, value_in_use)) from sys.configurations
where  name = 'show advanced options') <> @isshowadvancedoptions
begin
exec sp_configure 'show advanced options', @isshowadvancedoptions
reconfigure with override
end

end

data loss

Tdata_loss = last_commit_timeprimary – last_commit_timesecondary

;WITH UpTime AS

(

SELECT DATEDIFF(SECOND,create_date,GETDATE()) [upTime_secs]

FROM sys.databases

WHERE name = 'tempdb'

),

AG_Stats AS

(

SELECT AR.replica_server_name,

   HARS.role_desc,

   Db_name(DRS.database_id) [DBName],

   CAST(DRS.log_send_queue_size AS DECIMAL(19,2)) log_send_queue_size_KB,

   (CAST(perf.cntr_value AS DECIMAL(19,2)) / CAST(UpTime.upTime_secs AS DECIMAL(19,2))) / CAST(1024 AS DECIMAL(19,2)) [log_KB_flushed_per_sec]

FROM   sys.dm_hadr_database_replica_states DRS

INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id

INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id

AND AR.replica_id = HARS.replica_id

--I am calculating this as an average over the entire time that the instance has been online.

--To capture a smaller, more recent window, you will need to:

--1. Store the counter value.

--2. Wait N seconds.

--3. Recheck counter value.

--4. Divide the difference between the two checks by N.

INNER JOIN sys.dm_os_performance_counters perf ON perf.instance_name = Db_name(DRS.database_id)

AND perf.counter_name like 'Log Bytes Flushed/sec%'

CROSS APPLY UpTime

),

Pri_CommitTime AS

(

SELECT replica_server_name

, DBName

, [log_KB_flushed_per_sec]

FROM AG_Stats

WHERE role_desc = 'PRIMARY'

),

Sec_CommitTime AS

(

SELECT replica_server_name

, DBName

--Send queue will be NULL if secondary is not online and synchronizing

, log_send_queue_size_KB

FROM AG_Stats

WHERE role_desc = 'SECONDARY'

)

SELECT p.replica_server_name [primary_replica]

, p.[DBName] AS [DatabaseName]

, s.replica_server_name [secondary_replica]

, CAST(s.log_send_queue_size_KB / p.[log_KB_flushed_per_sec] AS BIGINT) [Sync_Lag_Secs]

FROM Pri_CommitTime p

LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName]