Query for last logged on user and date

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Query for last logged on user and date

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Query for last logged on user and date

Page: [1]

Message

<< Older Topic Newer Topic >>

Query for last logged on user and date - 8/26/2008 1:11:28 PM

swood

Posts: 474
Score: 5
Joined: 12/6/2001
Status: offline

As many before me, I'm looking for the holy grail of queries to pull the last logged on user and logged on date from our systems. Management wants to see any machines that are not being used.

We pull hardware inventory every 30 minutes so I could use that for the most current info. I'm currently playing with some of the v_GS views. Does anyone have something close to what I'm looking at?

_____________________________

Sandy Wood
Network Admin
Orange County District Attorney

Post #: 1

RE: Query for last logged on user and date - 8/26/2008 5:06:58 PM

gjones

Posts: 824
Score: 50
Joined: 6/5/2001
From: Ottawa, Ontario, Canada
Status: offline

Last logon user
http://smsug.ca/blogs/garth_jones/archive/2007/09/05/580.aspx

Last logon date is not there by default. You could add it but it would take time to work out of the details and to collect the data. Why not look at your AD logs to determine this information.

_____________________________

Garth@enhansoft.com

For a List of my Articles
http://www.myitforum.com/contrib/default.asp?cid=116
Blogs:
http://smsug.ca/blogs/garth_jones/default.aspx
http://myitforum.com/cs2/blogs/gjones/default.aspx

(in reply to swood)

Post #: 2

RE: Query for last logged on user and date - 8/26/2008 5:13:01 PM

swood

Posts: 474
Score: 5
Joined: 12/6/2001
Status: offline

Thanks for the link and the code. We've been using a derivative of that and we can get the last logged in user but have not found a way to pull the time the last user logged into the network on a system.

Our management is looking to get a report that shows the last user and when they logged in last to see how our systems are being utilized. If no one is using some machines we'll move them around.

The problem I see is that the login info (unless we login locally) is really kept on the DCs anyways and is not pulled into ConfigMgr. I suppose I could try to get fancy with pulling the last logged in info from AD via ADSI and them merging it somehow with what we have in ConfigMgr.

Thanks again for the thought!

_____________________________

Sandy Wood
Network Admin
Orange County District Attorney

(in reply to swood)

Post #: 3

RE: Query for last logged on user and date - 8/26/2008 6:55:49 PM

swood

Posts: 474
Score: 5
Joined: 12/6/2001
Status: offline

I do like the query you linked and wondered if you might answer a question for me. I've tweaked your query to allow me to add the Timestamp from the view you were using and it appears to pull the best data for us. However I can't quite figure out how to limit the date / timestamp field to show just the most recent date. Have any idea?

Here's my / your code

SELECT
CS.Name0 AS 'Computer', isnull(CS.UserName0,HCS.UserName0) AS 'User Name' , HCS.TimeStamp
FROM
v_GS_COMPUTER_SYSTEM CS Left outer join v_HS_COMPUTER_SYSTEM HCS on CS.ResourceID = HCS.ResourceID
WHERE
HCS.UserName0 is not NULL
Group by
CS.Name0, CS.UserName0, HCS.UserName0, HCS.TimeStamp
order by
CS.Name0

_____________________________

Sandy Wood
Network Admin
Orange County District Attorney

(in reply to gjones)

Post #: 4

RE: Query for last logged on user and date - 8/27/2008 9:02:25 AM 1 votes

hcortez463

Posts: 780
Score: 62
Joined: 4/8/2005
Status: offline

have you considered SLAT.. its free and does a great job. Im currently using it on my 2003 env.
http://www.systemcentertools.com/security.html

_____________________________

If it Helps, Please rate....

(in reply to swood)

Post #: 5

RE: Query for last logged on user and date - 8/27/2008 9:27:46 AM

gjones

Posts: 824
Score: 50
Joined: 6/5/2001
From: Ottawa, Ontario, Canada
Status: offline

Why not turn this around and find PCs who's information has not changed in 30 days

http://smsug.ca/blogs/garth_jones/archive/2008/08/27/pcs-with-the-no-logon-information-for-30-days.aspx

_____________________________

Garth@enhansoft.com

For a List of my Articles
http://www.myitforum.com/contrib/default.asp?cid=116
Blogs:
http://smsug.ca/blogs/garth_jones/default.aspx
http://myitforum.com/cs2/blogs/gjones/default.aspx

(in reply to swood)

Post #: 6

RE: Query for last logged on user and date - 8/27/2008 1:15:46 PM

swood

Posts: 474
Score: 5
Joined: 12/6/2001
Status: offline

Thanks for the post! I've just discovered this and it looks like it should do exactly what I need! Thanks for the thought.

_____________________________

Sandy Wood
Network Admin
Orange County District Attorney

(in reply to hcortez463)

Post #: 7

RE: Query for last logged on user and date - 8/27/2008 1:16:51 PM

swood

Posts: 474
Score: 5
Joined: 12/6/2001
Status: offline

Thanks - I never thought of it this way. This would be much easier that listing all 900 machines and their login times. It would narrow things down to a manageable list. Thanks for sharing your SQL knowledge!

_____________________________

Sandy Wood
Network Admin
Orange County District Attorney

(in reply to gjones)

Post #: 8