Putting groov log into a groov data store - Read intro on first post!


#1

Edit by @Beno - Please use this with some caution. The log file formatting may change at any time and while this is very cool code and a great work around, it could ‘break’ at any time.
Keep in mind that Opto wants to provide an official gadget to do this exact function and also provide a method to view/track/log groov user/operator changes/interactions.

We are going to leave this post up as some great sample code for looking at file structures and displaying in groov.

The information from https://<groovIP>/#log can be very valuable, but it’s a fully unfiltered stream, I have to open a whole new tab to view it, and it has a lot of information that I am honestly not interested in.
Thankfully the stream is also written to a local data base file logs.db which I can access with a bit of Node-RED to get those strings filtered and written to a data store table to view on any groov page!

In this example I am using two layers of filtering, one to specify which ‘LOGGER’ columns I gather from - I get 50 events (rows) from com.opto22.groov.server.security.Security and groov.Audit - and another filter to get ten of those that contain the keywords “wrote” or “logged” in the ‘MESSAGE’ column.
The idea is that I only get login and gadget activity, but you can easily change this to filter for whatever you wish, even get activity from a specific user or users by picking out specific keywords in the #log stream.

Once filtered this data goes into the groov tag event_list[10]:

To get the data I build a SELECT statement, fetch the rows from logs.db with a sqlite node, format and package the payload returned using a little JavaScript, then write to a groov data store table event_list using this flow here (import text at the bottom of this post):

My select top 50 * from LOGS function node just holds the following:

return { topic : "SELECT * FROM LOGS WHERE LOGGER == \"groov.Audit\" OR "+
        "LOGGER == \"com.opto22.groov.server.security.Security\" "+
        "ORDER BY TIMESTAMP DESC LIMIT 50;"};

This topic string sets the database query, which I use to grab the 50 latest rows from C:\Program Files (x86)\Opto22\groov\logs.db if using groov Server for Windows (or /var/lib/jetty/optoapps/logs.db if using sqlite node on an AR1), which is the local data base file containing the info from https://<groovIP>/#log.
Using this the sqlite node returns an array of objects containing each log entry’s timestamp, message, level, and logger, where I am limiting which loggers may be returned.

Once I have these rows I check to see if each message contains the keywords “wrote” or “logged”, and if it does I reformat the time and package it up with the message data into a string and push that into an array of objects that will write that big string to the groov data store tag event_list using a previously posted tip. If of the 50 limited rows returned there are not at least 10 that have these keywords, I instead return the string “insufficient data”. All of this is handled by this JavaScript in the function node:

tag = 'event_list'; // groov data store tag name (type string table)
tablen = 10; // how many slots available in 'tag'
data = []; // array of objects that make up the table in groov
index = 0; // groov table index (not the same as payload index)
for(i = 0; i < msg.payload.length; i++) // i = payload array index
{
    if(msg.payload[i].MESSAGE.includes("wrote") || msg.payload[i].MESSAGE.includes("logged"))
    {
        var d = new Date(msg.payload[i].TIMESTAMP); // TIMESTAMP is in epoch
        formtime = d.toISOString().substring(0,10)+
            ' @ '+d.toISOString().substring(11,16); // format time: "yyyy-mm-dd @ hh:mm"
        data.push({ // new groov tag entry, this string goes at <index> in <tag>
            tagName         : tag,
            tableStartIndex : index,
            value           : (formtime + ", " + msg.payload[i].MESSAGE) // big string
        });
        index++; // next spot in the table...
    }
    if(index == tablen) break; // filled up the maximum table length, we're done!
}
missing = tablen - data.length; // for the values expected but not returned,
for(i = 0; i < missing; i++)
{
    data.push({ // new groov tag entry -> dummy object
        tagName         : tag,
        tableStartIndex : index,
        value           : "insufficient data"
    });
    index++;
}
// return the data objects as the payload for split -> groov write
return {payload : data};

The result looks something like this:

Of course it is totally possible to search the returned strings for different data, package it differently, or display it in groov in any one of a huge variety of ways – this is just a baseline on how to get ahold of the data in Node-RED and one way it can be displayed in groov.

Happy coding!

[{“id”:“9703280c.595648”,“type”:“sqlite”,“z”:“4138159b.03e25c”,“mydb”:“635948df.833b28”,“name”:"",“x”:230,“y”:440,“wires”:[[“fcee3ce5.569c2”]]},{“id”:“75555c2a.002fa4”,“type”:“inject”,“z”:“4138159b.03e25c”,“name”:"",“topic”:"",“payload”:"",“payloadType”:“date”,“repeat”:“30”,“crontab”:"",“once”:true,“x”:130,“y”:360,“wires”:[[“3381144a.3a65ac”]]},{“id”:“3381144a.3a65ac”,“type”:“function”,“z”:“4138159b.03e25c”,“name”:“select top 50 * from LOGS”,“func”:“return { topic : “SELECT * FROM LOGS WHERE LOGGER == \“groov.Audit\” OR “+\n “LOGGER == \“com.opto22.groov.server.security.Security\” “+\n “ORDER BY TIMESTAMP DESC LIMIT 50;”};”,“outputs”:1,“noerr”:0,“x”:170,“y”:400,“wires”:[[“9703280c.595648”]]},{“id”:“fcee3ce5.569c2”,“type”:“function”,“z”:“4138159b.03e25c”,“name”:“format time, package event_list”,“func”:“tag = ‘event_list’; // groov data store tag name (type string table)\ntablen = 10; // how many slots available in ‘tag’\ndata = []; // array of objects that make up the table in groov\nindex = 0; // groov table index (not the same as payload index)\nfor(i = 0; i < msg.payload.length; i++) // i = payload array index\n{\n if(msg.payload[i].MESSAGE.includes(“wrote”) || msg.payload[i].MESSAGE.includes(“logged”))\n {\n var d = new Date(msg.payload[i].TIMESTAMP); // TIMESTAMP is in epoch\n formtime = d.toISOString().substring(0,10)+\n ’ @ '+d.toISOString().substring(11,16); // format time: “yyyy-mm-dd @ hh:mm”\n data.push({ // new groov tag entry, this is goes at index in ‘event_list’\n tagName : tag,\n tableStartIndex : index,\n value : (formtime + “, " + msg.payload[i].MESSAGE) // big string\n });\n index++; // next spot in the table…\n }\n if(index == tablen) break; // filled up the maximum table length, we’re done!\n}\nmissing = tablen - data.length; // for the values expected but not returned,\nfor(i = 0; i < missing; i++)\n{\n data.push({ // new groov tag entry -> dummy object\n tagName : tag,\n tableStartIndex : index,\n value : “insufficient data”\n });\n index++;\n}\n// return the data objects as the payload for split -> groov write\nreturn {payload : data};”,“outputs”:1,“noerr”:0,“x”:270,“y”:480,“wires”:[[“f15d7087.d27cb”]]},{“id”:“f15d7087.d27cb”,“type”:“split”,“z”:“4138159b.03e25c”,“name”:””,“splt”:”\n”,“spltType”:“str”,“arraySplt”:1,“arraySpltType”:“len”,“stream”:false,“addname”:”",“x”:350,“y”:520,“wires”:[[“8509409b.bf3be”]]},{“id”:“8509409b.bf3be”,“type”:“groov-write-ds”,“z”:“4138159b.03e25c”,“dataStore”:“48acfab7.997ef4”,“tagName”:"",“tableStartIndex”:"",“value”:“payload.value”,“valueType”:“msg”,“name”:"",“x”:370,“y”:560,“wires”:[[]]},{“id”:“635948df.833b28”,“type”:“sqlitedb”,“z”:"",“db”:“C:\Program Files (x86)\Opto22\groov\logs.db”},{“id”:“48acfab7.997ef4”,“type”:“groov-data-store”,“z”:"",“project”:“8c04a671.242118”,“dsName”:“NodeRED”},{“id”:“8c04a671.242118”,“type”:“groov-project”,“z”:"",“address”:“localhost”}]


Log Viewer Feature
Groov log details on a groov screen
#2

Unfortunately I have had some issues getting the sqlite node package installed correctly on my AR1, while I’m still tracking down what’s going wrong there I have built a workaround using the sqlite3 console command through an exec node (this should apply any other sqlite database as well!)

The downside to using this method is that sqlite3 does not return a nicely packaged msg.payload[#].COLUMN_NAME object that I can easily iterate through, it returns a very long string with each row separated by the new line \n character and each column in that row separated by the | character. Thankfully this is consistent so it’s easy to disassemble the string using these as markers.

Instead of using msg.topic to hold the query I set the exec node to append the msg.payload:

And I build my payload in the first function node, with a space at the start since it is stuck directly to the end of this sqlite3 /var/lib/jetty/optoapps/logs.db command, and I make sure the quotation marks are sustained by using \\\" to provide the exec node with \" so that the string is executed with just the " quote character.
It’s not super pretty but it works great:

return { payload : " \"SELECT * FROM LOGS WHERE LOGGER == \\\"groov.Audit\\\" OR "+
        "LOGGER == \\\"com.opto22.groov.server.security.Security\\\" "+
        "ORDER BY TIMESTAMP DESC LIMIT 50;\""};
// character 0 must be a space
// <\\\"> required to result in the character <\"> for the sqlite3 exec command

The first step after the exec node returns the massive string is to break up each column using a split node, which separates strings into isolated payloads at the \n character by default, then I can immediately build an array with a join node that I have set to manually combine each msg.payload so I get a msg.payload[#] object closer to the one I have in my groov Server for Windows log flow.

However each column is not a property of the payload objects, it’s still one long string, which calls for substring(start,end) using indexOf('|') to find the start and end points of each column. The rest of the code is essentially similar, but nonetheless here is the entire code body in the function node:

tag = 'event_list'; // groov data store tag name (type string table)
tablen = 10; // how many slots available in 'tag'
data = []; // array of objects that make up the table in groov
index = 0; // groov table index (not the same as payload index)
for(i = 0; i < msg.payload.length; i++) // i = payload array index
{
    if(msg.payload[i].length > 16) // check for terminating empty string ""
    {
        first = msg.payload[i].indexOf('|'); // first column is timestamp
        second = msg.payload[i].indexOf('|',first+1); // second column is message
        TIMESTAMP = parseInt(msg.payload[i].substring(0,first)); // grab time
        MESSAGE = msg.payload[i].substring(first+1,second); // grab message
        
        if(MESSAGE.includes("wrote") || MESSAGE.includes("logged")) // filter
        {
            var d = new Date(TIMESTAMP); // TIMESTAMP is in epoch
            formtime = d.toISOString().substring(0,10)+
                ' @ '+d.toISOString().substring(11,16); // format time: "yyyy-mm-dd @ hh:mm"
            data.push({ // new groov tag entry, this string goes at <index> in <tag>
                tagName         : tag,
                tableStartIndex : index,
                value           : (formtime + ", " + MESSAGE) // big string
            });
            index++; // next spot in the table...
        }
        if(index == tablen) break; // filled up the maximum table length, we're done!
    }
}
missing = tablen - data.length; // for the values expected but not returned,
for(i = 0; i < missing; i++)
{
    data.push({ // new groov tag entry -> dummy object
        tagName         : tag,
        tableStartIndex : index,
        value           : "insufficient data"
    });
    index++;
}
// return the data objects as the payload for split -> groov write
return { payload : data};

Once the timestamp and message are separated they are just as easily filtered and formatted as the above example, and the end result is of the exact same form:

So now I can filter and observe my groov log from my AR1 as well as my windows PC with no worries!

Happy coding!

[{“id”:“8dd8ff87.6aac5”,“type”:“exec”,“z”:“1c6984a1.1e25bb”,“command”:“sqlite3 /var/lib/jetty/optoapps/logs.db”,“addpay”:true,“append”:"",“useSpawn”:"",“timer”:"",“oldrc”:false,“name”:“execute sqlite3”,“x”:160,“y”:400,“wires”:[[“1299fb19.f9fe75”],[“ad6bec59.17f7c”],[“ad6bec59.17f7c”]]},{“id”:“49851262.49d5bc”,“type”:“inject”,“z”:“1c6984a1.1e25bb”,“name”:"",“topic”:"",“payload”:"",“payloadType”:“date”,“repeat”:“30”,“crontab”:"",“once”:true,“x”:150,“y”:300,“wires”:[[“e336454.38052b8”]]},{“id”:“e336454.38052b8”,“type”:“function”,“z”:“1c6984a1.1e25bb”,“name”:“select top 50 * from LOGS”,“func”:“return { payload : " \“SELECT * FROM LOGS WHERE LOGGER == \\\“groov.Audit\\\” OR “+\n “LOGGER == \\\“com.opto22.groov.server.security.Security\\\” “+\n “ORDER BY TIMESTAMP DESC LIMIT 50;\””};\n// character 0 must be a space\n// <\\\”> required to result in the character <\”> for the sqlite3 exec command”,“outputs”:1,“noerr”:0,“x”:190,“y”:340,“wires”:[[“8dd8ff87.6aac5”]]},{“id”:“a8f727bb.efd8a8”,“type”:“function”,“z”:“1c6984a1.1e25bb”,“name”:“format time, package event_list”,“func”:“tag = ‘event_list’; // groov data store tag name (type string table)\ntablen = 10; // how many slots available in ‘tag’\ndata = []; // array of objects that make up the table in groov\nindex = 0; // groov table index (not the same as payload index)\nfor(i = 0; i < msg.payload.length; i++) // i = payload array index\n{\n if(msg.payload[i].length > 16) // check for terminating empty string “”\n {\n first = msg.payload[i].indexOf(’|’); // first column is timestamp\n second = msg.payload[i].indexOf(’|’,first+1); // second column is message\n TIMESTAMP = parseInt(msg.payload[i].substring(0,first)); // grab time\n MESSAGE = msg.payload[i].substring(first+1,second); // grab message\n \n if(MESSAGE.includes(“wrote”) || MESSAGE.includes(“logged”)) // filter\n {\n var d = new Date(TIMESTAMP); // TIMESTAMP is in epoch\n formtime = d.toISOString().substring(0,10)+\n ’ @ '+d.toISOString().substring(11,16); // format time: “yyyy-mm-dd @ hh:mm”\n data.push({ // new groov tag entry, this string goes at in ‘event_list’\n tagName : tag,\n tableStartIndex : index,\n value : (formtime + “, " + MESSAGE) // big string\n });\n index++; // next spot in the table…\n }\n if(index == tablen) break; // filled up the maximum table length, we’re done!\n }\n}\nmissing = tablen - data.length; // for the values expected but not returned,\nfor(i = 0; i < missing; i++)\n{\n data.push({ // new groov tag entry -> dummy object\n tagName : tag,\n tableStartIndex : index,\n value : “insufficient data”\n });\n index++;\n}\n// return the data objects as the payload for split -> groov write\nreturn { payload : data};”,“outputs”:1,“noerr”:0,“x”:450,“y”:420,“wires”:[[“f50ed63e.0035f8”,“9c88ef8d.291e5”]]},{“id”:“f50ed63e.0035f8”,“type”:“split”,“z”:“1c6984a1.1e25bb”,“name”:””,“splt”:"\n",“spltType”:“str”,“arraySplt”:1,“arraySpltType”:“len”,“stream”:false,“addname”:"",“x”:530,“y”:460,“wires”:[[“c25ff86.1e9f708”]]},{“id”:“c25ff86.1e9f708”,“type”:“groov-write-ds”,“z”:“1c6984a1.1e25bb”,“dataStore”:“1b3ac020.01363”,“tagName”:"",“tableStartIndex”:"",“value”:“payload.value”,“valueType”:“msg”,“name”:"",“x”:550,“y”:500,“wires”:[[]]},{“id”:“ad6bec59.17f7c”,“type”:“debug”,“z”:“1c6984a1.1e25bb”,“name”:"",“active”:false,“console”:“false”,“complete”:“true”,“x”:470,“y”:340,“wires”:[]},{“id”:“1299fb19.f9fe75”,“type”:“split”,“z”:“1c6984a1.1e25bb”,“name”:"",“splt”:"\n",“spltType”:“str”,“arraySplt”:1,“arraySpltType”:“len”,“stream”:false,“addname”:"",“x”:410,“y”:380,“wires”:[[“e346d70.6785e28”]]},{“id”:“9c88ef8d.291e5”,“type”:“debug”,“z”:“1c6984a1.1e25bb”,“name”:"",“active”:false,“console”:“false”,“complete”:“true”,“x”:710,“y”:400,“wires”:[]},{“id”:“e346d70.6785e28”,“type”:“join”,“z”:“1c6984a1.1e25bb”,“name”:"",“mode”:“custom”,“build”:“array”,“property”:“payload”,“propertyType”:“msg”,“key”:“topic”,“joiner”:"\n",“joinerType”:“str”,“accumulate”:false,“timeout”:"",“count”:"",“x”:530,“y”:380,“wires”:[[“a8f727bb.efd8a8”,“9c88ef8d.291e5”]]},{“id”:“1b3ac020.01363”,“type”:“groov-data-store”,“z”:"",“project”:“cb2b7005.0a753”,“dsName”:“NodeRED”},{“id”:“cb2b7005.0a753”,“type”:“groov-project”,“z”:"",“address”:“localhost”}]