使用sql查询分析日志，以在LogParser studio中收集数据

pftdvrlh 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(330)

对不起，我的sql知识非常有限。我试图在exchange日志中找到匿名连接（我使用logparserstudio，它批量分析日志并用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”的数据，然后只显示会话id中存在“authsrc=anonymous”的“ip”、“mail from”和“rcpt to”记录。
我创建了一个查询，只列出了我要查找的那些ID：

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

但我不知道如何根据我的发现显示其他记录。我想我需要一个子查询？
供参考图片

sql

来源：https://stackoverflow.com/questions/61956428/parse-logs-with-sql-query-to-collect-data-in-log-parser-studio

1条答案

按热度按时间

kxxlusnw1#

也许我不明白你的措辞，但下面的查询发现一切都是独一无二的 session-id s与 AUTHsrc=Anonymous ```
SELECT DISTINCT session_id FROM '[LOGFILEPATH]'
WHERE data LIKE '%AUTHsrc=Anonymous%'

所以这个查询将选择所有记录中的任何一个 `session-id` s

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

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

赞(0）回复(0）举报 2021-08-13

我来回答

使用sql查询分析日志，以在LogParser studio中收集数据

1条答案

相关问题

热门标签

最新问答