Why does the SQL Server DBA learn PowerShell?
From: http://www.simple-talk.com/ SQL /database-administration/why-this-sql-server-dba-is-learning-powershell/
Joe. TJ is used only for the purpose of disseminating information.
Windows Management Instrumentation (WMI)Task
My next task is to quickly view the free space on all my services. To complete the task, I have to step into the WMI world, which provides an object model to expose the data of services or applications running on your machine. The first obstacle here is to find out what WMI provides. As long as you start looking for it, you will realize that the WMI Object Model is huge. You need to spend some time browsing it and finding out what the object can do. MSDN is the best place to get WMI class documents.
Browse Win32 WMIClass
The most useful WMI class for a DBA is the Win32 class. You can use the following line of code to obtain all available win32 classes.
# Browse Win32 WMI classes
get-wmiobject -list | where {$_.name -like "win32*"} | Sort-Object
The interesting classes I found at first were:
Win32_LogicalDisk-displays the current status of your disk drive
Win32_QuickFixEngineering-enumeration of all installed fixes on the computer
The example below will focus on using them and some other interesting classes.
Check disk space
The simplest way to check disk space is to use the Win32_LogicalDisk class. DriveType = 3 indicates all local disks. Win32_LogicalDisk does not display the mount point information.
# Check disk space on local disks on local server
Get-WmiObject -Class Win32_LogicalDisk -Filter "DriveType=3"
# Check disk space on a remote server.
Get-WmiObject -Class Win32_LogicalDisk -Filter "DriveType=3" –ComputerName ServerName
After further exploration, it is better to use the PerfFormattedData_PerfDisk_LogicalDisk class and its attributes PercentFreeSpace instead of calculating the percentage of free space from the Win32_LogicalDisk output. In the example below, I checked the drive with free space less than 20%. Another reason for using this class to check disk space is that it displays the mount point information.
# Checking disk space
foreach ($svr in get-content "C:\AllServers.txt")
{
$svr; Get-WmiObject Win32_PerfFormattedData_PerfDisk_LogicalDisk -ComputerName $svr | where{$_.Name -ne "_Total" -and $_.PercentFreeSpace -lt 20} | select-object Name, PercentFreeSpace | format-list
}
# Local computer example
Get-WmiObject -class Win32_PerfFormattedData_PerfOS_Processor -Property Name,PercentProcessorTime | where{$_.Name -eq "_Total"}
Check what services are running on the server
To find out which services are running on a remote server, use Get-WmiObject. Specify the Win32_Service class, specify the properties you want to list, and finally specify the computer you want to query. In the following example, I only want to query the service name.
# Checking what services are running on a computer.
get-wmiobject win32_service –computername COMPUTER | select name
IISYour SQLIs it running on the server? Oh, No!
If you want to check whether a specific service is running on a remote machine, you can use the Filter parameter and specify the service name. Shows the output.
# Is IIS running on your server?
get-wmiobject win32_Service -computername COMPUTER -f "name='IISADMIN'"
gwmi win32_Service –co COMPUTER -f "name='IISADMIN'"
--------------------------------------------
If it is reproduced or referenced, keep the following content:
Joe's Blog: http://www.cnblogs.com/Joe-T/