在Log Parser Studio中使用SQL查询解析日志以采集数据

Question

请原谅我的问题，我的SQL知识非常有限。我试图在交换日志中找到匿名连接(我使用的是Log Parser Studio，它批量分析日志，并使用SQL查询解析它们)。在日志中，我看到以下内容：

#Fields: date-time,connector-id,session-id,sequence-number,local-endpoint,remote-endpoint,event,data,context
2020-01-10T01:01:01.111X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,212,192.168.1.1:21111,192.168.1.2:5565,*,,Proxying inbound session with session id 01C1Z111DD1X11Z1
2020-01-10T01:01:01.111X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,213,192.168.1.1:21111,192.168.1.2:5565,>,RSET,
2020-01-10T01:01:01.112X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,214,192.168.1.1:21111,192.168.1.2:5565,<,250 2.0.0 Resetting,
2020-01-10T01:01:01.112X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,215,192.168.1.1:21111,192.168.1.2:5565,>,XPROXYFROM SID=08D7F721DC0D9A14 IP=215,192.168.1.1 PORT=21111 DOMAIN=CONTOSO.COM SEQNUM=1 PERMS=1077 AUTHsrc=Anonymous,
2020-01-10T01:01:01.113X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,216,192.168.1.1:21111,192.168.1.2:5565,<,250 XProxyFrom accepted,
2020-01-10T01:01:01.113X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,217,192.168.1.1:21111,192.168.1.2:5565,*,,sending message with RecordId 151516 and InternetMessageId <j6hd87fh-55h6-66h6-5g55-k9dj47gk704z@VM1203102312.contoso.com>
2020-01-10T01:01:01.113X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,218,192.168.1.1:21111,192.168.1.2:5565,>,MAIL FROM:<test@contoso.com> SIZE=0 AUTH=<> XMESSAGEVALUE=MediumHigh,
2020-01-10T01:01:01.113X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,219,192.168.1.1:21111,192.168.1.2:5565,>,RCPT TO:<receive@contoso.com>,
2020-01-10T01:01:01.115X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,210,192.168.1.1:21111,192.168.1.2:5565,<,250 2.1.0 Sender OK,
2020-01-10T01:01:01.115X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,211,192.168.1.1:21111,192.168.1.2:5565,<,250 2.1.5 Recipient OK,

因此，我需要找到带有"AUTHsrc=Anonymous“的数据，然后只显示"AUTHsrc=Anonymous”所在的会话id中的"IP“、"MAIL FROM”和"RCPT TO“记录。

我设法创建了一个查询，只列出我想要的ID：

SELECT * FROM '[LOGFILEPATH]'
WHERE data LIKE '%AUTHsrc=Anonymous%'

但我不知道如何根据我的发现显示其他记录。我假设我需要一个子查询？

picture for the reference

Answer 1

也许我没有理解您的措辞，但是下面的查询将查找所有具有AUTHsrc=Anonymous的唯一session-id

SELECT DISTINCT session_id FROM '[LOGFILEPATH]'
WHERE data LIKE '%AUTHsrc=Anonymous%'

因此，此查询将选择具有这些session-id中任何一个的所有记录

SELECT * FROM '[LOGFILEPATH]'
WHERE session_id IN (
   SELECT DISTINCT session_id FROM '[LOGFILEPATH]'
   WHERE data LIKE '%AUTHsrc=Anonymous%'
)

放置一些特定的列，而不是*，以减少到IP和您感兴趣的所有其他数据的输出。