Building a Command-Line Reporting Task with JavaScript and Wakanda

Saad Mousliki shares a tutorial on working within Wakanda to create a sys-admin reporting task with JavaScript.

JavaScript is usually not the language system admins think of to handle building command line tools for automating system tasks. However, it turns out that it is perfectly capable of doing so. In this article we’ll take a look at how JavaScript can be used to build a command line tool that solves a reporting task.

I was trying to find a way to extract reports from a MySQL database, putting them in a .csv file and sending the file in an email as an automated weekly report. Initially I tried using a Windows scheduled task or a Linux cron. However, because the application that populates the MySQL database was developed in PHP and because I’m more comfortable with JavaScript and Wakanda, I started thinking about how could I use my loved language JavaScript and Wakanda to achieve the result.

The MySQL Database

OCS Inventory and GLPI are the most well-known open source applications that are used together to achieve the IT asset management. The two applications are developed using PHP, Perl and MySQL and are used by more than 500 companies over the world to manage assets that can go on anywhere from 10 to 200,000 workstations. OCS Inventory is used to collect hardware/software information from the workstations and store it in its MySQL database, and GLPI can be synchronized with OCS using a the OCS-NG plugin to log and manage the IT assets collected by OCS. It has many other features like ticketing, IT asset budget management and others.

We could represent the architecture of the inventory chain as follows

An OCS agent in the workstations collects and sends any changes in the inventory to the OCS Server. The OCS Server updates the MySQL DB with the new snapshot of the workstations and the GLPI Server is connected to the OCS server to synchronize its MySQL DB and logs any hardware/software changes.

In my use case, I needed to get the history of changes in the workstations asset by receiving a weekly report about the workstations including where memory, hard drives, peripherals or monitors may have changed. This way I could detect the authorized and unauthorized actions on a workstation’s assets. Obviously, this was my specific use case but there are plenty of other ways this information could be utilized.

Prerequisites

To achieve the weekly report, we won’t require a MySQL connector or a runtime environment installed on our machine or server. All we need is to download the Wakanda All in one Enterprise package build 8.162963.

Setup

After downloading Wakanda All In one package, first unzip the file which will give you two primary folders: Wakanda Server and Wakanda Studio. The Studio will be our IDE to code and debug our script and the Server will be used to run the JavaScript script file from the command line.

Open Wakanda Studio by opening Wakanda Studio in the Wakanda Studio folder. Next, create a new Solution with a new project named “GetWeeklyReports”:

Next, add a new JavaScript file to your project, making sure that the file is added on server side part of your project and not the client side. The file should be created outside the web folder as seen in the following screenshot:

Give the file the name getReports.js. Let’s start by performing a simple “hello world” test to make sure that our Wakanda Server is running as expected and that we haven’t any issue during the installation. Add the following command to show “Hello World” in the prompt to your JavaScript file:

console.info("Hello World");

To execute the test script and check if the Wakanda Server is correctly installed, open a command line, pass the path to the Wakanda Server followed by a space and the path to the JS script file, and then run the command:

The same process should be followed in Mac or Linux, for more details you could refer to this doc.

Connect to the Database

After making sure that the process is functional we’ll start writing our script. Start by importing the waf-sql Wakanda module, and setting the connection parameters to MySQL Server:

var connector = require('waf-sql');

var params = {
    dbType: 'mysql', // specify the DB type it could SSQL
    hostname: "140.150.5.11",// the IP address of the GLPI MySQL server
    user: "glpiuser",  // the GLPI MySQL DB user
    password: "glpipwd",  // the GLPI MySQL DB password
    database: "glpi",   // the DB name
    port: 3306,   // the MySQL port
    encryptionMode: 'not supported'
};

The first line of code is used to import the waf-sql module which will allow us to connect to the MySQL DB and execute the query to get the data which will be in the weekly reports.

After that we should initiate the connection by creating a session to the MySQL DB, and execute the query using the execute() method of the waf-sql module. The result of the query execution will be set in an array variable:

try {
    var session = connector.connect(params); //use session to manage data
    var query_on_logs = "SELECT `glpi_logs`.`date_mod` AS dat, `itemtype_link`,`old_value`, `new_value`, name FROM `glpi_logs` LEFT JOIN `glpi_computers` ON (`glpi_logs`.`items_id` = `glpi_computers`.`id`) WHERE `glpi_logs`.`date_mod` > DATE_SUB(Now(), INTERVAL 7 DAY) AND `itemtype` = 'Computer' AND `itemtype_link` IN ('DeviceHardDrive','DeviceDrive','DeviceMemory','Printer','Monitor','Peripheral','DeviceNetworkCard') ORDER BY `glpi_logs`.`id` DESC";

    var result = session.execute(query_on_logs).getAllRows();
}
catch (e) {
    console.info("MySQL connection issue : " + e.message);
}
session.close(); // close connection

The try/catch statement is used to handle any exception (for example, the server is down, the network is down, errors in the connection parameters, etc.) and display it on the command line using console object method info(). Finally, don’t forget to close the MySQL session after executing your SQL queries.

If all is going well, when we run this script in Wakanda Studio, we will get the SQL query result in the result variable. to test those lines of codes in Wakanda Studio, we could put a debugger; statement or a breakpoint after getting the SQL query result as in the following screenshot:

For more details about how to debug the code in Wakanda Studio, take a look to this doc.

Working with the Result

Now that we’re connected and received the result of the query correctly, we should have a JavaScript array containing the result of the SQL query. We need now to convert this JavaScript array to .csv file, so let’s write a method that accepts an array as a parameter and returns a .csv file.

function DownloadJSON2CSV(objArray) {

    var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;

    var str = '';

    var d = new Date()
    var df = "_" + d.getMonth() + '-' + d.getDate() + '-' + d.getYear();

    try {

    var currentFolder = "C:/Users/saad/Documents/Wakanda/GetWeeklyReports/ GetWeeklyReports /"; 

    var csvReport = File(currentFolder + "modification" + df + ".csv");
    if (csvReport.exists) csvReport.remove();
    var stream = TextStream(csvReport, "write", 7);

    stream.write(" Modification date, Modification type , Old value , new value , computer" + 'rn');

    for (var i = 0; i < array.length; i++) {
        var line = '';

        for (var index in array[i]) {
            line += array[i][index] + ',';
        }

        line.slice(0, line.Length - 1);

        stream.write(line + 'rn');

    }

    stream.close();
}
catch(e){
  console.info("CSV File creation issue : " + e.message);  
}

    return csvReport;
}

The first line of the code checks whether the passed argument is actually a JavaScript array. Assuming it is, the execution continues and we will create a date sting using the Date() that contains the current date which will be added to the .csv file name.

Using the Wakanda File() method, we will create a file by supplying its full path and name. If the file exists in the current folder we’ll remove it and create a new one. Uusing the textStream() method we’ll write the data into the .csv file from the JavaScript array, using commas as a delimiter. When finished, we close the textStream and return the .csv file.

Sending an Email with the Data

The result of the SQL query can also be added to the email body as an HTML table using the setBodyAsHTML() method.

We could use the following function to convert the result of the SQL query to a HTML table and after that add it to the body of the message:

function convertToHTMLtable(resultArray) {

    var resultAsHTML = '<table>';
    resultAsHTML += '<tbody>';
    resultAsHTML += '<tr><td><b> Date de changment </b></td><td><b> Type de changement </b></td><td><b> Ancienne Valeur </b></td><td><b> Nouvelle Valeur </b></td><td><b> Ordinateur </b></td></tr>';

    var row = 0;

    for (var i = 0; i < resultArray.length; i++) {

        resultAsHTML += ((row % 2 == 0) ? '<tr style="background-color: #fdbd63">' : '<tr style="background-color: #b0cdcb">');
        for (var index in resultArray[i]) {

            resultAsHTML += '<td>  ' + resultArray[i][index] + '  </td>';

            row++;
        }
        resultAsHTML += '</tr>';
    }
    resultAsHTML += '</tbody>'
    resultAsHTML += '</table>';
    return resultAsHTML;
}

The result of this function can be added to message’s HTML body as in the following code:

var rsltHTML = convertToHTMLtable(result);
message.setBodyAsHTML ('<html><b><i>Hello, <br> <br>   The following table contains the list of hardware changes in the workstations during the last 7 days <br> <br>  Best Regards </b><i></html> <br> <br> <br> <br>'+ rsltHTML);
     Now we have the CSV file, but we haven’t yet finished we need to attach it to an email and send it, so we will create another method that accept as  an argument the CSV file and send the email using the exchange server to a my Gmail address :
//create the email message
var mail = require("waf-mail/mail");
var message = new mail.Mail();
message.subject = "Hardware modifications in the last 7 days";
message.from = "saad1@mydomain.com";
message.to = "saadmos06@gmail.com";
message.setContent("Hello, " + 'rnn' + " Following a file which contains a list of modifications in the hardware of the workstations " + 'rn'+'n' + "Best Regards");
message.addAttachment(rsltCSV, "modifications.csv", "text/csv", "ID123", true);

First of all, we need to import the waf-mail Wakanda module which will be used to send the email using the SMTP connection to the Exchange server. After that we create and set the parameters of the message.

To send the email I’ll use the sendEmail() function that was shared via the Wakanda forum:

function sendEmail(config, message) {
    "use strict";

    var smtp = require("waf-mail/smtp"),
        client, errName, errInfo, isSent;

    //init error tracking
    errName = "";
    errInfo = [];

    //connect
    client = new smtp.SMTP();
    client.connect(config.address, config.port, config.isSSL, config.domain, function onAfterConnect(isConnected, replyArr, isESMTP) {
        if (isConnected) {

            //authenticate
            client.authenticate(config.user, config.password, function onAfterAuthenticate(isAuthenticated, replyArr) {
                if (isAuthenticated) {

                    //send
                    client.send(message.from, message.to, message, function onAfterSend(isSent, replyArr) {
                        if (isSent) {
                            exitWait();
                        }
                        else {
                            errName = "smtp_SendFailed";
                            errInfo = replyArr;
                            exitWait();
                        }
                    });
                }
                else {
                    errName = "smtp_AuthFailed";
                    errInfo = replyArr;
                    exitWait();
                }
            });
        }
        else {
            errName = "smtp_CouldNotConnect";
            errInfo = replyArr;
            exitWait();
        }
    });
    wait();

    //determine if sent
    if (errName === "") {
        isSent = true;
    }
    else {
        isSent = false;
    }

    //return if sent and any error info
    return {
        isSent: isSent,
        errName: errName,
        errInfo: errInfo
    };
}

The function uses the waf-mail SMTP client to connect to the Microsoft Exchange server and send the email. We need to pass as a parameter an object containing the Exchange server IP address and port, the sender credentials, the domain name, and the isSSL parameter.

try {
        //send the email message
        sendEmail({
            address: "140.150.2.11",
            port: 25,
            isSSL: false,
            domain: "mydomain.com",
            user: "saad1@mydomain.com",
            password: "Pa$$word"
        }, message);
}
catch(e){
    console.info("SMTP issue : " + e.message);
}

Finishing Up

We’ve now finished all the parts of our script: connecting and querying the MySQL DB; converting the result to a CSV file; and attaching the CSV file to an email and sending it. The full script could be found on GitHub.

The next step, after running and debugging the script in Wakanda Studio, is to use the Wakanda Server via the command line to execute the JavaScript file.

After executing the script, you should receive an email containing the reports in a CSV file.

The final step is to automate the email using a scheduled Windows task. First, copy the command that we have tested into a .bat file and then configure the task manager to execute the .bat file each week.

We could also achieve the same automation using a Linux cron and the Wakanda Server for Linux Ubuntu distribution. For example, we could add this line to the crontab in Linux :

# The script will be executed each Saturday at 8:05  
5 8 * * 6 ./wakanda gerReports.js

Conclusion

As we have seen, bycombining JavaScript and Wakanda, we can go far beyond simple shell scripts. We can connect to a MySQL database, create a file and send an email. The script I have created is executed once a week and I’m enjoying the creation of other reports using the same steps by adding some customization to the SQL query and other functions.