12-06-2014 11:21 AM
I am looking for a report that will list all employees from the "Employees" tab listing:
Thank you.
Solved! Go to Solution.
12-15-2014 06:28 AM
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.
12-12-2014 02:35 PM
you can pull this from the directory database in SQL
12-15-2014 06:23 AM
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.
12-15-2014 06:28 AM
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.
12-15-2014 06:28 AM
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.
12-15-2014 06:45 AM
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.
12-29-2014 11:05 PM
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