Forum Discussion

admin_laura's avatar
admin_laura
Level 4
11 years ago
Solved

Report listing information from "Employees" section

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.

Compliance Accelerator
Information Governance
Not Applicable
Reporting
  • Kenneth_Adams's avatar
    Kenneth_Adams
    11 years ago

    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.

     

6 Replies

Sort By
Replies have been turned off for this discussion
  • Kenneth_Adams's avatar
    Kenneth_Adams
    Level 6
    11 years ago

    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's avatar
    Kenneth_Adams
    Level 6
    11 years ago

    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's avatar
    admin_laura
    Level 4
    11 years ago

    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's avatar
    Kenneth_Adams
    Level 6
    11 years ago

    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's avatar
    EV_Ajay
    Level 6
    11 years ago

    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