Hi All,

I am facing a strange issue in my environment. We have a report being generated on daily basis (SQL) which fetches all the info related to systems. Amongst the various coulmns there is a column 'Last User Logon' & 'Time'. The query is running smoothly for all devices accept a few hundreds of them. All the basic troubleshooting has been done! 

The last doubt is to check the 'wmi' which stores the 'Last Logon User & Time' info of a device and from which SCCM fetchs the data. Can any body please help / guide me in checking the pain area?

Also, please advice what other probably can cause this to happen. Searched almost all forums but couldn't get a proper answer to this. Can anyone help ?

Regards

There are at least three places in CM07/ CM12 that the last logon data is collected/stored, exactly which one are you using?

What troubleshooting steps are you done?

Hello Garth,

Thank You for response. Below are the steps already taken in order to check/resolve the issue:

- Repairing the wmi of issue systems
- Repairing the sccm client on systems
- Rebuilding the systems
- Checked if any firewall is blocking any sort of communication

- Checked if any GPO is restricting some policy

All the above steps have been taken and checked apart from wmi but the settings are all similar for corerct & issue devices.

As far as wmi structure is concerned, I have checked the below classes:

- \\.\root\cimv2\win32_Environment

- \root\ccm\LoggedOnUser

Both the above paths are being reflected in Inventory Agent.log of an issue system. But I fail to understand in which class is the information stored :( 

Also, please let me know the three places in SCCM 2007 which stores the Last Logon User info !

Please suggest.

Regards

• Edited by DEEP DAS 19 hours 40 minutes ago Added a few more lines.

Hello Garth,

On investigating the Inventory.log on an issue device I found something related to UserName which directed to the below wmi class:

\root\cimv2\Win32_ComputerSystem

Object : UserName whose value is Null

Please suggest if I am looking in right direction.

Regards

What exactly does you query look like, without this detail, it is impossible to know which of the three different place you are looking for the data.

The three place are

• Heartbeat discovery
• Computer System
• AI

You could also look at Top console user too.

Hello Garth,

Below is the query:

select distinct
 SYS.Name0 As [Computer],
 SYS.AD_Site_Name0 As [Site],
((sys.User_Domain0 + '\' + sys.User_Name0)) AS [user],
 (SELECT CONVERT(CHAR(11),gs.LastHWScan,103) + CONVERT(CHAR( 5),gs.LastHWScan,114)) as [Heartbeat],
 ' ' as [Logon Count],
(SELECT  TOP 1 IPAddress0  
FROM v_GS_NETWORK_ADAPTER_CONFIGUR as NetCard 
WHERE NetCard.resourceID = sys.resourceID 
AND NetCard.IPAddress0 NOT like 'NULL'
AND DefaultIPGateway0 !=' '  
)               as [Ip Addr],
(SELECT  Top 1 IPSubnet0  
FROM v_GS_NETWORK_ADAPTER_CONFIGUR as NetCard 
WHERE NetCard.resourceID = sys.resourceID 
AND NetCard.IPAddress0 NOT like 'NULL'
AND DefaultIPGateway0 !=' '  
  )  as [Ip Subnet],
 ' ' as [IP Count],
(SELECT CONVERT(CHAR(11),ls.TimeStamp,103) + CONVERT(CHAR( 5),ls.TimeStamp,114)) as 'Last Logon',
ls.UserName0 As 'Last Logged on User' 
from 
 v_R_System SYS 
 join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID 
 join v_Collection COL on FCM.CollectionID = COL.CollectionID 
 join v_GS_WORKSTATION_STATUS gs on SYS.ResourceID = gs.ResourceID 
 Left Outer join v_GS_NETWORK_ADAPTER_CONFIGUR ip on sys.ResourceID=ip.ResourceID
 join v_GS_Computer_System ls on ls.ResourceID=sys.ResourceID
where 
 COL.Name = 'All Systems'
 and (DateDiff(DAY,gs.LastHWScan, GetDate()) < 1)
 and (sys.Client0 not like '%0%'
and sys.Operating_System_Name_and0 like '%NT_Workstation%'
and sys.User_Name0 not like 'NULL'
and ip.IPAddress0 Not like '0.0.0.0'
)
Order By [Computer]

Point to be noted is there are more than 25 k systems in the nevironment but only 200 systems are not returning their 'LAst Logon User' data :|

Regards

• Edited by DEEP DAS 18 hours 25 minutes ago

Hello Garth,

I ran a query mentioned by you in the below link:

http://smsug.ca/blogs/garth_jones/archive/2008/02/22/location-of-last-logon-user.aspx

It has several User Name columns but how to determine which is the Last Logon User. The query returned the result under beloq columns:

Also, can you please let me know what the query does or where it searches for the info as I am not an expert in SQL :( The result has the same 'NULL' value for a few User columns for the systems I am trying to remediate.

Regards

What is Heartbeat and Hardware inventory cycle set to? this will tell you which is the best to use for last logon user.

What do you want the Last logon user vs the top console user?

Hello Garth,

Thank You for response. Below are the steps already taken in order to check/resolve the issue:

- Repairing the wmi of issue systems
- Repairing the sccm client on systems
- Rebuilding the systems
- Checked if any firewall is blocking any sort of communication

- Checked if any GPO is restricting some policy

All the above steps have been taken and checked apart from wmi but the settings are all similar for corerct & issue devices.

As far as wmi structure is concerned, I have checked the below classes:

- \\.\root\cimv2\win32_Environment

- \root\ccm\LoggedOnUser

Both the above paths are being reflected in Inventory Agent.log of an issue system. But I fail to understand in which class is the information stored :( 

Also, please let me know the three places in SCCM 2007 which stores the Last Logon User info !

Please suggest.

Regards

• Edited by DEEP DAS Tuesday, June 23, 2015 11:57 AM Added a few more lines.

Hello Garth,

Thank You for response. Below are the steps already taken in order to check/resolve the issue:

- Repairing the wmi of issue systems
- Repairing the sccm client on systems
- Rebuilding the systems
- Checked if any firewall is blocking any sort of communication

- Checked if any GPO is restricting some policy

All the above steps have been taken and checked apart from wmi but the settings are all similar for corerct & issue devices.

As far as wmi structure is concerned, I have checked the below classes:

- \\.\root\cimv2\win32_Environment

- \root\ccm\LoggedOnUser

Both the above paths are being reflected in Inventory Agent.log of an issue system. But I fail to understand in which class is the information stored :( 

Also, please let me know the three places in SCCM 2007 which stores the Last Logon User info !

Please suggest.

Regards

• Edited by DEEP DAS Tuesday, June 23, 2015 11:57 AM Added a few more lines.

Hello Garth,

Below is the query:

select distinct
 SYS.Name0 As [Computer],
 SYS.AD_Site_Name0 As [Site],
((sys.User_Domain0 + '\' + sys.User_Name0)) AS [user],
 (SELECT CONVERT(CHAR(11),gs.LastHWScan,103) + CONVERT(CHAR( 5),gs.LastHWScan,114)) as [Heartbeat],
 ' ' as [Logon Count],
(SELECT  TOP 1 IPAddress0  
FROM v_GS_NETWORK_ADAPTER_CONFIGUR as NetCard 
WHERE NetCard.resourceID = sys.resourceID 
AND NetCard.IPAddress0 NOT like 'NULL'
AND DefaultIPGateway0 !=' '  
)               as [Ip Addr],
(SELECT  Top 1 IPSubnet0  
FROM v_GS_NETWORK_ADAPTER_CONFIGUR as NetCard 
WHERE NetCard.resourceID = sys.resourceID 
AND NetCard.IPAddress0 NOT like 'NULL'
AND DefaultIPGateway0 !=' '  
  )  as [Ip Subnet],
 ' ' as [IP Count],
(SELECT CONVERT(CHAR(11),ls.TimeStamp,103) + CONVERT(CHAR( 5),ls.TimeStamp,114)) as 'Last Logon',
ls.UserName0 As 'Last Logged on User' 
from 
 v_R_System SYS 
 join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID 
 join v_Collection COL on FCM.CollectionID = COL.CollectionID 
 join v_GS_WORKSTATION_STATUS gs on SYS.ResourceID = gs.ResourceID 
 Left Outer join v_GS_NETWORK_ADAPTER_CONFIGUR ip on sys.ResourceID=ip.ResourceID
 join v_GS_Computer_System ls on ls.ResourceID=sys.ResourceID
where 
 COL.Name = 'All Systems'
 and (DateDiff(DAY,gs.LastHWScan, GetDate()) < 1)
 and (sys.Client0 not like '%0%'
and sys.Operating_System_Name_and0 like '%NT_Workstation%'
and sys.User_Name0 not like 'NULL'
and ip.IPAddress0 Not like '0.0.0.0'
)
Order By [Computer]

Point to be noted is there are more than 25 k systems in the nevironment but only 200 systems are not returning their 'LAst Logon User' data :|

Regards

• Edited by DEEP DAS Tuesday, June 23, 2015 1:12 PM

Hello Garth,

Below is the query:

select distinct
 SYS.Name0 As [Computer],
 SYS.AD_Site_Name0 As [Site],
((sys.User_Domain0 + '\' + sys.User_Name0)) AS [user],
 (SELECT CONVERT(CHAR(11),gs.LastHWScan,103) + CONVERT(CHAR( 5),gs.LastHWScan,114)) as [Heartbeat],
 ' ' as [Logon Count],
(SELECT  TOP 1 IPAddress0  
FROM v_GS_NETWORK_ADAPTER_CONFIGUR as NetCard 
WHERE NetCard.resourceID = sys.resourceID 
AND NetCard.IPAddress0 NOT like 'NULL'
AND DefaultIPGateway0 !=' '  
)               as [Ip Addr],
(SELECT  Top 1 IPSubnet0  
FROM v_GS_NETWORK_ADAPTER_CONFIGUR as NetCard 
WHERE NetCard.resourceID = sys.resourceID 
AND NetCard.IPAddress0 NOT like 'NULL'
AND DefaultIPGateway0 !=' '  
  )  as [Ip Subnet],
 ' ' as [IP Count],
(SELECT CONVERT(CHAR(11),ls.TimeStamp,103) + CONVERT(CHAR( 5),ls.TimeStamp,114)) as 'Last Logon',
ls.UserName0 As 'Last Logged on User' 
from 
 v_R_System SYS 
 join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID 
 join v_Collection COL on FCM.CollectionID = COL.CollectionID 
 join v_GS_WORKSTATION_STATUS gs on SYS.ResourceID = gs.ResourceID 
 Left Outer join v_GS_NETWORK_ADAPTER_CONFIGUR ip on sys.ResourceID=ip.ResourceID
 join v_GS_Computer_System ls on ls.ResourceID=sys.ResourceID
where 
 COL.Name = 'All Systems'
 and (DateDiff(DAY,gs.LastHWScan, GetDate()) < 1)
 and (sys.Client0 not like '%0%'
and sys.Operating_System_Name_and0 like '%NT_Workstation%'
and sys.User_Name0 not like 'NULL'
and ip.IPAddress0 Not like '0.0.0.0'
)
Order By [Computer]

Point to be noted is there are more than 25 k systems in the nevironment but only 200 systems are not returning their 'LAst Logon User' data :|

Regards

• Edited by DEEP DAS Tuesday, June 23, 2015 1:12 PM

Hello Garth,

Both 'Heartbeat' discovery & 'Hardware' inventory cycle are set to run on daily basis. (1 day interval)

The end result we want through the query is 'Last Logon User' and not 'Top Console User'.

Regards

Hello Garth,

I checked the below path for an issue device:

\\systemname\root\cimv2\Win32_Environment

and found the UserName (object) present. On double-clicking there were various other entries.

I thought either this class won't be present or if class is there then UseName object won't be there but it seems otherwise :(

Regards

The Logon details will not come from Win32_Environment, it will come from win32_computersystem or SMS_SystemConsoleUsage or SMS_SystemConsoleUser.

I believed that "The "Last Logon User", comes from heartbeat. The heartbeat query looks in WMI, in select username from win32_computersystem"

Is this incorrect ? :O

Please find the below link where the same is mentioned :

https://social.technet.microsoft.com/Forums/de-DE/f9aae7ae-7cf5-4e9b-95d1-9b795d717c3d/query-device-for-last-user-to-logon?forum=configmanagergeneral

Regards

On the contrary " SMS_SystemConsoleUsage or SMS_SystemConsoleUser" is not the view being used in my query and as I said, the above mentioned query returns the exact info for all the systems in estate apart from a few 100 devices :|

win32_computersystem returns the value of Computer Name as below:

- Win32_ComputerSystem.Name="SystemName"

Wherease

win32_environment returns the value of User Name as below:

- Win32_Environment.Name="TEMP",UserName="UserName"

- Win32_Environment.Name="TMP",UserName="UserName"

and so on...

You can see exactly what is query from the inventoryagent.log on a client computer.

and yes it is correct.

win32_computersystem returns the value of Computer Name as below:

- Win32_ComputerSystem.Name="SystemName"

Wherease

win32_environment returns the value of User Name as below:

- Win32_Environment.Name="TEMP",UserName="UserName"

- Win32_Environment.Name="TMP",UserName="UserName"

and so on...

Respected Garth,

I didn't mean any disrespect :(

Just was clearing my doubts.

But I don't understand that when evrything is in place, what am I missing to check. And why is the data not populating in my sql query!

Regards

None taken, but I still dont understand why you are pointing to environment vars. What does that have to do with anything? How exactly are you logon to the system?