The following command for a Get-WinEvent command works:
PowerShell:
Get-WinEvent -FilterHashtable @{Logname=’application’;StartTime="2018-04-20 00:00:00";EndTime="2018-04-20 23:59:59"}
CMD:
powershell.exe -noprofile -command "Get-WinEvent -FilterHashtable @{Logname=\"application\";StartTime=\"2018-04-18 17:42:56\";EndTime=\"2018-04-19 17:43:01\"}"
The problem I'm facing is that when I try to pipe the results to get a specific RecordID, it works in PowerShell:
Get-WinEvent -FilterHashtable @{Logname=’application’;StartTime="2018-04-20 00:00:00";EndTime="2018-04-20 23:59:59"} | ? {$_.RecordId -eq 293116} | Select Message
When I try to add the same thing to my cmd call, it returns no results:
powershell.exe -noprofile -command "Get-WinEvent -FilterHashtable @{Logname=\"application\";StartTime=\"2018-04-18 17:42:56\";EndTime=\"2018-04-19 17:43:01\"} | ? {$RecordId -eq 293116} | Select Message"
I've already tried replacing the ? with Where-Object and -property to rule out an issue with CMD characters being misinterpreted. I feel like I'm missing something obvious here.
I was able to resolve this.
Because I was going from SQL to CMD to PowerShell, there were some characters that were being interpreted in a way I wasn't expecting and no message was returned. Ultimately I was able to write this stored procedure that will call a specific machine from the SQL host using xp_cmdshell that will give you the event viewer logs for a specific time range.
I've also put an override for number of records to avoid it running for a long time. This is the content of the stored procedure:
if object_id('tempdb..#result') IS NOT NULL
drop table #result
create table #result
([ID] int
,[Timegenerated] datetime
,[EntryType] varchar(max)
,[Source] varchar(max)
,[Message] nvarchar(max)
)
if object_id('tempdb..#temp') IS NOT NULL
drop table #temp
create table #temp
([output] nvarchar(max))
Declare @command nvarchar(1000)
Set @command = 'powershell.exe -noprofile -command "Get-WinEvent -FilterHashtable @{Logname=''application'';StartTime='''+@StartTime+''';EndTime='''+@EndTime+'''} -MaxEvents ' + @topEvents + '| % {\"$($_.RecordId)`t$($_.TimeCreated)`t$($_.ProviderName)`t$($_.LevelDisplayName)\"}"'
Insert Into #temp
exec xp_cmdshell @command
Delete from #temp where [output] is null
Declare @fullstring nvarchar(max)
While (Select COUNT(*) from #temp) > 0
BEGIN
Select TOP 1 @fullstring = [output] from #temp
Insert Into #result
SELECT TOP 1 dbo.F_ExtractSubString([output], 1, ' ') AS ID,
dbo.F_ExtractSubString([output], 2, ' ') AS TimeGenerated,
dbo.F_ExtractSubString([output], 3, ' ') AS EntryType,
dbo.F_ExtractSubString([output], 4, ' ') AS Source,
Null
FROM #temp
Delete From #temp where [output] = @fullstring
END
Declare @IDSearch nvarchar(100), @messageString NVARCHAR(MAX)
While (Select COUNT(*) from #result Where #result.[Message] is null) > 0
BEGIN
Select Top 1 @IDSearch = ID from #result Where #result.[Message] is null
Set @command = 'powershell.exe -noprofile -command "Get-WinEvent -FilterHashtable @{Logname=''application'';StartTime='''+@StartTime+''';EndTime='''+@EndTime+'''} | ? {$_.RecordId -eq ' + @IDSearch + '} | % {\"$($_.Message)`r`n\"}'
Insert Into #temp
exec xp_cmdshell @command
Set @messageString = STUFF(
( SELECT ',' + CONVERT(NVARCHAR(Max), [output])
FROM #temp
FOR xml path('')
)
, 1
, 1
, '')
Update #result Set [Message] = @messageString Where ID = @IDSearch
TRUNCATE TABLE #temp
END
Select * from #result