Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Run a report to see what system an old user logged on to?

Created: 11 Apr 2014 • Updated: 14 Apr 2014 | 14 comments
This issue has been solved. See solution.

Hello All,

 

     Management is asking for me to pull a report on a old user to see what laptop they used.  The user is no longer with us and i'm not sure if the laptop is on Altiris any longer but it was for some time.  Is it possible to pull a report on historical information to see what laptop a user logged on to?  If so please let me know what report would be best or if a custom report is needed please let me know how to run as I've never ran a report other then the standard ones installed.  Thanks for the help.

Operating Systems:

Comments 14 CommentsJump to latest comment

SK's picture

First check the vUser view to see if that user is still in the db or not.

Then check the Inv_AeX_AC_Primary_User table to see if the user is their. If it is, then you will be able to find out the last machine they logged into enough times for then to be classed as its PU (the table will contain the computers guid).

Also check to see if resource history has been enabled for that data class, because if it is, you should hopefully find traces of that user in that history table too.

There is also an event table for user logons that you can check as well as its corresponding history table if one exists.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

oscarc619's picture

Thanks for the response SK.  How do I check the vUser view?  I went under Manage, Computer and ran a check for the user but he 's no longer listed there.  

SK's picture

By running a SELECT statement against that database object via a sql query window.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

oscarc619's picture

Thanks not a SQL guy but let me  see if I can figure out how to run that.  

SK's picture

SELECT * FROM vUser WHERE

[Name] LIKE '%surname%'

--[Given Name] LIKE '%firstname%'

--[Surname] LIKE '%surname%'

--[Display Name] LIKE '%username%'

 

Enter the relevant values in the %% fields, after running (SELECT * FROM vUser) to see the current format.  Remark (--) the first WHERE cluase once run, and then unremark the second one in order to use it, and so forth.

 

SELECT * FROM Inv_AeX_AC_Primary_User WHERE User LIKE '%surname%'

SELECT * FROM InvHist_AeX_AC_Primary_User WHERE User LIKE '%surname%'

 

SELECT * FROM Evt_AeX_Client_LogOn WHERE User LIKE '%surname%'

 

Settings > All Settings > Ntification Server > Resource and Data Class Settings > Resource History > Resource Data History

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

oscarc619's picture

If I run the first command I do see the user info.  However if I run the following commands I'm getting blank data looking for any users even my account. For the first I tried running using my User name but nothing comes up either. I'm basically compying the commands below and running in a new query and changing the name between the %%.  

 

SELECT * FROM Inv_AeX_AC_Primary_User WHERE User LIKE '%surname%'

SELECT * FROM InvHist_AeX_AC_Primary_User WHERE User LIKE '%surname%'

SELECT * FROM Evt_AeX_Client_LogOn WHERE User LIKE '%surname%'

SK's picture

OK, so you don't have any PU or LogOn data for this user.

Can you please let me know which of the vUser columns I provided are populated for this user?

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

oscarc619's picture

It doesn't look like I have any PU or LogOn info for any of our users.  I tried entering my account and other users in our environment and they all come back with no data.

When I run the vUser command for their user name I get the following columns:

GUID, Name, Domain, Given Name, Surname, Display Name, Office Location, Office Telephone, Email, Job Title, Department, Company, Street Address, City, State, Zip & Country

SK's picture

So running the folloiwng three queries does not bring back any results, correct?

SELECT * FROM Inv_AeX_AC_Primary_User

SELECT * FROM InvHist_AeX_AC_Primary_User

SELECT * FROM Evt_AeX_Client_LogOn

 

However, all columns within the vUser view are populated for the user you are looking for, yes?

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

oscarc619's picture

Yeah so say the user i'm looking for info is Jeff Smith.  When I run the first vUser query It shows the Name is jsmith, Given Name is Jeff and Surname is Smith.

So I start a new query and run the following:

SELECT * FROM Inv_AeX_AC_Primary_User WHERE User LIKE '%jsmith%'

When I run that I get the columns _id, _RescourceGuid, Month, User, Domain, Server Generated, Year

Am I running the command correclty?  

thanks for all the help I do appreciate it.

SK's picture

Yes, looking at my database, the vUser.Name value matches the username values of those three tables.

So you do have data in those tables. just not for the user you are looking for, correct?

 

vUser uses the following two tables for those four columns:

Inv_Global_Windows_Users

Inv_Global_User_General_Details

 

Unfortunately, if the user you are looking for is not in either the PU tables or the LogOn table, then you will not be able to find out which computers they logged onto.

In fact, as the Evt_AeX_Client_LogOn table des not have its history table enabled by default, you will not be able to track all the acual logon instances the user did on all computers.

If the user is found in the PU tables, then you will at least be able to find out the machines that they were classed as logging onto the most during a given month.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

Gavin Sanish's picture

Hi Oscar,

 You can check with the following query 

SELECT     dbo.Evt_AeX_Client_LogOn.Event, dbo.Evt_AeX_Client_LogOn.[User], dbo.Evt_AeX_Client_LogOn.Domain, dbo.Evt_AeX_Client_LogOn.[Login Time], 
                      dbo.vComputer.Name AS [Computer Name], dbo.Evt_AeX_Client_LogOn.Time
FROM         dbo.Evt_AeX_Client_LogOn LEFT OUTER JOIN
                      dbo.vComputer ON dbo.Evt_AeX_Client_LogOn._ResourceGuid = dbo.vComputer.Guid
WHERE     (dbo.Evt_AeX_Client_LogOn.[User] = N'deepak')

 

In this query instead of the [[User] = N'deepak') add [User] = N'old user name')]in the last line you have to add the older user name to find the details.

 

 

--

 

...

G. Gavin Sanish

T : +91 9884877206
&a
SOLUTION
oscarc619's picture

Gavin that did it.  Exactly what I needed.  Thanks so much.

Gavin Sanish's picture

You are most welcome :)

--

 

...

G. Gavin Sanish

T : +91 9884877206
&a