SCCM查询:如何获取最新的用户登录注册(如果有)，并显示PC、IP、User、logontimestamp
SCCM查询:如何获取最新的用户登录注册(如果有)，并显示PC、IP、User、logontimestamp
提问于 2009-06-15 18:34:57
我正在尝试创建一个SQL2005查询来检索和组合来自3个SCCM数据视图的记录。第一个视图包含有效PC的记录；第二个视图包含登录信息，其中包含: PC-id、用户名、时间戳等；第三个视图包含PC-id、IP地址。
第一个视图在每台PC上只包含一个单独的记录；第二个视图可以在每个PC上包含多个记录:用户每次登录到计算机时都有一个记录；第三个视图可以在每个PC上包含多个记录:每个IP都已在数据库中注册。
所以它是这样的：
view1 (v_R_System_Valid) fields (among others)
ResourceID, NetBIOS
view2 (v_GS_SYSTEM_CONSOLE_USER) fields (among others)
id,ResourceID,SystemConsoleUser0,LastConsoleUse0
view3 (v_RA_System_IPAddresses) fields (among others)
ResourceID,IP_Addresses0我希望查询在第一个视图中显示所有PC的列表，并显示(可用) IP地址；最近一次登录的时间和登录的人。
到目前为止，我构建的查询返回每台PC，但包括登录到该PC的每个用户和时间，而不仅仅是最新的用户。我希望有人能帮我解决这个问题。我不是一个有经验的SQL脚本编写者，并使用来自网络的信息构建了下面的代码。
我的问题是：
Select
SV.Netbios_Name0 AS [NetBIOS Name],
SCU.SystemConsoleUser0 AS [User Name],
CAST(ISNULL(SCU.theLastTime, 0) AS datetime ) AS [Last Console Use]
from v_R_System_Valid SV
Left Join (Select ResourceID, SystemConsoleUser0,
Max(LastConsoleUse0) as theLastTime
from v_GS_SYSTEM_CONSOLE_USER
group by ResourceID, SystemConsoleUser0)
AS SCU on SCU.ResourceID = SV.ResourceID
where (SV.Netbios_Name0 not like 'ENC-%')
and (SV.Netbios_Name0 not like 'NL%')
order by SV.Netbios_Name0回答 2
Stack Overflow用户
回答已采纳
发布于 2009-06-15 18:57:00
SELECT SV.Netbios_Name0 AS [NetBIOS Name]
, latest_user.SystemConsoleUser0 as [User Name]
, CAST(ISNULL(SCU.theLastTime, 0) AS datetime ) AS [Last Console Use]
, IP.IP_Addresses0
from v_R_System_Valid SV
left outer join
(Select ResourceID
,Max(LastConsoleUse0) as theLastTime
from v_GS_SYSTEM_CONSOLE_USER
group by ResourceID
,SystemConsoleUser0) AS SCU
on SCU.ResourceID = SV.ResourceID
left outer join v_GS_SYSTEM_CONSOLE_USER as latest_user
on latest_user.LastConsoleUse0 = SCU.theLastTime
and
latest_user.ResourceID = SCU.ResourceID
left outer join v_RA_System_IPAddresses as IP
on IP.ResourceID = SV.ResourceID
where (SV.Netbios_Name0 not like 'ENC-%')
and
(SV.Netbios_Name0 not like 'NL%')
order by SV.Netbios_Name0Stack Overflow用户
发布于 2009-06-16 09:40:11
在Adam的帮助下，我得到了这个结果查询：
select SV.Netbios_Name0 AS [NetBIOS Name]
, latest_user.SystemConsoleUser0 as [User Name]
, CAST(ISNULL(SCU.theLastTime, 0) AS datetime ) AS [Last Console Use]
, IP.IP_Addresses0
from v_R_System_Valid SV
left outer join
(Select ResourceID
,Max(LastConsoleUse0) as theLastTime
from v_GS_SYSTEM_CONSOLE_USER
group by ResourceID) AS SCU
on SCU.ResourceID = SV.ResourceID
left outer join v_GS_SYSTEM_CONSOLE_USER as latest_user
on latest_user.LastConsoleUse0 = SCU.theLastTime
and
latest_user.ResourceID = SCU.ResourceID
left outer join v_RA_System_IPAddresses as IP
on IP.ResourceID = SV.ResourceID where (SV.Netbios_Name0 not like 'ENC-%')
and
(SV.Netbios_Name0 not like 'NL%')
and
(IP.IP_Addresses0 like '10.%')
order by SV.Netbios_Name0页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/997619
复制相关文章
相似问题
腾讯云开发者
