When we need to analize much information, we can use charts in OroCRM. There are many ways to create them – in OroCRM a chart widget can be simply added by few lines of the code and configurations. But what if we need something more than having a chart with predetermined values dependency? For example, we need to build some new dependencies already from the existing values just in few simple actions and then apply some functions to them or group them by any parameter without complex modifications in the code.
In this case, we may find helpful the Grafana tool. It is used for visualizing time series metrics from the corresponding databases. If more detailed, time series is a set of certain values with some parameter at a specific time. For keeping those time series you can use a NoSQL databases or some other specific tools. Fortunately, out of the box, Grafana supports many types of databases. After some researches, we’ve decided to choose InfluxDB. This database is quite simple in installation, it has handy SQL-like query syntax and client libraries for most
popular programming languages.
Now, let’s find out how all those tools are working together.
First of all, install InfluxDB and Grafana. The detailed instruction about installation can be found in the official documentation: InfluxDB installation and Grafana installation.
As it was mentioned before, InfluxDB has libraries for many languages, and in our case we will work with PHP language. Use composer for its installation:
composer require influxdb/influxdb-php
On the next step, we add class and all further logic as shown below to make our application work with InfluxDB:
<?php
namespace App\InfluxDbBundle\Provider;
use InfluxDB\Database;
use InfluxDB\Client;
use InfluxDB\Point;
class InfluxDbProvider
{
/**
* @var string
*/
protected $host;
/**
* @var int
*/
protected $port;
/**
* @var string
*/
protected $dbName;
/**
* @var string
*/
protected $user;
/**
* @var mixed
*/
protected $password;
/**
* @var Client
*/
private $client;
/**
* InfluxDb constructor.
* @param string $host
* @param int $port
* @param string $dbName
* @param string $user
* @param mixed $password
*/
public function __construct($dbName, $user, $password, $host, $port)
{
$this->host = $host;
$this->port = $port;
$this->dbName = $dbName;
$this->user = $user;
$this->password = $password;
}
/**
* @return string
*/
public function getDbName()
{
return $this->dbName;
}
/**
* @param string $dbName
* @return InfluxDbProvider
*/
public function setDbName($dbName)
{
$this->dbName = $dbName;
return $this;
}
/**
* @param $measurement
* @param null $value
* @param array $tags
* @param array $additionalFields
* @param null $timestamp
* @return Point
*/
public function createPoint(
$measurement,
$value = null,
array $tags = array(),
array $additionalFields = array(),
$timestamp = null)
{
return new Point(
$measurement,
$value,
$tags,
$additionalFields,
$timestamp
);
}
/**
* @param Point[] $points
* @return bool
* @throws \InfluxDB\Exception
*/
public function writePoints($points)
{
$database = $this->getDatabase();
//The timestamp precision in seconds
return $database->writePoints($points, Database::PRECISION_SECONDS);
}
/**
* @return Client
*/
public function getClient()
{
if (!$this->client) {
$this->client = new Client($this->host, $this->port, $this->user, $this->password);
}
return $this->client;
}
/**
* @param bool $createIfNotExist
* @return Database
* @throws Database\Exception
* @throws \Exception
*/
public function getDatabase($createIfNotExist = true)
{
$client = $this->getClient();
$db = $client->selectDB($this->dbName);
if (!$db->exists()) {
if ($createIfNotExist) {
$db->create();
} else {
throw new \Exception('Database not exist');
}
}
return $db;
}
}
Then, add the class provider that will process data from TrackingVisitEvent entity and send them to the InfluxDB server in an appropriate format.
<?php
namespace App\InfluxDbBundle\Provider;
use Oro\Bundle\TrackingBundle\Entity\TrackingVisitEvent;
class InfluxDbPointProvider
{
/**
* @var InfluxDbProvider
*/
protected $influxDbProvider;
/**
* WebTracker constructor.
* @param InfluxDbProvider $influxDbProvider
*/
public function __construct(InfluxDbProvider $influxDbProvider)
{
$this->influxDbProvider = $influxDbProvider;
}
/**
* @param TrackingVisitEvent $visitEvent
*/
public function writeVisitEventPoint(TrackingVisitEvent $visitEvent)
{
// Will use for aggregations grafana functions
// example count(visit_events) group by (visitor_uid)
$pointTags = [
'id' => $visitEvent->getId(),
'user_identifier' => strval($visitEvent->getWebEvent()->getUserIdentifier()),
'url' => strval($visitEvent->getWebEvent()->getUrl()),
'name' => strval($visitEvent->getWebEvent()->getName()),
'visitor_uid' => strval($visitEvent->getVisit()->getVisitorUid()),
'desktop' => intval($visitEvent->getVisit()->isDesktop()),
'mobile' => intval($visitEvent->getVisit()->isMobile()),
'bot' => intval($visitEvent->getVisit()->isBot()),
];
$pointAdditionalFields = [];
$point = $this->influxDbProvider
->createPoint('visit_event',
$visitEvent->getId(),
$pointTags,
$pointAdditionalFields,
$visitEvent->getWebEvent()->getCreatedAt()->getTimestamp() // Point time
);
$this->influxDbProvider
->writePoints([$point]);
}
}
Moreover, if we want to track changes in our application for TrackingVisitEvent we can, for example, add the entity listener:
public function postPersist(LifecycleEventArgs $args)
{
$entity = $args->getEntity();
if (!$entity instanceof TrackingVisitEvent) {
return;
}
try{
$this->influxDbPointProvider
->writeVisitEventPoint($entity);
} catch (InfluxDBException $e){
//do something with exception
}
}
And since our event listener cannot process existing records from tracker, we just add the command that will get all the records from the tracker and send them to InfluxDB.
<?php
namespace App\InfluxDbBundle\Command;
use Doctrine\ORM\QueryBuilder;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use \InfluxDB\Exception as InfluxDBException;
class WritePointsCommand extends ContainerAwareCommand
{
protected function configure()
{
$this
->setName('influxdb:write-tracker-points')
->setDescription('Writes tracker events time points');
}
protected function execute(InputInterface $input, OutputInterface $output)
{
$repository = $this->getContainer()
->get('doctrine')
->getRepository('OroTrackingBundle:TrackingVisitEvent');
/** @var QueryBuilder $qb */
$qb = $repository->createQueryBuilder('visit');
$visitEvents = $qb
->select(['visit','event'])
->join('visit.webEvent','event')
->getQuery()
->getResult();
if(!empty($visitEvents)){
$pointsProvider = $this->getContainer()
->get('app_influxdb.point_provider');
foreach($visitEvents as $visitEvent){
try{
$pointsProvider->writeVisitEventPoint($visitEvent);
} catch(InfluxDBException $e){
//do something with exception
}
}
}
$output->writeln("<info>Success</info>");
}
}
Here is the result that we should have in InfluxDB:
So, now what should be done with Grafana?
First of all, we need to add the “data source” like this:
Then, add “row” via the dashboard editing:
As follows, in that “row”, select the “data source” that we’ve already added:
And now we can build the graphs for our data. Here we have a graph for number of events for each of the trackers “user_identefier”:
For example, if we add this query:
That will return a graph of visits for “name=visit”:
As a summary of what has been already mentioned, we can say that Grafana is a very handy tool for building charts in OroCRM. We highlighted how to work with Grafana and InfluxDB and use them for different tracking operations. Try how it works by yourself and leave your feedback in the comments below.