选择不同行时提供附加数据
我有一个来自 Active Directory 的登录事件表。其中一种类型是计算机登录,其中包括执行登录的计算机的 IP 地址。这很方便,因为它提供了一种带时间戳的方法来确定给定时间哪台计算机位于哪个 IP 上。我正在尝试构建一个查询,该查询将为我提供机器首次登录 IP 时的时间戳列表(谢谢 DHCP,IP 是可变的)。
如果该机器已持有 IP 地址,则仅返回列表的查询很简单。
SELECT DISTINCT IP
FROM EventStream
WHERE (Machine='$Machine')
我知道“选择不同”是一个非最佳查询,这就是为什么我正在寻找更好的查询。这可能包括子查询,对此我知之甚少。在这种情况下,不提供“Distinct”会返回一个最多包含 2000 行的表,因此大量数据被选择出来但未使用。
我真正想要的是某种方式来表达查询,这样我就可以获得机器首次出现在 IP 地址上的时间戳列表。我可以通过在第一个查询的结果上迭代此查询来在代码中伪造它:
SELECT TOP 1 DateTime
FROM EventStream
WHERE (Machine='$Machine' and IP='$IP')
ORDER BY DateTime
我很确定这两个查询可以组合成一个大统一查询。这是可能的,还是应该坚持使用应用程序逻辑来提供我正在寻找的内容?
I have a table of login events coming from Active Directory. One type of these events are machine logins, which include the IP address of the machine doing the login. This is handy, since it provides a timestamped way to determine what machine was on what IP at a given time. I'm trying to construct a query that'll give me a timestamped list of when a machine first logged in to an IP (thank you DHCP, IP is variable).
The query that just returns the list if IP addresses that machine has held is simple.
SELECT DISTINCT IP
FROM EventStream
WHERE (Machine='$Machine')
I know 'select distinct' is a non-optimal query, which is why I'm looking for something better. This probably includes sub-queries, of which I know very little. Not providing 'Distinct' in this case returns a table with up to 2000 rows, so a lot of data is being selected out and not used.
What I would really like is some way to phrase a query such that I get a time-stamped list of when a machine first showed up on an IP address. I can fake it in code by iterating this query over the results of the first:
SELECT TOP 1 DateTime
FROM EventStream
WHERE (Machine='$Machine' and IP='$IP')
ORDER BY DateTime
I'm pretty sure these two can be combined into a grand-unified-query. Is this possible, or should I stick with application logic to provide what I'm looking for?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
只是为了确认一下,您想查看机器使用过的所有 IP 地址以及每个 IP 地址第一次出现的时间吗?
如果是这样你应该能够做这样的事情:
Just to confirm, you want to see all the IP addresses the machine has used along with the first time it appeared on each IP address?
If so you should be able to do something like this:
虽然有点晚了,但这可以满足您的要求,而无需您先查找 IP,然后循环查找您想要的信息:
这将为您提供 Eventstream 中所有计算机的所有 IP,并仅返回最后一个日期 ( ORDER BY Date DESC),根据日期列。如果您想要第一个日期,只需从订单依据中删除“DESC”即可。
A bit late to the party, but this would do what you want without you needing to first find the IPs and then loop through to find the information you want:
This gives you all IPs for all Machines in Eventstream and returns only the last date (ORDER BY Date DESC) for each, according to the column Date. If you want the first date, just remove 'DESC' from the order by.