В данной статье приведены примеры технических отчётов, которые наши сотрудники делают при решении задач - часто это короткие отчеты для каких-то специфичных кейсов.
- Примеры
- Просмотр текущих прав доступа к типам адресных единиц для задачи SUP-632439
- Пояснение структуры хранения данных в справочнике адресов по задаче SUP-594716
- Дубли реквизитов, задача SUP-645914
- Учётные записи с логином равным номеру договора другого абонента, задача SUP-650800
- Отчет со списком абонентов, подключивших оповещение через Telegram. SUP-651766
- Количество точек подключения по адресам SUP-633846
- Точки подключения по выбранному адресу SUP-633846
- Проверка дублей портов SUP-633846
- Транки операторов SUP-246480
- Услуги, привязанные к NAS SUP-675790
- Количество абонентов с услугами привязанными к NAS-серверам SUP-675790
- Flussonic Watcher v2, учтётные записи, камеры и услуги по камерам для схемы интеграции SUP-675790
- Flussonic Watcher v2, услуги сервиса, которые можно подключить абонентам, с контролем ошибок SUP-772439
- Дубли по Названию/ФИО, SUP‑765404
- Изменения даты договора, отчёт по аудиту, SUP-812311
- Учётные записи, сгруппированные по типу авторизации и NAS, SUP-1004993
- Узнать MAC адрес клиентского устроства в PPPoE сессии
Примеры
Просмотр текущих прав доступа к типам адресных единиц для задачи SUP-632439
Выводит настроенные права доступа к справочнику "Типы адресных единиц"
select agp.id, ag.name, ap.codename from auth_permission ap left join AUTH_GROUP_PERMISSIONS as agp on agp.PERMISSION_ID = ap.id left join AUTH_GROUP as ag on ag.id = agp.group_id where ap.codename like 'modelperm_HomeTypeName%' order by agp.id
Пояснение структуры хранения данных в справочнике адресов по задаче SUP-594716
- Основные данные (страна, улица, номер дома) хранятся в таблице HOMES
- Основная таблица связана с таблицей типы адресных единицы (это строение, дом, микрорайон, улица, область или край и тд), название таблицы - HOME_TYPE_NAME
- Еще есть служебное поле с типом записи справочника дома - нужно для верного построения дерева домов, таблица называется HOME_TYPES
Ниже сам запрос и пример полученных данных
select first 1 HOMES.ID "ID дома", HOMES.ZIP_CODE "Индекс", HOME_TYPE.NAME "Служебный тип", HOMES.COUNTRY "Страна", REGION_TYPE."FULL" "Тип региона", HOMES.REGION "Регион", CITY_TYPE."FULL" "Тип нас.пункта", HOMES.CITY "Нас. пункт", SETTLEMENT_TYPE."FULL" "Тип поселения", HOMES.SETTLEMENT "Поселение", STREET_TYPE."FULL" "Тип улицы", HOMES.STREET "Улица", S_NUMBER_TYPE."FULL" "Тип строения", HOMES.S_NUMBER "Номер строения", KLADR_ID "ID в ФИАС", UNRESTRICTED_VALUE "Адрес строкой" from HOMES left join HOME_TYPES as HOME_TYPE on HOMES.HOME_TYPES_ID=HOME_TYPE.ID left join HOME_TYPE_NAME as CITY_TYPE on HOMES.CITY_TYPE_ID=CITY_TYPE.ID left join HOME_TYPE_NAME as DISTRICT_TYPE on HOMES.DISTRICT_TYPE_ID=DISTRICT_TYPE.ID left join HOME_TYPE_NAME as REGION_TYPE on HOMES.REGION_TYPE_ID=REGION_TYPE.ID left join HOME_TYPE_NAME as SETTLEMENT_TYPE on HOMES.SETTLEMENT_TYPE_ID=SETTLEMENT_TYPE.ID left join HOME_TYPE_NAME as STREET_TYPE on HOMES.STREET_TYPE_ID=STREET_TYPE.ID left join HOME_TYPE_NAME as S_NUMBER_TYPE on HOMES.S_NUMBER_TYPE_ID=S_NUMBER_TYPE.ID left join HOME_TYPE_NAME as S_LITER_TYPE on HOMES.S_LITER_TYPE_ID=S_LITER_TYPE.ID order by HOMES.ID desc
Пример данных | |
---|---|
ID дома | 2409 |
Индекс | 665831 |
Служебный тип | Строение |
Страна | Россия |
Тип региона | область |
Регион | Иркутская |
Тип нас.пункта | город |
Нас. пункт | Ангарск |
Тип поселения | микрорайон |
Поселение | Байкальск |
Тип улицы | улица |
Улица | Торговая |
Тип строения | дом |
Номер строения | 10 |
ID в ФИАС | 3800000400010560005 |
Адрес строкой | 665831, Иркутская обл, г Ангарск, мкр Байкальск, ул Торговая, д 10 |
Дубли реквизитов, задача SUP-645914
Отчёт находит абонентов с дублями реквизитов.
Абоненту можно добавить несколько экземпляров одного и того же реквизита (например, если нужно добавить несколько телефонов или файлов). Но в некоторых случаях это может создавать проблемы - например, при интеграции с СОРМ.
Отчёт покажет какие реквизиты у каких абонентов задублированы.
Последнее текстовое поле нужно поправить "под себя" - укажите там IP-адрес Вашего биллинга вместо 10.0.0.1
select
*
from
(select
count(*) cnt,
ua.name attr_name,
av.attribute_id attr_id,
av.abonent_id abon_id,
a.name abon_name,
'http://10.0.0.1:8082/admin/Abonents/' || av.abonent_id || '/?tab=tab1147' url_to_abon_attrs
from
ATTRIBUTE_VALUES av
join
USER_ATTRIBUTES ua
on av.attribute_id=ua.attribute_id
join
abonents a
on
av.abonent_id=a.id
group by
2,3,4,5,6)
where
cnt>1
order by
4,5,1 desc
Учётные записи с логином равным номеру договора другого абонента, задача SUP-650800
При создании абонента, создаётся и учётная запись с логином соответствующим номеру договора, поэтому маловероятно что такой логин будет использоваться у како-то другого абонента.
Тем неменее, такую ситуацию можно воспроизвести при интеграции IPTV, когда отдельная услуга создаёт учетную запись, в результате "второму" абоненту не будет добавлена новая учетная запись, а система мониторинга создаст автозаявку о проблеме.
Чтобы проблема не возникала, лучше не создавать ситуацию когда номер договора соответствует логину учетной записи другого абонента. Отчёт позволит найти таких абонентов.
select u.abonent_id, u.login, a.id, a.contract_number from users u join abonents a on u.login=a.contract_number and u.abonent_id<>a.id where a.is_folder=0
select count(*) from users u join abonents a on u.login=a.contract_number and u.abonent_id<>a.id where a.is_folder=0
Отчет со списком абонентов, подключивших оповещение через Telegram. SUP-651766
select A.CONTRACT_NUMBER as "Номер договора", A.Name as "ФИО", TLG.ATTRIBUTE_VALUE as "ID Telegram" from ABONENTS A left join ATTRIBUTE_VALUES TLG on TLG.ABONENT_ID=A.ID and TLG.ATTRIBUTE_ID=1014 where A.is_folder=0 and A.deleted=0 and coalesce(TLG.ATTRIBUTE_VALUE,'') != ''
В данном примере явно указан ATTRIBUTE_ID=1014, в вашем биллинге идентификатор может быть совсем иным. Чтобы проверить это, необходимо обратиться к справочнику "Реквизиты" По столбцу № вы сможете определить нужный id для подстановки в отчет.
Количество точек подключения по адресам SUP-633846
Отчёт покажет только адреса, на которых есть хотя бы одна точка подключения.
select count(*), h.street "Улица", h.s_number "Номер дома", h.id "ID дома" from connection_points cp join homes h on cp.home_id=h.id group by 2,3,4 order by 3,4
Точки подключения по выбранному адресу SUP-633846
Отчёт покажет только точки подключения, в которых выбран коммутатор и порт
select cp.id "ID точки п.", case when coalesce(sp.name,'')<>'' then sp.name ||', ' || uf_ip2string(sw.ip) when coalesce(sp.num,-100)<>-100 then sp.num ||', ' || uf_ip2string(sw.ip) else '' end "Коммутатор/порт", cp.socket "Розетка", '(ID ' || h.id ||'), ' || h.street ||', ' || h.s_number ||', ' || cp.flat "Адрес", case when cp.abonent_id is not null then (select a.id || ' ' || a.name from abonents a where a.id=cp.abonent_id) when cp.user_id is not null then (select u.id || ' ' || u.login from users u where u.id=cp.user_id) else 'Свободно' end "Кем занято" from connection_points cp join homes h on cp.home_id=h.id join switch_ports sp on cp.switch_p_id=sp.id join switch sw on sp.switch_id=sw.id where cp.home_id = ':Дом|select[Homes,home_types_id=6]$' order by h.id,cp.flat
Проверка дублей портов SUP-633846
Порты заведённые с одним наименованием более одного раза
select * from (select count(*) cnt, sp.switch_id, sp.name from switch_ports sp where coalesce(name,'')<>'' group by sp.switch_id, sp.name ) where cnt>1 order by 1 desc
Транки операторов SUP-246480
Отчёт покажет настройку транков в услугах операторов для учёта межоператороского трафика.
select distinct a.name "Оператор", coalesce(u.name,'Нет услуги') "Услуга", coalesce(vt.name,'') "Тип трафика", iif(uu.id is not null,voip_operator_match_format,'оттсутствуют') "Транки" from abonents a join users_usluga uu on a.id=uu.abonent_id and voip_operator_match_format<>'' left join usluga u on uu.usluga_id=u.id left join v_type vt on u.voip_v_type_id=vt.id where a.category_id=2 and a.id>2 order by :Сортировка по:|choices[1^]Наименованию^[3^]Транкам]$
Услуги, привязанные к NAS SUP-675790
Отчёт поможет проверить корректность настройки интеграции Flussonic Watcher v1 (архивная) и IPTV, показав услуги привязанные к выбранному NAS. Так Вы сможете проверить, правильно ли указаны параметры активации/деактивации услуги, все ли они привязаны в нужному NAS-серверу.
select u.id, u.name "Название", u.activate_string, u.deactivate_string, ut.name "Тип услуги", uat.name "Метод списания", case when coalesce(post_pay,0)=1 then 'Постоплата' else 'Предоплата' end "Тип списания", case when coalesce(u.deleted,0)=1 then 'Удалена' else '-' end "Услуга удалена?" from usluga u join usluga_type ut on u.system_type=ut.id join usluga_abon_type uat on u.usluga_abon_type_id=uat.id where nas_id = :NAS|select[NAS]$ and coalesce(u.deleted,0) :Показывать удалённы услуги?|choices[=0^]Нет^[<>2^]Да]$ order by u.activate_string
Количество абонентов с услугами привязанными к NAS-серверам SUP-675790
Отчёт пригодится для учета абонентов интернет-телевидения и видеонаблюдения - он покажет количество абонентов с услугами, в которых указан какой-либо NAS, что используется в схемах интеграции с этими сервисами.
select count(distinct uu.abonent_id) "Кол-во абонентов", n.name "NAS" from users_usluga uu join usluga u on uu.usluga_id=u.id join nas n on u.nas_id=n.id where uu.abonent_id is not null and uu.deleted=0 and u.deleted=0 group by n.name order by 1
Flussonic Watcher v2, учтётные записи, камеры и услуги по камерам для схемы интеграции SUP-675790
Отчёт покажет учетную запись Flussonic с ID пользователя в Watcher. Если у абонента есть камеры, то в отчёте будет её наименование и привязанные услуги - пресет и глубина DVR записи с детекцией движения.
u.nas_id=1184 во втором подапросе это ID NAS Flussonic со 2 версией схемы.
--УСЛУГИ И КАМЕРЫ select a.id "ID абонента", '' "Номер договора", '' "ФИО", '' "ID учетной записи", '' "Логин", '' "Пароль", '' "ID Watcher", coalesce(ususl.serial_no,''), coalesce(ususl.usl_list,'') from abonents a join ( select e.serial_no serial_no, e.name e_name, e.id e_id, uu.abonent_id abonent_id, list('Услуга ' || coalesce(usl.name,'') || ' (activate_string: ' || coalesce(usl.activate_string,'') || ')') usl_list from users_usluga uu join usluga usl on uu.usluga_id=usl.id join nas n on usl.nas_id=n.id and n.nas_scheme_id=219001 --схема Flussonic and n.nas_scheme_ver=2 --версия, в которой реализован синхронизатор join equipment e on uu.equipment_id=e.id where uu.deleted=0 and coalesce(uu.equipment_id,'')<>'' group by e.serial_no, e.name, e.id, uu.abonent_id ) ususl on ususl.abonent_id=a.id where a.id>0 union --УЧЕТНЫЕ ЗАПИСИ select a.id "ID абонента", a.contract_number "Номер договора", a.name "ФИО", cast(u.id as varchar(32)) "", u.login, u.gen_pwd, coalesce(u.ext_id,''), '', '' from abonents a join users u on u.abonent_id=a.id where a.id>0 and u.nas_id=1184 --1 - СОРТИРУЕМ ПО ID АБОНЕНТА ЧТОБЫ УЧЕТКА И УСЛУГИ С КАМЕРАМИ БЫЛИ РЯДОМ --4 desc - СОРТИРУЕМ ТАК, ЧТОБЫ СВЕРХУ БЫЛА УЧЕТКА, ПОД НЕЙ УСЛУГИ order by 1,4 desc
Flussonic Watcher v2, услуги сервиса, которые можно подключить абонентам, с контролем ошибок SUP-772439
select u.id, u.name "Название", u.activate_string, u.deactivate_string, ut.name "Тип услуги", uat.name "Метод списания", case when coalesce(u.post_pay,0)=1 then 'Постоплата' else 'Предоплата' end "Тип списания", case when coalesce(u.deleted,0)=1 then 'Удалена' else '-' end "Услуга удалена?", case when coalesce(list(duplicate.id),'-') <> '-' then list('ID=' || cast(duplicate.id as varchar(32)) || ' ' || duplicate.name) else '-' end "Дубль?", case when u.create_login=1 and u.require_assign_to_user=1 then '1=Создавать учетную запись при подключении, 2=Для услуги необходима привязка к учетной записи' when u.create_login=1 and u.require_assign_to_user=0 then 'Создавать учетную запись при подключении' when u.create_login=0 and u.require_assign_to_user=1 then 'Для услуги необходима привязка к учетной записи' else '-' end "Отключите опции!", case when coalesce(list(tuu.tarif_id),'-') <> '-' and coalesce(u.deleted,0)=0 then 'Удалите услугу из этих тарифов: ' || list('ID=' || tuu.tarif_id || ' ' || t.name) else '-' end "УберитеИзТарифoв!" from usluga u join nas n on u.nas_id=n.id join usluga_type ut on u.system_type=ut.id join usluga_abon_type uat on u.usluga_abon_type_id=uat.id left join tarif_users_usluga tuu on tuu.usluga_id = u.id left join tarif t on tuu.tarif_id = t.id left join usluga duplicate on (u.activate_string = duplicate.activate_string or u.deactivate_string = duplicate.deactivate_string) and u.id<>duplicate.id and u.nas_id=duplicate.nas_id and coalesce(duplicate.deleted,0)=0 where n.nas_scheme_id = 219001 and coalesce(u.deleted,0) :Показывать удалённы услуги?|choices[=0^]Нет^[<>2^]Да]$ group by u.id, u.name, u.activate_string, u.deactivate_string, ut.name, uat.name, u.post_pay, u.deleted, u.create_login, u.require_assign_to_user order by u.activate_string
Дубли по Названию/ФИО, SUP‑765404
Отчёт выведет абонентов с повторяющимися наименованием или ФИО.
![]() | Замените IP 10.0.0.2 на IP/домен Вашего сервера, чтобы удобней копировать ссылки. |
select distinct a.name, case when coalesce(a.deleted,0) = 1 then 'Удалён' else '' end, 'http://10.0.0.2:8082/admin/Abonents/' || a.id from abonents a join abonents a_double on a.name = a_double.name and a.id <> a_double.id where 1=1 and a.name <> 'Новый абонент' order by a.name
Изменения даты договора, отчёт по аудиту, SUP-812311
Отчёт поможет найти абонентов, у которых менялась дата в поле "Дата договора".
В выборку попадёт только аудит, находящийся в основной базе: по-умолчанию, за последний месяц.
Самая левая колонка - логин администратора, который менял дату.
select admins.username, audit.* from audit_operations audit left join auth_user admins on audit.admin_id = admins.id where table_name = 'abonents' and descr like '%Изменения%Дата договора%Ссылки%' order by op_time desc
Учётные записи, сгруппированные по типу авторизации и NAS, SUP-1004993
У технической поддержки при часто возникает необходимость узнать, какой тип авторизации, на каком NAS и с какой схемой используется. Это необходимо, чтобы правильно диагностировать причины различных инцедентов с авторизацией.
В отчёт попадут только абоненты, у которых указан NAS и задан либо IP, либо телефонный номер.
select count(*) "Количество", n.name || ' (ID=' || cast(n.id as varchar(32)) || ')' "NAS", ns.name "Схема", coalesce(aut.name,'Не задан') "Тип авторизации" from users u left join auth_types aut on u.auth_type=aut.id join nas n on u.nas_id=n.id join nas_scheme ns on n.nas_scheme_id=ns.id where u.deleted=0 and u.is_template=0 and ( u.ip is not null or u.phone is not null ) and u.id>0 group by n.id, n.name, ns.name, aut.name order by :Сортировать по|choices[2, 1 desc,3^]NAS, потом количеству^[1 desc,2^]количеству, потом NAS]$ desc
Узнать MAC адрес клиентского устроства в PPPoE сессии
Биллинг записывает данные по сессиям абоенента в таблицу RADIUS_SESSIONS. В ней есть поле MAC. Чтобы биллинг заполниел его, должен придти accaunting start пакет. В нём должен быть атрибут Calling-Station-Id и MAC разделённы двоеточиями.
Calling-Station-Id = "48:22:54:eb:3f:3d"
Следующий отчёт покажет абоненетов с "проблемным" оборудованием - у которых за последние сутки было более трёх подключений.
select rs.abonent_id, a.contract_number, mac, count(rs.abonent_id) from radius_sessions rs left join abonents a on rs.abonent_id = a.id where rs.start_time > DATEADD(day, -1, CURRENT_TIMESTAMP) group by rs.abonent_id, a.contract_number, mac having count(rs.abonent_id)>3 order by 3 desc