PureConnect

Discussion Thread View
Expand all | Collapse all

Where is the Agent Location data stored in the database

  • 1.  Where is the Agent Location data stored in the database

    GCAP Member
    Posted 08-28-2019 23:11
    Hi all,

    I want to be able to split my agent level reporting by the agent's location.
    I see there is a field in the User Configuration in Interaction Administrator called "Location".
    Where is this field saved in the database?
    #Reporting/Analytics

    ------------------------------
    Jayden Bradow
    Service Performance Analyst
    Precision Administration Services (Pty) Ltd
    ------------------------------


  • 2.  RE: Where is the Agent Location data stored in the database

    GCAP Member
    Posted 08-29-2019 00:19
    This configuration data (and most configuration data) is held in the IC server registry/directory services rather than the database.

    ------------------------------
    Aaron Lael
    State of Utah
    ------------------------------



  • 3.  RE: Where is the Agent Location data stored in the database

    GCAP Member
    Posted 08-29-2019 01:55
    Noo I was really hoping that would not be the case!

    Is there anything else available in the database that would allow me to identify which location/site the agents are based at, or would I need to look to another source outside of PureConnect for this data?

    ------------------------------
    Jayden Bradow
    Service Performance Analyst
    Precision Administration Services (Pty) Ltd
    ------------------------------



  • 4.  RE: Where is the Agent Location data stored in the database

    Top 25 Contributor
    Posted 08-29-2019 10:23
    Jayden,

    To the best of my knowledge this is not stored anywhere within the database. I think you are going to have to create a supplemental table and the store some sort of mapping info to map agents to sites. Also keep in mind that the location info in CIC is really more of a logical grouping of devices for routing purposes although some customers do choose to create locations for each office or some other type of logical grouping.

    Thanks,

    ------------------------------
    Mark Tatera
    ConvergeOne

    Opinions are my own and not the views of my employer. Any suggestions or programming changes I suggest come with no warranty and should be tried at your own risk.
    ------------------------------



  • 5.  RE: Where is the Agent Location data stored in the database

    GENESYS
    Posted 08-29-2019 16:29
    Ok, so you could work with Custom Handlers to add the agent's location as an attribute to the call (there are tools that can retrieve the location from the user object).

    Once there, you can add the value of the attribute to the custom columns of the reporting database.

    Finally, you can modify the report to show the information as desired.

    Genesys University already has a class in writing Handlers (5 days) and will soon be launching a class on Custom Reporting that will cover how to do the above.

    I hope this helps.

    ------------------------------
    Paul Simpson
    Senior Technical Instructor
    ------------------------------



  • 6.  RE: Where is the Agent Location data stored in the database

    Top 25 Contributor
    Posted 08-30-2019 10:13
    Edited by Trent Vance 08-30-2019 10:14
    Jayden -

    Unfortunately, you've been given false information on this topic.

    Not only can the Location information be stored in the database with OOB tools (ie. a checkbox versus custom handlers/registry trickery/supplemental database objects), all user configuration information can be stored as well.  This includes licenses, permissions, access control, etc.  Furthermore, if a change is made to any of these entries, such as Location, the database will track previous and new values.

    Here is an example where I have already done this, for my 'trent.vance' test user.  This morning, I changed that user location from "<Default Location>" to "Indianapolis":


    If you like, feel free to reach out and I can walk you through this fairly simple operation.  The query can also be updated to dynamically group/sort by the latest location value for your users (so when a user does move locations, your reports will always show the most accurate Location level reporting data).

    Thanks!

    ------------------------------
    Trent Vance
    Senior Contact Center Consultant, Reporting & Analytics
    Avtex
    ------------------------------



  • 7.  RE: Where is the Agent Location data stored in the database

    Posted 08-30-2019 10:24

    Hi Trent, this sounds great. I would be interested in understanding how this can be achieved.

    Regards



    ------------------------------
    Vineet Kakroo
    FIL Investment Management Ltd
    ------------------------------



  • 8.  RE: Where is the Agent Location data stored in the database

    GENESYS
    Posted 08-30-2019 11:46
    Edited by Paul Simpson 08-30-2019 12:59
    Hey Trent,

    I assume you are using the Audit trail for this? Interesting approach ;-)

    Unless you know some weird IC Voodoo on this, I don't believe you can get that to show up in reports (the original requirement) without customization?

    Also, I assume that this isn't something that changes often, so what happens when the Agent's location change ages out of the Audit Table?

    ------------------------------
    Paul Simpson
    Senior Technical Instructor
    ------------------------------



  • 9.  RE: Where is the Agent Location data stored in the database

    Top 25 Contributor
    Posted 09-03-2019 07:39
    All good questions.  Here goes:

    1.  If by Audit Trail you are referring to the Enhanced Interaction Administrator Change Notification Log, then yes.  Originally, that feature tracked the Users, Workgroups, Skills, and Licenses nodes in IA at a very granular level.  Later, the Default User and Roles nodes were added.  It isn't enabled by default though.  So to get this information, it needs to be enabled.  When enabling, per documentation, enable after hours!

    2. Correct.  Turning the feature on allows the PureConnect system to take a "snapshot" of those nodes (mentioned above) and their values "right now".  It can be, depending on system and database resources, a bit of an intensive task.  So again, be sure to do so after hours.  Once complete though, reports can be customized, new reports can be created, or BI tools can be used to extract this information and display accordingly.

    3. Along with this feature, the date modified, previous value, and new value of that field (along with countless others) will be tracked.  When creating the report/query, always look to the most recent value.  Also, ranges can be employed here in your BI tools.  So you'll always know that Agent01 was located in Daly City from Jan01 through May01, then relocated to Indianapolis.  Using ranges allows values to be associated to the correct Location at the correct time.

    We do something similar with our licensing reports/audits we've created.


    ------------------------------
    Trent Vance
    Avtex
    ------------------------------



  • 10.  RE: Where is the Agent Location data stored in the database

    GENESYS
    Posted 09-03-2019 12:26
    Edited by Paul Simpson 09-03-2019 13:00
    Hey Trent,

    A very interesting feature!

    I do have one question / concern remaining, though. I see that the Report Log configuration still has a retention setting (default to 400 days). I checked out the docs etc, and couldn't see specific mention of this log, so I have to assume it works the same as the others? If so, surely any changes to a user would disappear from the database after 400 days, so any User who move location in that time period would no longer have their location stored in the database? Or am I missing something here?

    ------------------------------
    Paul Simpson
    Senior Technical Instructor
    ------------------------------



  • 11.  RE: Where is the Agent Location data stored in the database

    Top 25 Contributor
    Posted 09-03-2019 12:56
    Correct.  The default retention period and functionality works the same way for this report log as it does for any of the others.

    ------------------------------
    Trent Vance
    Avtex
    ------------------------------



  • 12.  RE: Where is the Agent Location data stored in the database

    GENESYS
    Posted 09-03-2019 13:02
    I see,

    So how to you avoid users who do not move from having their location data no longer available for the report?

    ------------------------------
    Paul Simpson
    Senior Technical Instructor
    ------------------------------



  • 13.  RE: Where is the Agent Location data stored in the database

    Top 25 Contributor
    Posted 09-03-2019 14:19
    Many customers already have as DB mirror or log shipping enabled for their production PureConnect database.  However, for those that don't, there are a few options:

    Turn off the automatic purging.
    Lengthen the default automatic purging days for this report log.
    The data could be extracted with the Data Extractor tool.
    Because this data is going to be exposed with a custom query/report only, custom DB functions could be used:  A DB Trigger could be utilized to copy the data to a new custom table, it could be exported elsewhere and built into the data model manually (ie. with a BI solution).

    There are a number of ways to keep the data.  It all depends on what is most comfortable, how it is going to be used, with what tool(s), etc.

    Thanks,

    ------------------------------
    Trent Vance
    Avtex
    ------------------------------



  • 14.  RE: Where is the Agent Location data stored in the database

    GCAP Member
    Posted 09-01-2019 18:28
    Hi @Trent Vance,

    Yes please I would love to know how/where you source this info.
    Feel free to share in this thread if you can and it may benefit anyone else that needs this as well.​

    ------------------------------
    Jayden Bradow
    Service Performance Analyst
    Precision Administration Services (Pty) Ltd
    ------------------------------



  • 15.  RE: Where is the Agent Location data stored in the database

    GCAP Member
    Posted 09-01-2019 22:03
    Jayden

    Is the information in the IndivDetails or IndivSummary tables useful?

    ------------------------------
    Tarquin Bell
    Precision Administration Services (Pty) Ltd
    ------------------------------



  • 16.  RE: Where is the Agent Location data stored in the database

    Top 25 Contributor
    Posted 09-03-2019 07:51
    Jayden -

    After hours, enable the Enhanced Interaction Administrator Change Notification Log:


    Once that is done, and the snapshot of the system is complete, have a look at the EnhancedIAChangLogId table, as well as the IAAttributeLog table.  Every single value, for every single entry is contained here for the Users, Workgroups, Skills, Licenses, Default User, and Roles nodes in IA.

    More documentation can be found in the Resource Center, and the Release Notes.  But this information is absolutely contained in the database.

    Thanks,
    Trent.

    ------------------------------
    Trent Vance
    Avtex
    ------------------------------



  • 17.  RE: Where is the Agent Location data stored in the database

    Posted 09-04-2019 11:32
    Just a heads up the Snapshot does not capture all data and actually wipes out some historical data such as skills and will only capture when new changes occur. This bug was fixed in 2018 R2 patch 13 from what I've been told; however we have not installed the patch to confirm.

    We we do is create non-routing workgroups based on location that is assigned to every Agent; however, it only works on queries and does not appear to work on out of box reporting.

    ------------------------------
    Brad Goff
    Palo Alto Networks, Inc.
    ------------------------------



  • 18.  RE: Where is the Agent Location data stored in the database

    GCAP Member
    Posted 09-04-2019 18:19
    Hi @Brad Goff,

    Creating some non-routing workgroups was something I was considering doing as well and I still might look at this for grouping based on other requirements.
    We are currently looking into the Enhanced Log to see if this is suitable for us.

    ------------------------------
    Jayden Bradow
    Service Performance Analyst
    Precision Administration Services (Pty) Ltd
    ------------------------------