PowerShell vs CMD same command having different results

Question

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.

Put the PowerShell you want to run in a script, and run it using `powershell.exe -File`. — Bill_Stewart, Apr 21 '18 at 15:13
The powershell commands are generated dynamically and are not centralized so I cannot call a file. I did figure out a work around for this though — Xanderu, Apr 23 '18 at 16:12
There are workarounds for that (examples: Write a temporary disk file, run it, then delete it afterwards; use `-EncodedCommand`, etc.). — Bill_Stewart, Apr 23 '18 at 18:14

score 0 · Answer 1 · edited Sep 09 '18 at 12:55

0

Instead of escaping all the inner double quotes, use single ones:

powershell.exe -nop -c "Get-WinEvent -FilterHashtable @{Logname=’application’;StartTime=’2018-04-18 17:42:56’;EndTime=’2018-04-19 17:43:01’} | ? {$RecordId -eq 293116} | Select Message"

Aside from the here nonexistent RecordId, it works.

edited Sep 09 '18 at 12:55

Peter Mortensen

30,738
21
105
131

answered Apr 20 '18 at 23:50

I am calling this command from SQL using xp_cmdshell (I have a reason for it) which means I cant use ' because SQL will see it as the termination of the string. – Xanderu Apr 21 '18 at 01:50
I was able to figure it out by escaping it in SQL. I'll post the results when I check it more – Xanderu Apr 23 '18 at 16:12

score 0 · Accepted Answer · edited Sep 09 '18 at 12:57

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

PowerShell vs CMD same command having different results

2 Answers2