Vista - Powershell/WMI scripting for Disk Usage

I am taking on the task of creating a powershell script maybe combined with
WMI to generate a "report" to show the disk space usage on all the servers on
my network. I also need it to be where i can view the history of the usage
and store it in an SQL database. Then probably use Crystal reports to display
a graphical report of it with graphs and such.

What would be the best way to accomplish this? How hard is it to send the
collected data to the SQL database? TIA

In a nutshell

generate a list of servers in a single column csv file - use server or
something similar for the column heading
use import-csv to read file
pipeline in to get-wmiobject and use $_.Server as the parameter for the
computer name
pipeline into foreach
loop though the disks and use something like
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=SQL1;
Initial Catalog=Test; Integrated Security=SSPI")
$conn.Open()

$cmd = $conn.CreateCommand()

$cmd.CommandText ="INSERT Table1 VALUES ('Server1', 'C:', 456)"
$cmd.ExecuteNonQuery()
$conn.Close()

to insert data into sql. Data Source is the server, Initial catalog is the
table. I set up a test table with 3 columns, server, drive, freespace

may put the connection creation and teardown outside the pipeline for
efficency.

Hope this helps if need more help please post specifics
--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk


"Jonathan P" wrote:

> I am taking on the task of creating a powershell script maybe combined with
> WMI to generate a "report" to show the disk space usage on all the servers on
> my network. I also need it to be where i can view the history of the usage
> and store it in an SQL database. Then probably use Crystal reports to display
> a graphical report of it with graphs and such.
>
> What would be the best way to accomplish this? How hard is it to send the
> collected data to the SQL database? TIA

ok so i have my script which is working great for now...ill tackle the
pulling the servers from AD later...my question tho is how can i keep the
script from outputting a 1 everytime a row is added to the db? simple i know
but its getting me...

the problem is when the executenonquery() executes it always displays a 1
and i need it to do nothing except run



"RichS" wrote:

> In a nutshell
>
> generate a list of servers in a single column csv file - use server or
> something similar for the column heading
> use import-csv to read file
> pipeline in to get-wmiobject and use $_.Server as the parameter for the
> computer name
> pipeline into foreach
> loop though the disks and use something like
> $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=SQL1;
> Initial Catalog=Test; Integrated Security=SSPI")
> $conn.Open()
>
> $cmd = $conn.CreateCommand()
>
> $cmd.CommandText ="INSERT Table1 VALUES ('Server1', 'C:', 456)"
> $cmd.ExecuteNonQuery()
> $conn.Close()
>
> to insert data into sql. Data Source is the server, Initial catalog is the
> table. I set up a test table with 3 columns, server, drive, freespace
>
> may put the connection creation and teardown outside the pipeline for
> efficency.
>
> Hope this helps if need more help please post specifics
> --
> Richard Siddaway
> Please note that all scripts are supplied "as is" and with no warranty
> Blog: http://richardsiddaway.spaces.live.com/
> PowerShell User Group: http://www.get-psuguk.org.uk
>
>
> "Jonathan P" wrote:
>
> > I am taking on the task of creating a powershell script maybe combined with
> > WMI to generate a "report" to show the disk space usage on all the servers on
> > my network. I also need it to be where i can view the history of the usage
> > and store it in an SQL database. Then probably use Crystal reports to display
> > a graphical report of it with graphs and such.
> >
> > What would be the best way to accomplish this? How hard is it to send the
> > collected data to the SQL database? TIA

Could change
$conn.Open()

to
$conn.Open() | out-null
or
[void]$conn.Open()

The problem is that $conn.open() actually returns a value.

"Jonathan P" <JonathanP@discussions.microsoft.com> wrote in message
news:2F301CF1-A179-4891-8A42-1C2EED5D447D@microsoft.com...
> ok so i have my script which is working great for now...ill tackle the
> pulling the servers from AD later...my question tho is how can i keep the
> script from outputting a 1 everytime a row is added to the db? simple i
> know
> but its getting me...
>
> the problem is when the executenonquery() executes it always displays a 1
> and i need it to do nothing except run
>
>
>
> "RichS" wrote:
>
>> In a nutshell
>>
>> generate a list of servers in a single column csv file - use server or
>> something similar for the column heading
>> use import-csv to read file
>> pipeline in to get-wmiobject and use $_.Server as the parameter for the
>> computer name
>> pipeline into foreach
>> loop though the disks and use something like
>> $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=SQL1;
>> Initial Catalog=Test; Integrated Security=SSPI")
>> $conn.Open()
>>
>> $cmd = $conn.CreateCommand()
>>
>> $cmd.CommandText ="INSERT Table1 VALUES ('Server1', 'C:', 456)"
>> $cmd.ExecuteNonQuery()
>> $conn.Close()
>>
>> to insert data into sql. Data Source is the server, Initial catalog is
>> the
>> table. I set up a test table with 3 columns, server, drive, freespace
>>
>> may put the connection creation and teardown outside the pipeline for
>> efficency.
>>
>> Hope this helps if need more help please post specifics
>> --
>> Richard Siddaway
>> Please note that all scripts are supplied "as is" and with no warranty
>> Blog: http://richardsiddaway.spaces.live.com/
>> PowerShell User Group: http://www.get-psuguk.org.uk
>>
>>
>> "Jonathan P" wrote:
>>
>> > I am taking on the task of creating a powershell script maybe combined
>> > with
>> > WMI to generate a "report" to show the disk space usage on all the
>> > servers on
>> > my network. I also need it to be where i can view the history of the
>> > usage
>> > and store it in an SQL database. Then probably use Crystal reports to
>> > display
>> > a graphical report of it with graphs and such.
>> >
>> > What would be the best way to accomplish this? How hard is it to send
>> > the
>> > collected data to the SQL database? TIA

I would use write-Host "what your doing" before each part. This will show
you where the 1 is getting returned.

candidates are:
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.ExecuteNonQuery()
$conn.Close()

Can you paste the output of the entire script/function?

"Jonathan P" <JonathanP@discussions.microsoft.com> wrote in message
news:76478689-ABD7-462A-B867-9884EF119AE6@microsoft.com...
> that did not work on either the $conn.Open() or the close or the
> executenonquery... any ideas?
>
>
>
> "Brandon Shell" wrote:
>
>> Could change
>> $conn.Open()
>>
>> to
>> $conn.Open() | out-null
>> or
>> [void]$conn.Open()
>>
>> The problem is that $conn.open() actually returns a value.
>>
>> "Jonathan P" <JonathanP@discussions.microsoft.com> wrote in message
>> news:2F301CF1-A179-4891-8A42-1C2EED5D447D@microsoft.com...
>> > ok so i have my script which is working great for now...ill tackle the
>> > pulling the servers from AD later...my question tho is how can i keep
>> > the
>> > script from outputting a 1 everytime a row is added to the db? simple i
>> > know
>> > but its getting me...
>> >
>> > the problem is when the executenonquery() executes it always displays a
>> > 1
>> > and i need it to do nothing except run
>> >
>> >
>> >
>> > "RichS" wrote:
>> >
>> >> In a nutshell
>> >>
>> >> generate a list of servers in a single column csv file - use server or
>> >> something similar for the column heading
>> >> use import-csv to read file
>> >> pipeline in to get-wmiobject and use $_.Server as the parameter for
>> >> the
>> >> computer name
>> >> pipeline into foreach
>> >> loop though the disks and use something like
>> >> $conn = New-Object System.Data.SqlClient.SqlConnection("Data
>> >> Source=SQL1;
>> >> Initial Catalog=Test; Integrated Security=SSPI")
>> >> $conn.Open()
>> >>
>> >> $cmd = $conn.CreateCommand()
>> >>
>> >> $cmd.CommandText ="INSERT Table1 VALUES ('Server1', 'C:', 456)"
>> >> $cmd.ExecuteNonQuery()
>> >> $conn.Close()
>> >>
>> >> to insert data into sql. Data Source is the server, Initial catalog
>> >> is
>> >> the
>> >> table. I set up a test table with 3 columns, server, drive, freespace
>> >>
>> >> may put the connection creation and teardown outside the pipeline for
>> >> efficency.
>> >>
>> >> Hope this helps if need more help please post specifics
>> >> --
>> >> Richard Siddaway
>> >> Please note that all scripts are supplied "as is" and with no warranty
>> >> Blog: http://richardsiddaway.spaces.live.com/
>> >> PowerShell User Group: http://www.get-psuguk.org.uk
>> >>
>> >>
>> >> "Jonathan P" wrote:
>> >>
>> >> > I am taking on the task of creating a powershell script maybe
>> >> > combined
>> >> > with
>> >> > WMI to generate a "report" to show the disk space usage on all the
>> >> > servers on
>> >> > my network. I also need it to be where i can view the history of the
>> >> > usage
>> >> > and store it in an SQL database. Then probably use Crystal reports
>> >> > to
>> >> > display
>> >> > a graphical report of it with graphs and such.
>> >> >
>> >> > What would be the best way to accomplish this? How hard is it to
>> >> > send
>> >> > the
>> >> > collected data to the SQL database? TIA
>>
>>

that did not work on either the $conn.Open() or the close or the
executenonquery... any ideas?



"Brandon Shell" wrote:

> Could change
> $conn.Open()
>
> to
> $conn.Open() | out-null
> or
> [void]$conn.Open()
>
> The problem is that $conn.open() actually returns a value.
>
> "Jonathan P" <JonathanP@discussions.microsoft.com> wrote in message
> news:2F301CF1-A179-4891-8A42-1C2EED5D447D@microsoft.com...
> > ok so i have my script which is working great for now...ill tackle the
> > pulling the servers from AD later...my question tho is how can i keep the
> > script from outputting a 1 everytime a row is added to the db? simple i
> > know
> > but its getting me...
> >
> > the problem is when the executenonquery() executes it always displays a 1
> > and i need it to do nothing except run
> >
> >
> >
> > "RichS" wrote:
> >
> >> In a nutshell
> >>
> >> generate a list of servers in a single column csv file - use server or
> >> something similar for the column heading
> >> use import-csv to read file
> >> pipeline in to get-wmiobject and use $_.Server as the parameter for the
> >> computer name
> >> pipeline into foreach
> >> loop though the disks and use something like
> >> $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=SQL1;
> >> Initial Catalog=Test; Integrated Security=SSPI")
> >> $conn.Open()
> >>
> >> $cmd = $conn.CreateCommand()
> >>
> >> $cmd.CommandText ="INSERT Table1 VALUES ('Server1', 'C:', 456)"
> >> $cmd.ExecuteNonQuery()
> >> $conn.Close()
> >>
> >> to insert data into sql. Data Source is the server, Initial catalog is
> >> the
> >> table. I set up a test table with 3 columns, server, drive, freespace
> >>
> >> may put the connection creation and teardown outside the pipeline for
> >> efficency.
> >>
> >> Hope this helps if need more help please post specifics
> >> --
> >> Richard Siddaway
> >> Please note that all scripts are supplied "as is" and with no warranty
> >> Blog: http://richardsiddaway.spaces.live.com/
> >> PowerShell User Group: http://www.get-psuguk.org.uk
> >>
> >>
> >> "Jonathan P" wrote:
> >>
> >> > I am taking on the task of creating a powershell script maybe combined
> >> > with
> >> > WMI to generate a "report" to show the disk space usage on all the
> >> > servers on
> >> > my network. I also need it to be where i can view the history of the
> >> > usage
> >> > and store it in an SQL database. Then probably use Crystal reports to
> >> > display
> >> > a graphical report of it with graphs and such.
> >> >
> >> > What would be the best way to accomplish this? How hard is it to send
> >> > the
> >> > collected data to the SQL database? TIA
>
>

## Connect to the SQL server and the Database
$sql_conn = New-Object system.Data.SqlClient.SqlConnection
$sql_conn.connectionstring = "server=*;database=*;User
ID=*;Password=*;trusted_connection=false;"
$sql_conn.open()

## Only get credentials once and then set the file that lists the server names
$cred=Get-Credential
$servers = Get-Content "servers.txt"

foreach ($server in $servers){

## Get WMI data for each server
$colItems = Get-WmiObject -class "Win32_LogicalDisk" -namespace "root\CIMV2" `
-computername $server -cred $cred | where {$_.DriveType -eq "3"}

foreach ($objItem in $colItems) {

## Store the WMI data in variables

$SystemName = $objItem.SystemName
$VolumeName = $objItem.VolumeName
$Size = $objItem.Size
$FreeSpace = $objItem.FreeSpace
$Name = $objItem.Name
$Description = $objItem.Description

## Store the INSERT query in a variable
$insert_query = "INSERT INTO Server
(SystemName,VolumeName,TotalSize,FreeSpace,Name,Descrp)
VALUES('$SystemName','$VolumeName','$Size','$FreeSpace','$Name','$Description')"
## Add new object to the current database connection and then execute the
query
$execute_query = New-Object System.Data.SqlClient.SqlCommand
$execute_query.connection = $sql_conn
$execute_query.commandtext = $insert_query
$execute_query.executenonquery()

}
}

## Close the connection to the database
$sql_conn.close()

## Connect to the SQL server and the Database
$sql_conn = New-Object system.Data.SqlClient.SqlConnection
$sql_conn.connectionstring = "server=*;database=*;User
ID=*;Password=*;trusted_connection=false;"
$sql_conn.open()

## Only get credentials once and then set the file that lists the server names
$cred=Get-Credential
$servers = Get-Content "\\servers.txt"

foreach ($server in $servers){

## Get WMI data for each server
$colItems = Get-WmiObject -class "Win32_LogicalDisk" -namespace "root\CIMV2" `
-computername $server -cred $cred | where {$_.DriveType -eq "3"}

foreach ($objItem in $colItems) {

## Store the WMI data in variables

$SystemName = $objItem.SystemName
$VolumeName = $objItem.VolumeName
$Size = $objItem.Size
$FreeSpace = $objItem.FreeSpace
$Name = $objItem.Name
$Description = $objItem.Description

## Store the INSERT query in a variable
$insert_query = "INSERT INTO Server
(SystemName,VolumeName,TotalSize,FreeSpace,Name,Descrp)
VALUES('$SystemName','$VolumeName','$Size','$FreeSpace','$Name','$Description')"
## Add new object to the current database connection and then execute the
query
$execute_query = New-Object System.Data.SqlClient.SqlCommand
$execute_query.connection = $sql_conn
$execute_query.commandtext = $insert_query
$execute_query.executenonquery()

}
}

## Close the connection to the database
$sql_conn.close()

Sorry... I meant the output of the script where you are showing the 1s

"Jonathan P" <JonathanP@discussions.microsoft.com> wrote in message
news:12098F14-D304-4669-9219-7C3176C34CA0@microsoft.com...
> ## Connect to the SQL server and the Database
> $sql_conn = New-Object system.Data.SqlClient.SqlConnection
> $sql_conn.connectionstring = "server=*;database=*;User
> ID=*;Password=*;trusted_connection=false;"
> $sql_conn.open()
>
> ## Only get credentials once and then set the file that lists the server
> names
> $cred=Get-Credential
> $servers = Get-Content "\\servers.txt"
>
> foreach ($server in $servers){
>
> ## Get WMI data for each server
> $colItems = Get-WmiObject -class "Win32_LogicalDisk" -namespace
> "root\CIMV2" `
> -computername $server -cred $cred | where {$_.DriveType -eq "3"}
>
> foreach ($objItem in $colItems) {
>
> ## Store the WMI data in variables
>
> $SystemName = $objItem.SystemName
> $VolumeName = $objItem.VolumeName
> $Size = $objItem.Size
> $FreeSpace = $objItem.FreeSpace
> $Name = $objItem.Name
> $Description = $objItem.Description
>
> ## Store the INSERT query in a variable
> $insert_query = "INSERT INTO Server
> (SystemName,VolumeName,TotalSize,FreeSpace,Name,Descrp)
> VALUES('$SystemName','$VolumeName','$Size','$FreeSpace','$Name','$Description')"
> ## Add new object to the current database connection and then execute the
> query
> $execute_query = New-Object System.Data.SqlClient.SqlCommand
> $execute_query.connection = $sql_conn
> $execute_query.commandtext = $insert_query
> $execute_query.executenonquery()
>
> }
> }
>
> ## Close the connection to the database
> $sql_conn.close()

i cant give you the exact output right this min but it looks like:
1
1
1
1
1
1
1
1
1
1
1
1
1
1


a 1 for every disk thats encountered on the server it goes to and list like
above.



"Brandon Shell" wrote:

> Sorry... I meant the output of the script where you are showing the 1s
>
> "Jonathan P" <JonathanP@discussions.microsoft.com> wrote in message
> news:12098F14-D304-4669-9219-7C3176C34CA0@microsoft.com...
> > ## Connect to the SQL server and the Database
> > $sql_conn = New-Object system.Data.SqlClient.SqlConnection
> > $sql_conn.connectionstring = "server=*;database=*;User
> > ID=*;Password=*;trusted_connection=false;"
> > $sql_conn.open()
> >
> > ## Only get credentials once and then set the file that lists the server
> > names
> > $cred=Get-Credential
> > $servers = Get-Content "\\servers.txt"
> >
> > foreach ($server in $servers){
> >
> > ## Get WMI data for each server
> > $colItems = Get-WmiObject -class "Win32_LogicalDisk" -namespace
> > "root\CIMV2" `
> > -computername $server -cred $cred | where {$_.DriveType -eq "3"}
> >
> > foreach ($objItem in $colItems) {
> >
> > ## Store the WMI data in variables
> >
> > $SystemName = $objItem.SystemName
> > $VolumeName = $objItem.VolumeName
> > $Size = $objItem.Size
> > $FreeSpace = $objItem.FreeSpace
> > $Name = $objItem.Name
> > $Description = $objItem.Description
> >
> > ## Store the INSERT query in a variable
> > $insert_query = "INSERT INTO Server
> > (SystemName,VolumeName,TotalSize,FreeSpace,Name,Descrp)
> > VALUES('$SystemName','$VolumeName','$Size','$FreeSpace','$Name','$Description')"
> > ## Add new object to the current database connection and then execute the
> > query
> > $execute_query = New-Object System.Data.SqlClient.SqlCommand
> > $execute_query.connection = $sql_conn
> > $execute_query.commandtext = $insert_query
> > $execute_query.executenonquery()
> >
> > }
> > }
> >
> > ## Close the connection to the database
> > $sql_conn.close()
>
>