如何通过PowerShell跨服务器查询服务器运行时间？求SQL Server实现方案

Great question! Let's break this down into PowerShell best practices first, then cover the SQL Server options you're asking about.

First off, Get-WMIObject is deprecated—Microsoft recommends using Get-CimInstance instead for modern Windows systems. It leverages the Common Information Model (CIM) over WinRM, which is more reliable, secure, and efficient. Here's the improved command with proper error handling and readable output:

$server = "RemoteServerName"
$altcreds = Get-Credential # Use stored credentials safely in production (e.g., from a secure vault)

try {
    # Fetch OS info from remote server
    $osInfo = Get-CimInstance -Class Win32_OperatingSystem -ComputerName $server -Credential $altcreds -ErrorAction Stop
    
    # Convert raw WMI datetime string to a readable format
    $lastBootUpTime = [Management.ManagementDateTimeConverter]::ToDateTime($osInfo.LastBootUpTime)
    
    # Calculate human-readable uptime
    $uptime = (Get-Date) - $lastBootUpTime
    
    # Display results
    Write-Host "Server: $server"
    Write-Host "Last Boot Time: $($lastBootUpTime.ToString('yyyy-MM-dd HH:mm:ss'))"
    Write-Host "Uptime: $($uptime.Days) days, $($uptime.Hours) hours, $($uptime.Minutes) minutes"
}
catch {
    # Show meaningful error messages to diagnose issues
    Write-Error "Failed to query $server : $_"
}

Key Improvements & Troubleshooting: #

• Error visibility: Replaced SilentlyContinue with Stop and a try/catch block to pinpoint why queries fail (e.g., permission denied, server unreachable)
• Readable output: Converts the raw WMI datetime value to a standard date format
• Uptime calculation: Adds a human-friendly uptime breakdown
• Common fixes for no results:
  • Ensure WinRM is enabled on the remote server: Run Enable-PSRemoting -Force (requires admin rights)
  • Verify your credentials have administrative access to the remote server
  • Check firewall rules: Allow inbound WinRM traffic (ports 5985 for HTTP, 5986 for HTTPS)
  • Confirm the Windows Management Instrumentation (WMI) service is running on the remote machine

Yes, you can query remote server uptime via SQL Server, but the approach depends on your security requirements. Here are two practical methods:

Method 1: Stored Procedure with xp_cmdshell (Use Sparingly) #

This runs a PowerShell command directly from SQL, but note that xp_cmdshell is disabled by default for security reasons. Only use this if you can't implement the recommended method below.

Step 1: Enable xp_cmdshell (if necessary) #

-- Enable advanced configuration options
sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Enable xp_cmdshell
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Step 2: Create a Secure Stored Procedure #

CREATE PROCEDURE dbo.GetRemoteServerUptime
    @ServerName NVARCHAR(100),
    @CredentialName NVARCHAR(100) -- Use a SQL credential instead of plain text
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Use SQL credential to run the command as authorized user
    DECLARE @PowerShellCmd NVARCHAR(MAX) = 
        'powershell -Command "$creds = Get-Credential -Credential ''' + @CredentialName + '''; $os = Get-CimInstance Win32_OperatingSystem -ComputerName ''' + @ServerName + ''' -Credential $creds; [Management.ManagementDateTimeConverter]::ToDateTime($os.LastBootUpTime)"'
    
    DECLARE @Result TABLE (LastBootUpTime NVARCHAR(MAX))
    
    -- Execute command and capture results
    INSERT INTO @Result
    EXEC xp_cmdshell @PowerShellCmd
    
    -- Filter out null lines and return clean results
    SELECT LastBootUpTime FROM @Result WHERE LastBootUpTime IS NOT NULL
END

Critical Security Note: #

Never pass plain-text passwords in stored procedures. Instead, create a SQL credential to store secure credentials:

CREATE CREDENTIAL RemoteServerAdmin WITH IDENTITY = 'Domain\AdminUser', SECRET = 'SecurePassword123';

Method 2: SQL Server Agent Job with PowerShell Step (Recommended) #

This is the safest approach because it avoids enabling xp_cmdshell and runs the PowerShell script in a controlled, auditable context:

1. Create a new SQL Server Agent Job
2. Add a PowerShell job step with the script from the PowerShell section above
3. Configure the job to run with credentials that have access to remote servers
4. Optional: Modify the script to write results to a SQL table for reporting

Example script to log results to a table:

$serverList = @("Server1", "Server2")
$altcreds = Get-Credential -Credential "Domain\AdminUser"
$connectionString = "Server=YourSQLServer;Database=MonitoringDB;Integrated Security=True;"

# Create table if it doesn't exist (run once in SQL)
# CREATE TABLE dbo.ServerUptime (Id INT IDENTITY(1,1) PRIMARY KEY, ServerName NVARCHAR(100) NOT NULL, LastBootUpTime DATETIME NOT NULL, UptimeDays INT NOT NULL, RecordedAt DATETIME DEFAULT GETDATE())

foreach ($server in $serverList) {
    try {
        $osInfo = Get-CimInstance -Class Win32_OperatingSystem -ComputerName $server -Credential $altcreds -ErrorAction Stop
        $lastBootUpTime = [Management.ManagementDateTimeConverter]::ToDateTime($osInfo.LastBootUpTime)
        $uptimeDays = ((Get-Date) - $lastBootUpTime).Days
        
        # Insert results into SQL table
        $query = "INSERT INTO dbo.ServerUptime (ServerName, LastBootUpTime, UptimeDays) VALUES ('$server', '$lastBootUpTime', $uptimeDays)"
        Invoke-SqlCmd -ConnectionString $connectionString -Query $query
    }
    catch {
        Write-Error "Failed to query $server : $_"
    }
}

内容的提问来源于stack exchange，提问作者Amnah Khan