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
------------------------------

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
------------------------------

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------

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
------------------------------

Original Message:
Sent: 08-29-2019 00:19
From: Aaron Lael
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------

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.
------------------------------

Original Message:
Sent: 08-29-2019 01:55
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-29-2019 00:19
From: Aaron Lael
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------

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.

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
------------------------------

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------

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

Regards

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

Original Message:
Sent: 08-30-2019 10:13
From: Trent Vance
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------

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
------------------------------

Original Message:
Sent: 08-30-2019 10:13
From: Trent Vance
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------

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!

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
------------------------------

Original Message:
Sent: 09-03-2019 07:38
From: Trent Vance
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-30-2019 11:45
From: Paul Simpson
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-30-2019 10:13
From: Trent Vance
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------

Correct.  The default retention period and functionality works the same way for this report log as it does for any of the others.

I see,

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

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,

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
------------------------------

Original Message:
Sent: 08-30-2019 10:13
From: Trent Vance
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------

Jayden

Is the information in the IndivDetails or IndivSummary tables useful?

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

Original Message:
Sent: 09-01-2019 18:28
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-30-2019 10:13
From: Trent Vance
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------

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.

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.
------------------------------

Original Message:
Sent: 09-03-2019 07:50
From: Trent Vance
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 09-01-2019 18:28
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-30-2019 10:13
From: Trent Vance
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------

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
------------------------------

Original Message:
Sent: 09-04-2019 11:32
From: Brad Goff
Subject: Where is the Agent Location data stored in the database

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.

Original Message:
Sent: 09-03-2019 07:50
From: Trent Vance
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 09-01-2019 18:28
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-30-2019 10:13
From: Trent Vance
Subject: Where is the Agent Location data stored in the database

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

Original Message:
Sent: 08-28-2019 23:11
From: Jayden Bradow
Subject: Where is the Agent Location data stored in the database

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
------------------------------