I am working for a company where records about employees are in ServiceNow sys_user table. Some employees have more than one record. The way that company does things, multiple records in sys_user for one employee causes errors.
First records in sys_user
These names all are fictitious.
Rosemary Parks |
Lottie Carpenter |
Autumn Oneill |
Alisha Kane |
Eugenia Schmitt |
Andrea Villanueva |
| Andrea Villanueva |
| Andrea Villanueva |
| Andrea Villanueva |
Ernestine Mercado |
Amie Anderson |
Kelly Garrett |
Mercedes Bender |
Lena Middleton |
Tricia Bradford |
Josie Yu |
Sarah Randolph |
Claire Banks |
Marissa Ortiz |
May Garza |
Peggy Rodriguez |
Felecia Sutton |
Imelda Harper |
Deanna Castro |
Kathrine Benson |
Katina Benjamin |
Maricela Ray |
Francis Huang |
Jeri Mccormick |
Karin Fuentes |
Marjorie Hughes |
| Marjorie Hughes |
| Marjorie Hughes |
| Marjorie Hughes |
Beth Melendez |
Edith Cherry |
Jaime Luna |
Lilia Blankenship |
Jenifer Clayton |
Cecelia Fry |
Betsy Orr |
Lizzie Warren |
Silvia Lucero |
Coleen Bennett |
Alisa Delacruz |
Debora Fleming |
Erna Newman |
Geraldine Orozco |
Gabriela Ryan |
Tina Reynolds |
| Tina Reynolds |
Chelsea Moon |
Erica Davies |
Milagros Arnold |
Rosella Branch |
Cheri Pineda |
Joann Hale |
ServiceNow lists allow grouping of data on matching values in a column. Example:
Same records, grouped by name
Rosemary Parks | 1 |
Lottie Carpenter | 1 |
Autumn Oneill | 1 |
Alisha Kane | 1 |
Eugenia Schmitt | 1 |
Andrea Villanueva | 4 |
Ernestine Mercado | 1 |
Amie Anderson | 1 |
Kelly Garrett | 1 |
Mercedes Bender | 1 |
Lena Middleton | 1 |
Tricia Bradford | 1 |
Josie Yu | 1 |
Sarah Randolph | 1 |
Claire Banks | 1 |
Marissa Ortiz | 1 |
May Garza | 1 |
Peggy Rodriguez | 1 |
Felecia Sutton | 1 |
Imelda Harper | 1 |
Deanna Castro | 1 |
Kathrine Benson | 1 |
Katina Benjamin | 1 |
Maricela Ray | 1 |
Francis Huang | 1 |
Jeri Mccormick | 1 |
Karin Fuentes | 1 |
Marjorie Hughes | 3 |
Beth Melendez | 1 |
Edith Cherry | 1 |
Jaime Luna | 1 |
Lilia Blankenship | 1 |
Jenifer Clayton | 1 |
Cecelia Fry | 1 |
Betsy Orr | 1 |
Lizzie Warren | 1 |
Silvia Lucero | 1 |
Coleen Bennett | 1 |
Alisa Delacruz | 1 |
Debora Fleming | 1 |
Erna Newman | 1 |
Geraldine Orozco | 1 |
Gabriela Ryan | 1 |
Tina Reynolds | 2 |
Chelsea Moon | 1 |
Erica Davies | 1 |
Milagros Arnold | 1 |
Rosella Branch | 1 |
Cheri Pineda | 1 |
Joann Hale | 1 |
We have 25,000 employees.
Going through all those records by eye is time consuming. When we had our employee list in a system using a relational database management system, not hidden from technical staff such as my self, we could isolate this kind of record lie this
Using sql, isolating all this kind of duplicate record is accomplished with:
select
user_name, count(*)
from sys_user
group by user_name
having count(*) > 1
order by count(*) desc
The result looks something like this:
Andrea Villanueva | 4 |
Marjorie Hughes | 3 |
Tina Reynolds | 2 |
Is there a way to reproduce this result in ServiceNow?
