Archive for the ‘Admin’ category

Disk information via PowerShell & WMI

December 12, 2008

Sorry for taking so much time off in between posts. Lot’s of stuff happening. So, without further waiting, here we go – the long-promised Disk Info via PowerShell & WMI.

Note, there is a filter being applied to Get-WMIObject [“Drive Type = 3”] is a local disk drive.

##Create ServerList.txt file:
sqlcmd /Lc > F:/temp/ServerList.txt

#Populate $Server_List
$Server_List = Get-Content “F:\Temp\ServerList.txt”

##Loop through $Server_List & print disks with (% FreeSpace) < X: # foreach ($row in $Server_List) { $colItems = Get-WmiObject -computername $row -class Win32_LogicalDisk -filter "DriveType = 3" Write-Host "Checking: "$row "...." Write-Host foreach ($objItem in $colItems) { [decimal]$Size = [math]::Round( (($objItem.Size) / 1.00gb) ,2) # cast to [decimal] or [long] due to [decimal]$FreeSpace = [math]::Round( (($objItem.FreeSpace) / 1.00gb) ,2) # PSH inability to cope [decimal]$Percent = [math]::Round( (($FreeSpace / $Size) * 100.00) ,2) # with UInt64 types coming at it. #if ($Percent -le 90) #{ Write-Host " Mount Point: " $objItem.DeviceID Write-Host " Volume Name: " $objItem.VolumeName Write-Host " Total Size: " $Size "GB" Write-Host " Free Space: " $FreeSpace "GB" Write-Host Write-Host " Percent Free: " $Percent"%" Write-Host #} } Write-Host Write-Host }[/sourcecode]

Powershell – Read list & get service information.

May 23, 2008

This is a PowerShell script that will use SQLCMD to generate a list of SQL Servers on your network, piping the reults into a file.  A “classic” database connection is made and the list is processed using a PSH foreach loop calling Get-WMI in order to retrieve SQL Service information via WMI. As the service information is retrieved for each server in the file (that we can connect to) it is stored in an object and inserted into a table in the database specified in the connection string. The table is fairly simple to construct, but is included at the base of the script, commented out.

This script will prompt for network credentials at startup.  This is neccessary in my enviroment as we have different accounts for higher level access.  So we end up passing credentials quite a bit.  This isn’t really as big of an issue as you might think.  By using a scheduler service we can specify accounts to runas, or take advantage of proxies on SQL Agent cmdshell jobs…

I think this example shows a decent example of using a database to store results from an object.

As I go along, I’ve got more to show, especially regarding my comment about the ‘classic’ database connection.  This is just a quick little something to show how you can iterate a list of servers, get some good information about thier services and store it permanantly in a database for central management and reporting. 

## ServiceInfo.ps1
## G. Rayburn 05/08/2007
## Generate list of SQL Servers on the network and
## iterate through them to determine service information.
## The script will insert into a table called Server_Info
## on the server specified in the connection string.

##Create ServerList.txt file:
sqlcmd /Lc > ServerList.txt

##Connect to DBServer:
$sql_conn = New-Object system.Data.SqlClient.SqlConnection
$sql_Conn.connectionstring = "server=;database=Admin;trusted_connection=true"

#Get network credentials & populate $servers
$servers = Get-Content "ServerList.txt"

##Loop through $servers and insert into table.
foreach ($server in $servers)
write-host "Checking: " $server"...."

$colItems = Get-WmiObject -computername $server -cred $cred Win32_Service -filter "Name LIKE '%SQL%'"

foreach ($objItem in $colItems)
$SystemName = $server
$DisplayName = $objItem.DisplayName
$StartName = $objItem.StartName
$StartMode = $objItem.StartMode
$State = $objItem.State

$insert_query = "INSERT INTO dbo.Server_Info
(SystemName, DisplayName, StartName, StartMode, State)
VALUES ('$SystemName', '$DisplayName', '$StartName', '$StartMode', '$State')"

$execute_query = New-Object System.Data.SqlClient.SqlCommand
$execute_query.connection = $sql_conn
$execute_query.commandtext = $insert_query
$execute_query.executenonquery() | out-null

CREATE TABLE dbo.Server_Info
, SystemName sysname
, DisplayName varchar(128)
, StartName varchar(128)
, StartMode varchar(64)
, [State] varchar(32) )


** I apologize for the formatting. I will have to see if I can figure out the CSS and how to expand the size of the post column.