How to Create Analytics and gadget Dashboard for Logs

Assume you have configured logs according to my Previous Article, you will get logs from different nodes to BAM which will resides in cassandra data store. If you wont to analyzed those log data, a very simple use case would be to get number of logs by the priority.

Lets see how we can do it

Step 1 – Write an hive script to retrieve data from cassandra and store it in a relational database.

First we will use hive query language to retrieve the needed data and we’ll store it in relational database so we can create gadgets against relational data.

My simple query is retreive all log infomation to mysql database. (You can write your own logic accordingly)

Default keyspace is - EVENT_KS

My needed CF is – log_0_AS_2013_01_07

My Mysql database - MYBAMDB

Go to BAM management console -> Analytics -> Add-> and copy paste the above query. Please change the necessary parameters accordingly ( cassandra.cf.name,mapred.jdbc.url', mapred.jdbc.username, mapred.jdbc.password)

CREATE EXTERNAL TABLE IF NOT EXISTS LogEventInfo (key STRING,
tenantID INT,serverName STRING,
appName STRING, priority STRING,logTime DOUBLE,logger STRING,message STRING) STORED BY
'org.apache.hadoop.hive.cassandra.CassandraStorageHandler' WITH SERDEPROPERTIES ( "cassandra.host" =
"localhost",
"cassandra.port" = "9160","cassandra.ks.name" = "EVENT_KS",
"cassandra.ks.username"
= "admin","cassandra.ks.password" = "admin",
"cassandra.cf.name" = "log_0_AS_2013_01_07",
"cassandra.columns.mapping" =
":key,payload_tenantID,payload_serverName,payload_appName,
payload_priority,payload_logTime,payload_logger,payload_message"
);

CREATE EXTERNAL TABLE IF NOT EXISTS Logs(tenantID INT,serverName STRING,
appName STRING, priority STRING,logTime DOUBLE,logger STRING,message STRING)
STORED BY 'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES (
'mapred.jdbc.driver.class' = 'com.mysql.jdbc.Driver',
'mapred.jdbc.url' =
'jdbc:mysql://localhost:3306/MYBAMDB',
'mapred.jdbc.username' =
'root','mapred.jdbc.password' = 'root',
'hive.jdbc.update.on.duplicate' = 'true',
'hive.jdbc.table.create.query' =
'CREATE TABLE LogEvent(tenantID INT,serverName
VARCHAR(200),
appName VARCHAR(200), priority VARCHAR(200),logTime DOUBLE,logger VARCHAR(800),message VARCHAR(3800))');
insert overwrite table Logs select tenantID, serverName, appName, priority, logTime,
logger, message from LogEventInfo;
select tenantID, serverName, appName, priority, logTime,
logger, message from LogEventInfo;

In the first part of hive query it will map the cassandra data store to a storage handler which can extract data from cassandra. The second part we map the cassandra storage handler data to relational database storage handler to have the pipe between cassandra and relational database. The third part is where you write your logic for the analytics.

You can test your script by clicking execute. And if no errors occur go to your mysql database and check for the data.

Once you have done writing the hive script you can schedule it and save the query.

Step 2 – Writing gadgets

Once we have written our analytics, we can write gadgets using gadget gen tool.

Go to Management Console -> Tools-> Gadget gen tool

Give your database credentials as shown below

Write your SQL script with your analytical logic.

In my case I am going to get a count of number of log messages per priority.

My SQL would look like

select count(tenantID), priority From LogEvent group by priority;

Click on next and select the type of UI element you wont and fill the necessary information.

Fill the gadget Details and Click on generate to generate the gadget.

Step 3 – Add gadgets to gadget dashboard

Once we create the gadget we can go view the gadgets by adding the gadgets to the BAM dashboad.

Before we do that we need to add the gadget to the gadget repository from the registry. When we create the gadgets, gadgets will be saved in the registry repository therefore , we need to add them to the gadget repo.

Go to gadget Repository under gadgets in BAM management console. And click on add gadget.

Give the needed infomation as shown below and add the gadget to gadget repository.

Then you can go to view portal -> Add gadget from the gadget repo and view your gadgets.