ICM Agent/Person Export Issue

[jstans]

I'm running into an issue where when I export the Person and Agent tables using Configuration Manager's Bulk Edit and I try to include the description in the export. If the description has multiple lines in it ICM is causing the description to drop to a new line, which makes it to where I can't correlate that data correctly in Excel.

Would an SQL query be a better option for pulling that data? Currently I'm pulling the PersonID, AgendDeskSettingsID, PeripheralID, EnterpriseName, PeripheralNumber, and Description from the Agent side. Then pulling the PersonID, LastName, FirstName, LoginName, LoginEnabled, SSOEnabled, and Description from the Person side. As well as using an SQL query I found on here to get the Team they're assigned to so that I can use Excel to correlate it all together to help us when clearing inactive accounts after they've passed their delete hold threshold.

If an SQL query could pull the Description from both the Person table and Agent table without the info being split like it does with how I'm doing it, that would be extremely useful. Unfortunately I am very much not good at SQL.

If SQL can do that the info I'd ideally need would be: PersonID, EnterpriseName, LastName, FirstName, LoginName, PeripheralNumber, AgentDeskSettings, PeripheralID, Agent Team, Skill Group (Currently assigned, not default), and both the Person and Agent Descriptions. But honestly I'd be happy with just the PersonID and un-line broken descriptions then I can just use Excel to match them with their respective agents.

Hopefully someone here can point me in the right direction, or just let me know if it's a no go on a non-line broken description.

Thank you.

[david.macias]

You’re barking up the wrong tree and need to treat the end result as a CSV and nothing else. You can still use Excel for editing, but open it and save it as a CSV only. So, to answer your question the new line can’t be there in the CSV.

david

[ahmed.zakaria · Accepted solution]

UCCE Agent SQL

Agent <> Teams

SELECT     
AgentSkillTargetID = Agent.SkillTargetID, 
TeamID = isNull(Agent_Team_Member.AgentTeamID,'0'), 
PersonalID = Agent.PersonID, 
AgentPeripheralNumber = Agent.PeripheralNumber,
FirstName = Person.FirstName, 
LastName = Person.LastName, 
LoginName = Person.LoginName,
TeamName = isNull(Agent_Team.EnterpriseName,'No Team Assigned')
 
FROM Agent
LEFT JOIN Agent_Team_Member ON Agent.SkillTargetID = Agent_Team_Member.SkillTargetID
LEFT JOIN Agent_Team ON Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID
LEFT JOIN Person ON Agent.PersonID = Person.PersonID
 
-- WHERE Agent.PeripheralNumber = '55510001'
 
ORDER BY Person.FirstName

Agent Logged in and Other Agent Stats

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT 
	  --AI.[DateTime]
      --,AI.[SkillTargetID]
	  P.[FirstName]
	  ,P.[LastName]
      ,LoggedOnTime = CONVERT(VARCHAR, DATEADD(SECOND,SUM(AI.[LoggedOnTime]),0),108)
	  ,LoggedOnTimeSec = SUM(AI.[LoggedOnTime])
	  ,LoggedOnTimeDD = CONCAT((SUM(AI.[LoggedOnTime])/3600/24),':',(SUM(AI.[LoggedOnTime])/3600%24),':',(SUM(AI.[LoggedOnTime])/60%60))
      ,AvailTime = CONVERT(VARCHAR, DATEADD(SECOND,SUM(AI.[AvailTime]),0),108)
      ,NotReadyTime = CONVERT(VARCHAR, DATEADD(SECOND,SUM(AI.[NotReadyTime]),0),108)
      ,TalkOtherTime = CONVERT(VARCHAR, DATEADD(SECOND,SUM(AI.[TalkOtherTime]),0),108)
      ,AI.[Extension]
	  ,TeamName = isNull(AT.EnterpriseName,'No Team Assigned')
  FROM 
  [Agent_Interval] AI
  JOIN [Agent] A ON AI.SkillTargetID = A.SkillTargetID  
  JOIN [Person] P ON A.PersonID = P.PersonID 
  LEFT JOIN Agent_Team_Member ATM ON A.SkillTargetID = ATM.SkillTargetID
  LEFT JOIN Agent_Team AT ON ATM.AgentTeamID = AT.AgentTeamID
 
  WHERE
  (AI.DateTime >= '2019-07-11 00:00:00' AND AI.DateTime < '2019-07-15 00:00:00')
  --(AI.DateTime > '2019-07-11 00:00:00' AND AI.DateTime < '2019-07-12 00:00:00')
 
  GROUP BY 
  P.[FirstName]
 ,P.[LastName]
 ,AI.[Extension]
 ,AT.EnterpriseName
 ORDER BY SUM(AI.[LoggedOnTime]) DESC