Paul Courbis

01. Introduction et pré-requis techniques

Paul Courbis — 2007-01-04T17:11:15Z

Les autocommutateurs (PABX) Alcatel de type 4400 sont construits autour d'un système d'exploitation de type Unix (basé sur un noyau Chorus). Entre autre choses, ils sont capables de fournir un relevé de taxation exploitables par un logiciel fourni par le constructeur.

Ade permettre une exploitation plus souples de ces données, j'ai dévellopé un certains nombre d'outils logiciels afin de traiter ces tickets de facturation (DC - détails de communications) à travers une base de données de type SQL.

Vous trouverez ici les grandes lignes d'utilisation de cette procédure, ainsi que certains éléments logiciels spécifiques. N'hésitez pas à me contacter si vous rencontrez des difficultétés lors de leur mise en place...

Pré-requis techniques :

Un Pabx de type Alcatel 4400 ;
Un serveur de type Unix ou une émulation Unix (type Cygwin) ;
Une connexion réseau entre le Pabx et le serveur.

Vous devez à ce stade être capable de réaliser une connexion de type « ftp » avec le compte « mtcl » entre le serveur et la PBAX.

Attention : il peut être nécessaire d'autoriser le serveur à se connecter sur le PABX.

Avant de commencer, récupérez l'archive contenant les scripts et les programmes.

Décompressez là où vous le désirez et :
compilez le programme C « tax2sql » (make tax2sql)
editez le fichiers « Definitions » et renseignez le conformément à votre configuration
modifiez l'appel à « Definitions » dans l'ensemble des scripts shell livrés.

(une installation automatique viendra plus tard)

02. Récupération des fichiers de taxation

Paul Courbis — 2007-01-04T17:11:14Z

Avant toute analyse il convient de mettre en place une procédure de récupération des fichiers de taxation stockés sur le PABX.

Vérifiez l'emplacement des fichiers de taxation sur le serveur. Ceux ci se trouvent à priori dans /DHS3dyn/account. Le répertoire contient des fichiers dont le nom est du type « TAX ?????.DAT » où les ? sont des lettres (TAXAAAAA.DAT, TAXAAAAB.DAT...).

A ce stade vous devez normalement être à même de réaliser un transfert FTP entre votre serveur et le PABX. Le compte « mtcl » suffit en général pour cela.

Le PABX nettoyant régulièrement les fichiers de taxation, il est donc conseillé de répliquer ces données sur votre serveurs au moins une fois par jour. Pour cela, il est possible d'utiliser l'utilitaire « wget » qui réalise cette fonction.

Créez pour cela un répertoire « spool » sur votre serveur dans lequel seront stockés les fichiers répliqués.

Puis mettez en place un batch tournant de manière régulière et basé sur le modèle suivant :

RecupData

#!/bin/sh
 
 wget --directory-prefix=/home/pabx/raw \
 --ftp-user=mtcl \
 --ftp-password=password \
 -nv -m -nd ftp://pabx/DHS3dyn/account

(le script est livré dans le sous répertoire "bin de l'archive téléchargée).

Une excécution quotidienne est en général suffisante pour assurer une alimentation correcte de la base de données...

Vous pouvez par exemple créer un compte utilisateur « pabx » dont le « home directory » est /home/pabx, avec les sous répertoires « bin » et « rax ». Le répertoire « raw » recevra les données brutes en provenance du pabx et « bin » contiendra les différents scripts que nous allons détailler, dont celui ci. Sous Unix (Linux, AIX, HP-UX et autres), il suffit d'ajouter une ligne dans la crontab de l'utilisateur pabx (avec par exemple « crontab -e ») de la forme :

30 5* * * /home/pabx/bin/RecupData

Pour lancer le script à 5h30 du matin tous les jours.

Explication des paramètres :

—directory-prefix : précise le lieu de tockage des fichiers rapatriés ;
—ftp-user : précise le login à utiliser (mtcl) ;
—ftp-password : précise le mot de passe à utiliser pour le login ;
-nv : non-verbose, pas bavard mais pas muet ;
-m : mirroir / effectue une copie des nouveaux fichiers ;
-nd : ne cree pas de sous répertoires sur le disque local ;
le dernier paramètre (ftp://...) est l'URL d'accès aux données, « pabx » étant le nom ou l'adresse IP du PABX.

Il est aussi possible d'utiliser d'autres outils de synchronisation, comme « mirror » (a priori non distribué en natif sous cygwin).

03. Principe de décodage des fichiers de taxation

Paul Courbis — 2007-01-04T17:11:12Z

Les fichiers que nous vennons de récupérer sont stockés sous un format binaire. Vous trouverez ci-dessous la procédure à suivre pour les transformer en une version lisible et explotable...

Les fichiers de taxation sont en effet des fichiers compressés (avec le compress unix calssique) contenant des enregistrements de type texte dont la décomposition est donnée dans leur première ligne . Par exemple :

#TicketVersion,1,5,L,CalledNumber,6,31,L,ChargedNumber,32,51,L,....

Ce qui signifie :

'#' commence une ligne de description de format
le premier champ s'appelle « Ticket version »
il commence en colonne 1 et se termine en colonne 5
son type est L (les types possibles sont 'L', champ texte, 'N', champ texte, et 'R', nombre entier)
le deuxième champ est « CalledNumber »
etc...

Cette définition peut changer d'une version de logiciel à l'autre, version qui est d'ailleurs systématiquement rappelée dans le champ « TicketVersion ». Dans les versions que j'ai utilisé pour mes tests (4.8, 4.9 et 5.1), les champs sont les suivants :

	Version
Champ	4.8	4.9	5.1	Contenu
TicketVersion				Version du ticket de facturation
CalledNumber				Numéro appelé ou appelant (selon le sens)
ChargedNumber				Poste facturé
ChargedUserName				Nom du titulaire du poste
ChargedCostCenter				Centre de coût
ChargedCompany				-
ChargedPartyNode				-
Subaddress				-
CallingNumber				-
CallType				PublicNetworkOutgoingCall (0), PublicNetworkOutgoingCallThroughPrivateNetwork (1), PrivateNetworkCall (2), LocalCall (3), PublicNetworkIncomingCall (4), PublicNetworkIncomingCallThroughPrivateNetwork (5), Unspecified (6), PrivateNetworkOutgoingCallToPublicNetwork (7), PrivateNetworkOutgoingCallToPrivateNetwork (8), PublicNetworkIncomingCallToPrivateNetwork (9), PrivateNetworkIncomingCallToPrivateNetwork (10), PublicOrPrivateNetworkOutgoingCallThroughPrivateNetwor (11), PublicOrPrivateNetworkIncomingCallThroughPrivateNetwork (12), PrivateNetworkIncomingCall (13), LocalLocalNode (14)
CostType				-
EndDateTime				Date/heure de fin de l'appel
ChargeUnits				Unités téléphoniques facturées
CostInfo				-
Duration				Durée de l'appel
TrunkIdentity				-
TrunkGroupIdentity				-
TrunkNode				-
PersonalOrBusiness				-
AccessCode				-
SpecificChargeInfo				-
BearerCapability				-
HighLevelComp				-
DataVolume				-
UserToUserVolume				-
ExternFacilities				-
InternFacilities				-
CallReference				-
SegmentsRate1				-
SegmentsRate2				-
SegmentsRate3				-
ComType				-
X25IncomingFlowRate				-
X25OutgoingFlowRate				-
Carrier				-
InitialDialledNumber				-
WaitingDuration				-
EffectiveCallDuration				-
RedirectedCallIndicator				-
StartDateTime				Date/heure de début de l'appel
ActingExtensionNumber				-
CalledNumberNode				-
CallingNumberNode				-
InitialDialledNumberNode				-
ActingExtensionNumberNode				-
TransitTrunkGroupIdentity				-
NodeTimeOffset				-

A noter que d'une version à l'autre le type et la taille des champs peuvents changer !

Notre procédure d'import va fonctionner comme suit :

1/ Nous allons creer une base de données vide qui servira de container aux données

2/ Nous allons ensuite creer dans cette base les tables nécessaires au bon fonctionnement de l'application :

une table temporaire « spooledfiles » qui contiendra la liste des fichiers disponibles (ce qui permettra de charger uniquement les nouveaux fichiers à partir du répertoire spool) ;
une meta table (meta_columndesc décrivant la table de données (d'une version à l'autre le format est succeptible de changer, nous garderons donc la liste des descriptifs pour savoir si le format existe déjà et, si tel n'est pas le cas nous ajusteront dynamiquement une nouvelle table) ;
un début de table taxdata contenant le minimum d'informations nécessaires au fonctionnement du système : un identifiant unique de ticket et l'identifiant du fichier qui le contenait.

Nb : dans les évolutions possibles du système, il conviendra de gérer des notions de profil (qui peut faire quoi), de type d'analyses prédéfinies avec une exécution paramétrable, etc... Pour le moment notre objectif est simplement de charger ces fichiers de taxation dans un SGBDR, puis de fournir quelques exemples de traitements en SQL pur. L'interface clickodromesque viendra plus tard ;-). De ce fait seront crées :
des tables décrivant des traitements et leur évènements déclencheurs ;
des tables associées stockant les résultats des dits traitements.

3/ La procédure d'import va calculer la liste des fichiers à importer puis les chargera en adaptant si nécessaire la table de stockage (ajout/conversion de colonnes).

Remarque : de manière à permettre un travail plus efficace les données seront retravaillées comme suit :
les « leading spaces » et « trailing spaces » seront supprimés ;
les dates (StartDateTime et EndDateTime) seront converties en « timestamp ».

04. Installation et configuration de la base de données

Paul Courbis — 2007-01-04T17:11:10Z

Nous allons maintenant installer la base de données (ici postgresql) puis créer la structure de la base de données...

Si vous utilisez Cygwin, il est fortement conseillé d'utiliser la version Windows de Cygwin. Installez là où bon vous semble, sachant que, par défaut, les bases sont stockées dans le même répertoire que les binaires et que « c : » n'est donc pas forcément le meilleurs emplacement pour cela...

Pour vous simplifier la vie, il est possible de considérer les connextions locales comme étant de confiance, ce qui évite de taper le mot de passe du compte postgresql. Il faut pour cela éditer le fichier « pg_hba.conf » (dans c :\Program Files\Postgresql\data sous windows, dans $PGDATA/pg_hba.conf sous Unix) et remplacez

host all all 127.0.0.1/32 md5

par la directive suivante :

host all all 127.0.0.1/32 trust

Le script « CreateDatabase » permet ensuite :
de créer l'utilisateur « pabx » ;
de créer les tables précédement évoquées.

Le script DropDatabase réalise quant à lui une destruction complète des données, ce qui permet de reprendre complètement un import.

05. Alimentation de la base de données

Paul Courbis — 2007-01-04T17:11:07Z

Notre base est crée, il faut maintenant la remplir...

le script « Import » réalise une importation différentielle des données en calculant la liste des fichiers de taxation non connus par la base SQL.

Vous pouvez planifier son exécution automatique, juste apres la collecte des fichiers de taxation en modifiant comme suit votre crontab :

30 5* * * /home/pabx/bin/RecupData; /home/pabx/bin/Import

Note : j'ai constaté un comportemùent abérant (et rare) de postgresql sous Windows. De temps en temps, la requête de recherche de la table à utiliser ne renvoie rien ce qui provoque une tentative de création de nouvelle table par le script (création qui échoue à cause des contraintes de primary key). Le script « Clean » permet de nettoyer les données défectueuses. Il suffit alors de relancer l'import pour charger les données manquantes.

Si quelqu'un à l'explication de ce comportement- étrange, je suis prenneur...

06. Exemples d'analyses

Paul Courbis — 2007-01-04T17:11:06Z

Quelques exemples de rapports calculables à partir d'Alca-Libe

Nombre de lignes utilisées

Un facteur de blocage classique est la saturation du faisceau d'arrivée... Et, symétriquement, ajuster ce faisceau à l'utilisation réelle est une source d'économie !

Voici quelques exemples de rapports obtenus :

Nombre max de lignes utilisées sur 4 ans, agrégation par semaine.

Les 6 premiers mois de 2001, jour par jour.

Première quinzaine de janvier, heure par heure.

Le 10 janvier, heure par heure.

Le 10 janvier, heure par heure, appels entrants uniquement.

Le 10 janvier, heure par heure, appels sortants uniquement.

Top des consommateurs

Qui consomme le plus ? En durée pour le moment...

Sans les noms, mais en nombre de secondes passées au téléphone. Pas mal, non ? ;-)

Analyse géographique

Le classement est par durées cumulées décroissantes...

283771	[International] (12932:18:09)
	154851	[United States / Canada] (5968:51:18)
	11212	[Thailand] (394:57:10)
	12224	[Australia] (390:00:39)
	10686	[Malaysia] (312:37:56)
	3329	[Germany, Federal Republic of (West)] (164:22:58)
	2306	[Indonesia / Timor] (155:02:17)
	859	[China, People's Republic of] (147:40:12)
	1734	[Brazil] (114:53:11)
	2083	[Norway / Svalbard and Jan Mayen Islands (Norway)] (107:04:19)
	2717	[Korea, Republic of (South)] (106:55:03)
	3124	[Western Samoa] (96:47:00)
	2061	[Belgium] (87:27:19)
	958	[Chile] (56:22:12)
	1150	[United Kingdom] (48:55:44)
	990	[Greenland (Denmark)] (46:38:17)
	1658	[Palau] (41:57:58)
	1071	[Kiribati Republic (Gilbert Islands)] (39:38:25)
	1057	[American Samoa (U.S.)] (38:53:58)
	865	[Faeroe Islands (Denmark)] (38:04:10)
	926	[Mali] (37:46:32)
	468	[Argentina] (35:24:46)
	270	[Ethiopia] (34:29:40)
	615	[Equatorial Guinea] (32:29:13)
	739	[Cameroon, United Republic of] (31:08:28)
	631	[Tuvalu (Ellice Islands)] (31:00:22)
	429	[Cape Verde] (30:55:06)
	658	[Niue] (30:16:37)
	668	[Christmas Island / Cocos Islands / Norfolk Island (Australia)] (29:29:24)
	972	[Liberia] (28:15:25)
	671	[Papua New Guinea] (25:26:47)
	575	[Sweden] (25:21:48)
	697	[Spain] (25:03:41)
	556	[Kenya] (24:02:55)
	666	[Nauru] (23:49:46)
	729	[Wallis and Futuna Islands (Fr.)] (23:22:18)
	533	[Guam (U.S.)] (21:53:33)
	415	[Japan] (20:51:29)
	507	[Cook Islands (N.Z.)] (16:36:30)
	490	[New Caledonia (Fr.)] (14:08:22)
	469	[Northern Mariana Islands (Saipan)(U.S.)] (13:40:42)
	805	[Cuba] (13:19:56)
	247	[Bosnia] (13:18:41)
	286	[French Polynesia (Tuamotu)] (10:30:01)
	241	[Ghana] (09:48:39)
	223	[Yugoslavia] (09:45:05)
	165	[Zaire (Congo)] (09:16:16)
	177	[Aruba (ex Netherlands Antilles)] (09:11:22)
	167	[Chad] (09:08:24)
	289	[Sierra Leone] (08:46:13)
	139	[Solomon Islands] (08:45:30)
	289	[Denmark] (08:36:34)
	110	[Saint Helena & Ascension Island (U.K.)] (07:01:40)
	174	[Gabon] (06:52:26)
	233	[Tonga] (06:19:44)
	184	[Vanuatu (New Hebrides)] (05:13:55)
	117	[Brunei Darusalaam] (04:21:41)
	63	[Reunion (France)] (04:20:48)
	85	[Turkey] (04:18:24)
	46	[Benin (Dahomey)] (04:06:00)
	31	[Mauritius] (03:44:32)
	78	[Switzerland] (03:18:16)
	84	[Fiji] (03:13:30)
	86	[Poland] (03:00:51)
	93	[Guadeloupe (French Antilles)] (02:58:18)
	225	[Comoros and Mayotte Island] (02:41:02)
	32	[Luxembourg] (02:02:25)
	11	[Tokelau (Southern Union Islands) (N.Z.)] (01:38:27)
	74	[Italy] (01:34:21)
	12	[Martinique (French Antilles)] (01:32:56)
	52	[Austria] (01:20:44)
	140	[Singapore] (01:11:28)
	32	[Marshall Islands (U.S.)] (01:04:58)
	43	[Philippines] (01:04:34)
	27	[Israel] (00:59:16)
	15	[Paris] (00:28:59)
	16	[Irish Republic (Eire)] (00:24:43)
	18	[Cote d'Ivoire (Ivory Coast)] (00:19:52)
	14	[Union of Soviet Socialist Republics] (00:16:42)
	4	[Finland] (00:13:47)
	6	[Togolese Republic] (00:09:32)
	11	[Seychelles] (00:05:21)
	10	[Netherlands] (00:04:24)
	14	[Mauritania] (00:04:20)
	6	[Portugal] (00:02:15)
	6	[Sud Est] (00:01:42)
	6	[Hong Kong] (00:00:47)
	2	[Hungary] (00:00:00)
	1	[Sudan] (00:00:00)
	5	[Micronesia, Federated States of] (00:00:00)
	1	[Ecuador] (00:00:00)
	1	[Diego Garcia (Br. Indian Ocean Terr.)] (00:00:00)
	1	[Bahrain] (00:00:00)
	5	[Gambia, The] (00:00:00)
	3	[India] (00:00:00)
	1	[Angola] (00:00:00)
	1	[Venezuela] (00:00:00)
	2	[Madagascar] (00:00:00)
	1	[Morocco / Western Sahara] (00:00:00)
	1	[Haiti] (00:00:00)
	2	[South Africa] (00:00:00)
	11	[Egypt (United Arab Republic)] (00:00:00)
	1	[Bolivia] (00:00:00)
	7	[Czechoslovakia] (00:00:00)
	1	[Central African Republic] (00:00:00)
	2	[Numeros speciaux] (00:00:00)
	4	[Columbia] (00:00:00)
	2	[Tunisia] (00:00:00)
	1	[Kampuchea, Democratic (Cambodia/Khmer)] (00:00:00)
	1	[Nepal] (00:00:00)
	8	[Peru] (00:00:00)
	4	[Senegal] (00:00:00)
	3	[Afghanistan] (00:00:00)
	2	[Sud Ouest] (00:00:00)
	2	[Romania] (00:00:00)
	1	[Bulgaria] (00:00:00)
	1	[Pakistan] (00:00:00)
	5	[Bangladesh] (00:00:00)
	7	[Saudi Arabia] (00:00:00)
	2	[Int'l Marine Satellite, Pacific Ocean] (00:00:00)
	6	[Syrian Arab Republic] (00:00:00)
	3	[German Democratic Republic (East)] (00:00:00)
	26	[New Zealand] (00:00:00)
	1	[Sao Tome e Principe] (00:00:00)
	1	[Rwandese Republic] (00:00:00)
	1	[Guinea-Bissau (ex Portuguese Guinea)] (00:00:00)
	1	[Libyan Arab Jamahiriya] (00:00:00)
	2	[Portables] (00:00:00)
	1	[Taiwan (Republic of China)] (00:00:00)
	9	[Viet Nam] (00:00:00)
	1	[Macao] (00:00:00)
	1	[Nord Ouest] (00:00:00)
	3	[Guinea] (00:00:00)
	3	[Nigeria] (00:00:00)
	2	[Nord Est] (00:00:00)
	2	[Congo] (00:00:00)
	11	[Greece] (00:00:00)
	23	[Algeria] (00:00:00)
	2	[Mexico] (00:00:00)
	50765	Sans détail (3832:55:18)
34390	[France] (796:01:37)
	19300	[Paris] (478:48:03)
	3563	[Nord Est] (79:44:04)
	3342	[Sud Est] (61:30:56)
	2591	[Portables] (59:10:04)
		1157	[Orange] (27:58:30)
		871	[SFR] (17:19:57)
		401	[Bouygues] (11:27:47)
		22	[Bouygues Nomad] (00:52:29)
		140	Sans détail (01:31:21)
	2746	[Nord Ouest] (52:48:47)
	1063	[Numeros speciaux] (33:34:25)
		386	[Gratuit] (15:56:55)
		127	[Numero payant cout eleve] (05:34:44)
		313	[Audiotel] (04:50:41)
		134	[Cout d'un appel local] (03:15:32)
		46	[Numero payant cout faible] (02:15:17)
		57	Sans détail (01:41:16)
	1785	[Sud Ouest] (30:25:18)
343	[Numéros spéciaux] (04:24:52)
	33	[Renseignements] (00:56:45)
	38	[Horloge parlante] (00:16:08)
	5	[Minitel] (00:08:19)
	4	[3617] (00:07:49)
	1	[3615] (00:00:30)
	267	Sans détail (03:03:40)
414	[Numéros courts] (02:08:16)
	131	[Renseignement audio] (01:17:01)
	4	[Agence FT] (00:14:48)
	279	Sans détail (00:36:27)

80. Todo list

Paul Courbis — 2007-01-04T17:11:05Z

Bloc note pour les idées qui restent à implémenter...

Vos idées sont les bienvenues !

A faire :

modele de valorisation des appels ;
décodage géographique / valorisation : gérer des dates de validité des modèles ;
optimisation des rapports et réécriture en langage compilé ;
d'autres rapports ;
encore des rapports ;
et encore d'autres rapports d'analyse ;
gestion d'utilisateurs, de groupes, pour gérer les droits d'accès ;
modele de description des modeles de rapports ;
moteur de calcul de rapports automatiques ;
envoi des rapports par mails ;
réécriture des scripts cgi en PHP ;
interface de « self care » (permettre aux utilisateurs finaux d'avoir des infos sur leur consommation) ;
gérer les trasnferts d'appels et les conferences ;
gestion de plusieurs autocommutateurs inter-connectés.

Fait :
trouver un nom au projet ;-)
modifier la detection de format pour ignorer les changements de casse (entre la 4.9 et la 5.1 'trunkIdentity' est devenu 'trunkIdentity') ;
améliorer la detection de format pour accepter un changement de taille d'un champ en alterant les tables (est-ce vraiment utile ?) ; - ne faire tourner le vacuum sour les tables taxdata qu'en fin d'importation ;
interfacer directement le composant de décodage des tickets avec la base de données pour eviter le script shell qui ralentit le tout ;
réécrire l'algorithme de calcul du nombre de lignes utilisées : celui présenté est pitoyablement lent ;
mise en place d'index sur la base ;
décodage des zones géographiques correspondant aux numéros appelés (en cours) ;
stocker la description des zones géographiques en base de données pour permettre un reparamétrage sans recompilation du code ;
interface web pour la consultation des rapports ;

Abandonné :
étendre le format du journal : action => evenements => arguments typés (arret de l'utilisation du journal) ;
détermination du subset commun aux différentes versions de tickets de taxation afin de permettre une exploitation statistique globale des données (les tables ont été unifiées) ;

90. Remerciements

Paul Courbis — 2007-01-04T17:11:04Z

Merci à...

Eric Stern qui a eu la gentillesse de me fournir des tickets de taxation afin de tester le bouzin ;
Mathieu Cousin, ancien collègue avec qui j'avais travaillé sur le sujet et archiviste talentueux.

99. Archives téléchargeables

Paul Courbis — 2007-01-04T17:11:03Z

Les version contenues ici ne sont même pas au stade alpha-test. Aucune procédure de migration d'une version n'est encore à l'ordre du jour et le passage d'une version à une autre se fait en reconstruisant complètement les données à partir des fichiers de taxation.

Version 0.0.0.1.1 du 22/1/2007 (tôt le matin)
Version 0.0.0.1.0 du 21/1/2007
Version 0.0.0.0.3 du 14/1/2007 au soir
Version 0.0.0.0.2 du 14/1/2007 au matin
Version 0.0.0.0.1 du 4/1/2007