Forum Discussion

tomascorey's avatar
tomascorey
Copper Contributor
Mar 12, 2020

LogParser date-time with another fields

Hello,

           My name is Tomas, I try to combine SELECT With date-time but when I try to organize by group and order I have this error: 

 

Command:

 

PS C:\Program Files (x86)\Log Parser 2.2> .\LogParser.exe "SELECT [#Fields: date-time] as date-time, REVERSEDNS(EXTRACT_PREFIX(remote-endpoint,0,':')) as RemoteSenderDNS, EXTRACT_PREFIX(remote-endpoint,0,':') as RemoteSenderIP, Count(*) as Hits FROM c:\tools\*.log GROUP BY RemoteSenderIP ORDER BY HITS DESC" -i:CSV -nSkipLines:4

 

 

ERROR:

 

 

Error: Semantic Error: SELECT clause field-expression "date-time" is not an aggregate function and does not contain GROUP BY field-expressions

 

 

 

I'm a new with Logparser, I'm trying for 6 days!!!!

 

 

Thanks!

2010
2013
exchange online
Logs

3 Replies

Sort By
  • JeremyTBradshaw's avatar
    JeremyTBradshaw
    Iron Contributor
    Mar 15, 2020

    tomascorey  I'm no expert but I think your raw date-time field values mightn't be recognized datetime format.  I had a look at my only saved LogParser queries, here:

    https://github.com/JeremyTBradshaw/PowerShell/blob/master/LogParser/GetEwsUsers.ps1

     

    I would have copied from other example to come up with this:

    SELECT TO_STRING(TO_TIMESTAMP(EXTRACT_PREFIX(REPLACE_STR([#Fields: datetime],'T',' '),0,'.'), 'yyyy-MM-dd hh:mm:ss'),'yyMMdd') AS Day

    The datetime field in my case is being manipulated by TO_TIMESTAMP and then TO_STRING.  My source logs are EWS logs from Exchange, and I don't know what log types you're looking at, so hopefully this helps.  Mainly what I'm trying to point out is that in your code, you're just grabbing date-time and keeping it as it is, and that seems to not be a recognized datetime format that can be grouped by.

     

    • tomascorey's avatar
      tomascorey
      Copper Contributor
      Mar 25, 2020

      JeremyTBradshaw Thanks for your repply. Finally!, I can make works.

       

      There are logparser code, work for me.

      ./LogParser.exe "SELECT EXTRACT_PREFIX(remote-endpoint,0,':') as IP,REVERSEDNS(EXTRACT_PREFIX(remote-endpoint,0,':')) as Name, COUNT(*) AS Hits, TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'T'), 'yyyy-MM-dd')) AS LogDate from 'C:\Program Files\Microsoft\Exchange Server\V14\TransportRoles\Logs\ProtocolLog\SmtpReceive\*.log' WHERE data LIKE '%EHLO%' GROUP BY LogDate,IP ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -O:CSV >> c:\temp\ReceiveConnectorMailFlow25032020.csv

       

      Best Regards,
      Tomás Esteban Corey

Resources