cancel
Showing results for 
Search instead for 
Did you mean: 

Report listing information from "Employees" section

admin_laura
Level 4

I am looking for a report that will list all employees from the "Employees" tab listing:

  1. Dislay name
  2. Employee ID
  3. Email address

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions

Kenneth_Adams
Level 6
Employee Accredited Certified

Oops.  Just noticed a typo. The INNER JOIN is missing a letter 's' at the end of the table name. Here is the correct line:

INNER JOIN tblAddress AS ta

My apologies for the typo.  The entire corrected query is:

SELECT tau.DisplayName
     , tau.EmployeeID
     , ta.Address
FROM tblAddressUser AS tau
INNER JOIN tblAddress AS ta
  ON tau.AddressOwnerID = ta.AddressOwnerID
WHERE ta.AddressTypeID = 1
ORDER BY tau.DisplayName;

Also, I forgot to mention that you must run this query against the CA Customer database.  My apologies for that oversight.

 

View solution in original post

6 REPLIES 6

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

you can pull this from the directory database in SQL

Kenneth_Adams
Level 6
Employee Accredited Certified

Try this:

SELECT tau.DisplayName
     , tau.EmployeeID
     , ta.Address
FROM tblAddressUser AS tau
INNER JOIN tblAddres AS ta
  ON tau.AddressOwnerID = ta.AddressOwnerID
WHERE ta.AddressTypeID = 1
ORDER BY tau.DisplayName;

Note that this query will not tell you if the Monitored Employee has been deactivated.  It only tells you the 3 pieces of information that you requested.

 

Kenneth_Adams
Level 6
Employee Accredited Certified

Oops.  Just noticed a typo. The INNER JOIN is missing a letter 's' at the end of the table name. Here is the correct line:

INNER JOIN tblAddress AS ta

My apologies for the typo.  The entire corrected query is:

SELECT tau.DisplayName
     , tau.EmployeeID
     , ta.Address
FROM tblAddressUser AS tau
INNER JOIN tblAddress AS ta
  ON tau.AddressOwnerID = ta.AddressOwnerID
WHERE ta.AddressTypeID = 1
ORDER BY tau.DisplayName;

Also, I forgot to mention that you must run this query against the CA Customer database.  My apologies for that oversight.

 

admin_laura
Level 4

Ken: Thank you for the logic; I will work with my IT department to test.  It would be fantastic if such a report would be available with the other standard reports.

Regards.

Kenneth_Adams
Level 6
Employee Accredited Certified

Laura;

I suggest you enter the request for such a report into the Ideas forum and then get your fellow CA admins to vote it up so that Symantec's Program Managers will look at your request.

You are not the first person to request such a report and we normally defer to our consulting partners to create a customized report as the requests we've received have always wanted different information.  You're request was so simple (for me, anyway) that it only took a few minutes to write and test.

 

 

EV_Ajay
Level 6
Employee Accredited

Hi admin laura,

Please run following SQL query against CA Customer Database and you will get desire result.

Select tc.CaseID 'DepartmentID',tc.Name 'Department Name',tu.EmployeeID,ta.Address 'EmailID'

From tblAddressUser tu

JOIN tblHistCaseAddressUser th

ON th.AddressOwnerID=tu.AddressOwnerID

JOIN tblCase tc

ON tc.CaseID=th.CaseID AND th.EndDate IS NULL JOIN tblAddress ta ON ta.AddressOwnerID=tu.AddressOwnerID

Where tc.Type=102 AND tu.MonitorStatusID=120 Order by tc.Name

Query Result.jpg