A student in a recent class asked for a script that could take every command a user typed into PowerShell and send it to a csv file for monitoring purposes. That prompted me to think about the choices you have with PowerShell. He didn’t want to capture history after the fact, with a ‘get-history’ command, since the user could easily have run ‘clear-history’ resulting in the removal of the commands they had run to that point. That made me think about what his choices were. His chosen alternative was to send events to the PowerShell event log, however, when monitoring anything, you could also decide to send data a SQL database. My thanks to David W for his question, and his eventual script, which you can see below.
With PowerShell, you can easily export your data to a csv file. For ease of demonstration, I’ll use a simple command: Get-wmiobject win32_bios –computername (get-content servers.txt) | export-csv serverbios.txt. That’s not so hard. But, how do you get PowerShell to take each command, as you type it, and send the command to… well, wherever you choose?
We went to the web and found a script on the PowerShell website that changed the function, ‘prompt’. So now, each time the user hit enter, and PowerShell ran the command and offered the next prompt, the command was sent to a .txt file. Simply modifying the script so that it was sent to a csv file was easy. Here is what the original looked like:
function prompt
{
'PS> '
$Host.UI.RawUI.WindowTitle = Get- Location
if ($global:CmdLogFile) {
Get-History -Count 1 |
Select-Object -ExpandProperty CommandLine |
Out-File $global:CmdLogFile -Append
}
}
To enable logging, set $global:CmdLogFile to a path, like this:
$global:CmdLogFile = " $env:temp\logfile.txt"
The problem with this script is that it doesn’t record the server name, username, data and time. So, the script was modified to log to the Windows PowerShell log. As he pointed out: It simply logs anything the user has typed in. Each time you hit enter in PS, it records a new row in the event log. Nice thing is that it will capture whatever the user typed, whether it be a .ps1 name, a cmdlet name, or a .NET function call. By using an event, we don’t need to put the server name, user name, or date/time in the event description (we get that for free).
function prompt
{
"PS $($executionContext.SessionState.Path.CurrentLocation)$('>' * ($nestedPromptLevel + 1)) "
$InstanceID = $(Get-Host).InstanceId
$xml = "<PSData>`n"
$xml += "<cmd>$(Get-History -Count 1 | Select-Object -ExpandProperty CommandLine)</cmd>`n"
$xml += "<InstanceID>$InstanceID</InstanceID>`n"
$xml += "</PSData>"
Write-EventLog -logname 'Windows PowerShell' -source PowerShell -eventID 9000 -entrytype Information -message $xml -Category 8
}
$auditLogEntry | Add-Member NoteProperty DateTime $date
$auditLogEntry | Add-Member NoteProperty ComputerName $env:COMPUTERNAME
$auditLogEntry | Add-Member NoteProperty UserName $env:USERNAME
$auditLogEntry | Export-Csv 'C:\temp\test.csv' -NoTypeInformation –Append
}
So, that’s pretty cool right? How could that possibly be improved upon you wonder? Ah, well, what about adding the ability to log what the commands of a called PowerShell script is? So, here is the updated version:
function prompt
{
"PS $($executionContext.SessionState.Path.CurrentLocation)$('>' * ($nestedPromptLevel + 1)) "
if($commandtext.Length -gt 0 )
{
$InstanceID = $(Get-Host).InstanceId
$commandtext = Get-History -Count 1 | Select-Object -ExpandProperty CommandLine
$data += "Command Text = $commandtext`n"
$data += "Host ID = $InstanceID`n"
if($commandtext.ToLower().contains('.ps1'))
{
$ps1FileName = $commandtext.Substring(0,$commandtext.indexof(".ps1")) + '.ps1'
$result = test-path $ps1FileName -ErrorAction SilentlyContinue
if($result)
{
$scriptContents = Get-Content $commandtext -ErrorAction SilentlyContinue
$data += "Script Contents = $scriptContents`n"
}
}
Write-EventLog -logname 'Windows PowerShell' -source PowerShell -eventID 9000 -entrytype Information -message $data -Category 8
}
}
Now that’s using the power of PowerShell!
As an alternative, if you aren’t capturing PowerShell commands, but want to monitor, I’d like you to take a look at the blogs of Laerte Junior, a SQL Server MVP. He has blogged on Creating a monitoring server for SQL Server with PowerShell, as well as Gathering Perfmon data with PowerShell.
Here’s to your successful monitoring! You can monitor to a CSV or text file, to a Windows Event Log, or to SQL Server.