WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Wednesday, March 9, 2011

Putting on your SOX - Services

I had originally set out to publish a few scripts for other DBAs to help make their SOX audits a bit easier.  Currently, my team is going through the process with a third party and they are asking for the services running on a server, and what do they run as.  If you are experienced in Powershell, this is pretty easy.  The call comes down to Get-WMIObject and the win32_service class.  The key thing was adding the "StartName" property which translates to "Log On As", thanks to this site for the help.  You may need to filter on just SQL Services, or a list and filter on that using  -contains or -like.  Hope this helps take the screen shots out of your audit.

$OutputFile = "C:\Powershell\ServerServices.htm"
$title = "<h1> Services SOX2011</h1>"
$title | Out-File $OutputFile

# server list
$arr =  @("Server1"),("Server2")

# Loop for each Path
foreach($Server in $arr)
    "<h2>$Server</h2>" | out-File  $OutputFile -append
    Get-WmiObject -ComputerName $Server -Class "win32_service" |
        Where-Object {$_.State -eq "Running"} |
        Select-Object Name, StartMode, State, Status,StartName |
        ConvertTo-Html |
        Out-File -FilePath $OutputFile -Append
"<h3>GMT Run Date:" + (Get-Date).ToUniversalTime() + "</h3>" | Out-File $OutputFile -Append

1 comment: