Antoine LECOZ
Antoine LECOZ
Project Manager Developer Security Engineer Data Analyst
Antoine LECOZ

Blog

Les 24H du code 2019

Les 24H du code 2019

Introduction

Organisé par la CCI et l'Ensim, les 24h du code sont un défi pour les passionnés d'informatique. L'édition 2019 s'est déroulée le 19 et 20 Janvier.

Je tiens tout d'abord à saluer l'organisation de cet événement qui mobilise de nombreux volontaires, Merci.

Pour ma part c'était une première participation, et j'espère bien revenir l'année prochaine !

Notre équipe composée de 5 étudiants a choisi le sujet de Sopra Steria parmis les 4 proposées ; au programme : des données, des graphes, du code et du BI...

Le sujet

 


Etape 1 : Dévérouillage du fichier

>10H30

Nous avons reçu un fichier .zip protégé par un mot de passe ainsi que cette lettre indiquant que les données se trouvent à l'intérieur. 

Un indice ? "Le mot de passe est peu complexe et pourrait même être deviné". 

Après identification du fichier, il s'avère être protégé par un cryptage peu commun "PKZIP2", qui n'est à priori pas supporté par HashCat ni par John. Au lieu de bruteforcer le Hash on va donc directement tester les mots de passe via décompression. 

Fcrackzip est parfait pour çela : en utilisant un charset numérique, de 4 à 8 caractères, le mot de passe est découvert en quelques minutes : 07091923 soit la date de création d'Interpol! 

Les données

Le dossier contient un fichier .sql, qui une fois importé dans un SGBD (ici SQLServer) nous donne une petite idée de ce qui nous attend.


Etape 2 : Initiation à Neo4j

>11H00

Neo4j est un système de gestion de base de données au code source libre basée sur les graphes, développé en Java par la société suédo-américaine Neo technology.

Cet outil nous est totalement inconnu, il faut donc le prendre en main. La documentation est plutôt complète cependant la communauté est faible et il est difficile de trouver du support. 

Cet article explique plutôt bien le concept et permet une première prise en main.

Une fois la base créée, le naviguateur disponible à l'adresse http://localhost:7474/browser/

Cypher

Avec Neo4j, pas question de SQL, il nécessite son propre langage de requête : le Cypher

Pattern recognition is fundamental to the way that the brain works. Because of this, humans are very good at working with patterns (think of visual diagrams or even memory-matching games). Cypher is also heavily based on patterns and is designed to recognize various versions of these patterns in data, making it a simple and logical language for users to learn.

 


Etape 3 : Représenter les données

>15H30

C'est l'étape qui nous a posé le plus de difficultés : les données qui nous sont fournies sont structurées en base de donnée relationnelle, mais Neo4j fonctionne avec un modèle orienté graphes !

Migration du modèle

En suivant cet article , nous avons finalement pu établir le nouveau modèle de données. On passe de 12 entités à 5 !

Création de la base

Après avoir exporté chaque table en fichier CSV, on peut créer le modèle, les relations et insérer les données en Cypher :

CREATE CONSTRAINT ON (v:Ville) ASSERT v.id IS UNIQUE;
CREATE CONSTRAINT ON (p:Personne) ASSERT p.id IS UNIQUE;
CREATE CONSTRAINT ON (e:Entreprise) ASSERT e.id IS UNIQUE;
CREATE CONSTRAINT ON (d:Domaine_influence) ASSERT d.id IS UNIQUE;
CREATE CONSTRAINT ON (c:Ecole) ASSERT c.id IS UNIQUE;
---------------------------------------------------------------------------------------------------------------
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/ville.csv' AS line
CREATE (ville:Ville {id: toInteger(line.id) })
SET ville.nom = line.nom,
    ville.pays = line.pays
RETURN ville;
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/personne.csv' AS line
CREATE (personne:Personne {id: toInteger(line.id) })
SET personne.nom = line.nom,
    personne.prenom = line.prenom
RETURN personne;
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/ecole.csv' AS line
CREATE (ecole:Ecole {id: toInteger(line.id) })
SET ecole.nom = line.nom
RETURN ecole;
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/domaine_influence.csv' AS line
CREATE (domaine_influence:Domaine_influence {id: toInteger(line.id) })
SET domaine_influence.libelle = line.libelle
RETURN domaine_influence;
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/entreprise.csv' AS line
CREATE (entreprise:Entreprise {id: toInteger(line.id) })
SET entreprise.nom = line.nom
RETURN entreprise;
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/personne.csv' AS line
MATCH (personne:Personne {id: toInteger(line.id) })
MATCH (ville:Ville {id: toInteger(line.ville_id) })
CREATE (personne)-[:IN_VILLE]->(ville)
RETURN personne, ville;
----------------------------------------------------------------------------------------------------------------------
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/personne_influence.csv' AS line
MERGE (domaine_influence:Domaine_influence {id: toInteger(line.influence_id) })
MERGE (personne:Personne {id: toInteger(line.personne_id) })
CREATE (domaine_influence)-[:INFLU]->(personne)
RETURN domaine_influence, personne;
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/personne_entreprise.csv' AS line
MERGE (personne:Personne {id: toInteger(line.personne_id)})
MERGE (entreprise:Entreprise {id: toInteger(line.entreprise_id)})
CREATE (entreprise)-[e:PERSONNE_ENTREPRISE]->(personne)
SET e.arrivee = line.arrivee,
e.depart = line.depart;
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/personne_ecole.csv' AS line
MERGE (personne:Personne {id: toInteger(line.personne_id) })
MERGE (ecole:Ecole {id: toInteger(line.ecole_id) })
CREATE (personne)-[:PERSONNE_ECOLE]->(ecole)
RETURN ecole, personne;
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/ecole_influence.csv' AS line
MERGE (domaine_influence:Domaine_influence {id: toInteger(line.influence_id) })
MERGE (ecole:Ecole {id: toInteger(line.ecole_id) })
CREATE (domaine_influence)-[:ECOLE_INFLU]->(ecole)
RETURN ecole, domaine_influence;
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/entreprise_influence.csv' AS line
MERGE (entreprise:Entreprise {id: toInteger(line.entreprise_id)})
MERGE (domaine_influence:Domaine_influence {id: toInteger(line.influence_id)})
CREATE (entreprise)-[:ENTREPRISE_INFLU]->(domaine_influence);
LOAD CSV WITH HEADERS FROM 'https://www.antoine-lecoz.fr/24h/test/tbl_amis.csv' AS line
MERGE (personne1:Personne {id: toInteger(line.ami_de_id)})
MERGE (personne2:Personne {id: toInteger(line.ami_id)})
CREATE (personne1)-[:AMIS]->(personne2);

Pour les équipes n'ayant pas encore validé cette étape, une base fonctionnelle est fournie en début de soirée.

Importer un dump Neo4j : neo4j-admin load --from= --database= [--force]

Représentation

Une fois la base constituée, la représentation graphique des relations devient très intéressante !

Image 1 : Les relations d'amitié entre les individus

Image 2 : Les individus ayant travaillé à un endroit spécifique

 


Etape 4 : Trouver le leader

>21H00

1) Trouver le recruteur

Nous recevons cettre lettre. Nous savons donc que le Leader est en relation avec le Recruteur R372, dont on doit d'abord déterminer l'identité.

Il est expliqué que leurs matricules dépendent du nombre de personnes qu'ils peuvent atteindre jusqu'aux hommes de main : on recherche donc la personne qui possède 372 relations comprenant les amis et les amis des amis.

La requête Cypher suivante retourne la liste des individus et leurs nombre de relations directes avec une recursivité de niveau 2 :

MATCH(p:personne)-[:EST_AMI_AVEC*0..2]-(e:personne) RETURN e.nom,e.prenom,count(p) as cnt ORDER BY cnt DESC

On obtient le nom de Sidney Kline!

>23H00

2) Trouver le leader

Nous recevons le 1er indice pour localiser le leader : celui-ci réside aux Etats-Unis.

On cherche donc un individu, ami avec Sidney Kline, et qui vis aux Etats-Unis. Il n'existe pas d'entité "Pays", cette information est présente dans l'entité "Ville".

La requête Cypher suivante permet donc de trouver la bonne personne : 

MATCH (p:personne)-[:HABITE]->(v:ville)
MATCH(v:ville)-[:VILLE_DANS]->(pa:pays)
MATCH(p:personne)-[:EST_AMI_AVEC]->(t:personne)
WHERE pa.nom = 'Etats Unis' AND t.nom='Kline' AND t.prenom='Sidney'
RETURN p.nom,p.prenom,pa.nom

Un seul résultat est retourné, le leader est Braden Kline!

D'autres indices ont été divulgués plus tard dans la soirée, nous n'en avons pas eu besoin : 

-Le leader a travaillé à Virtual Instruments

-Le leader a étudié à l’université de Cambridge

 


Etape 5 : IHM

>1H00

La dernière étape consiste à proposer une interface pour visualiser les données :

-Les contacts directs d'un invidiu

-Ses domaines d'influence

-Cartographier géographiquement les domaines d'influences

Client C#

>3h00

Dans un premier temps pour visualiser les relations et les domaines d'influence directs et indirectes pour chaque personne, nous développons un client lourd en C# (Winforms). 

Celui-ci permet de :

  • Configurer et se connecter à sa base Neo4j
  • Lister et rechercher les personnes
  • Afficher leurs amis
  • Afficher leurs domaines d'influence directs et indirects

Neo4j n'est pas supporté nativement par .NET, cependant une bibliothèque est officiellement mise à disposition par Neo4j :

PM> Install-Package Neo4j.Driver-1.7.0

La connexion à la base se fait, via le code : 

using Neo4j.Driver.V1;

IDriver driver = GraphDatabase.Driver("host", AuthTokens.Basic("username", "password"));

Un exemple de requête :

using (ISession session = driver.Session())
{

IStatementResult result = session.Run("MATCH (d:domaine_influence)<-[:EST_INFLUENT]-(e:personne) WHERE e.nom = '" + selected_nom + "' AND e.prenom = '" + selected_prenom + "' RETURN e.nom,e.prenom,d.libelle");

lstInfluences = result.Select(record => record["d.libelle"].As()).ToList();

}

Power BI

>5h00

Pour la partie cartographie, ca sera Power BI ! Puissant outil décisionnel, il convient très bien à ce genre de besoin.

Il s'agit désormais de mettre en évidence sur une carte du monde, les zones d'influence sur un domaine spécifique, sur une personne spécifique.

1) Préparer les données

La 1ère étape consiste à effectuer une requête et exporter un CSV qui servira de source pour Power BI. 

On retourne, pour chaque personne, n lignes pour leurs chaque domaine d'influence directe ou indirecte (profondeur max 3) et la ville dans laquelle ils ont cette influence concaténée avec le pays (pour permettre à l'outil de les localiser avec plus de fiabilité).

2) Représentation des données

Le report est toujours accessible ici

->Afficher l'emplacement des domaines d'influence en filtrant par personne

-> Découpage en camembert des villes ayant n domaines d'influence

-> Découpage en camembert des villes ayant n domaines d'influence


Conclusion

Nous sommes venus pour nous amuser avant tout et découvrir de nouvelles technologies, de nouveaux concepts.

C'est chose faite avec ce sujet très intéressant et amusant à réaliser !

Petit bonus, notre équipe "LGB" a terminé 1ère du classement étudiant sur ce sujet !

A l'année prochaine :)

Team L.G.B

Add Comment