Automated CSV creation of Custom Reports

Reporting
1

(imported topic written by JasonO91)

Is it possible to create a csv for a custom report? I have reviewed the previous post:

http://forum.bigfix.com/viewtopic.php?id=6

I have a custom report which returns data in a simple table, which should be very easy to convert to csv.

Thanks,

Jason

2

(imported comment written by JasonO91)

Would it be possible to generate a rope with concatenated strings to generate a csv file? I don’t think it necessarily needs to be a rope, but some queries that get built could contain very long strings.

What I’m trying to do is create a comma separated file, and I’ll setup a custom report scheduled to run every day. The archive option will be set, and I should be able to perform any changes or save it to another location after the query has been run.

It would look something like this:

<?relevance trs of (td of (concatenation "," of ( name of item 1 of it; name of item 0 of it))) of (relevant fixlets whose (exists source severity of it AND (source severity of it as lowercase = "critical" OR source severity of it as lowercase = "important")) of it, it) of bes computers whose (exists result (bes property "computer name", it)) ?>
Computer Name,Fixlet Name,Source,Source ID,Source Severity,Source Release Date,Category

Specifically, my questions are:

  1. Is it possible to construct a custom report to only generate text (not table rows or html)

  2. When is the best time to rope the strings together? For each line?

ps.

I’ve been a user of many forums, and I’ve not seen the level of expertise, timely replies, and overall usability very often. I just wanted to say thanks for all the hard work that you put in to keep this forum so helpful.

Jason

3

(imported comment written by jessewk)

Jason,

As it turns out, I’ve been working on the same problem off and on for that last week or so. I have a solution that retrieves the data using relevance and loads it into a javascript array. There’s actually an inspector for this. Here’s an example you could embed into a javascript function:

eval(EvaluateRelevance(‘javascript array “Results” of values of results of bes property whose (name of it = “IP Address”)’));

At this point you’d have a javascript array ‘Results’ that contains all the results of the IP Address property. Note that I’m skipping some error checking for clarity.

Subsequent to this, you can use write a function that comma separates the results and writes them to a hidden IFRAME. You then call the Internet Explorer command ‘execCommand’ on the IFRAME and pass the ‘SaveAs’ parameter. Here is an example that iterates through an array where each element of the array is another array representing the results for a single row:

function SaveToCSV()
{

var d = CSVFrame.document;
var text = ‘’;

d.innerHTML = ‘’;

for (var i = 0; i < Results.length; i++)
{
var numresults = Results+.length;
for (var j = 0; j < numresults; j++)
{
text += CSVEncode(Results+[j]);
text += ', ';
}
text += ‘\r\n’;
}
d.write(text);
d.execCommand(‘SaveAs’,‘false’,‘results.txt’);

}

You need a couple of helper functions.

This one percent encodes commas and quotes:

function CSVEncode(txt)
{
txt = txt.replace(/,/g, ‘%2C’);
txt = txt.replace(/"/g, ‘%22’);
return ‘"’ + txt + ‘"’;
}

This one will locate the ‘Export to CSV’ link, enable it, and set it to call the SaveToCSV() function above when clicked:

function EnableExportToCSVLink()
{
var possNodes = GetElementsByClassname(document.body, ‘B’, ‘wr_disabledmenu’);
var CSVLinkNode = document.createElement(‘A’);

for (var i = 0; i < possNodes.length; i++)
{
if (possNodes+.innerText.match(‘Export to CSV’))
{

CSVLinkNode.innerText = ‘Export to CSV’;
CSVLinkNode.className = ‘wr_menu’;
CSVLinkNode.href = ‘javascript:SaveToCSV()’;
possNodes+.parentNode.replaceChild(CSVLinkNode, possNodes+);
return;
}
}
}

This one will return all subnodes of StartNode with TagName = NodeType and classname = nodeClass. Called by ‘EnableExportToCSVLink()’.

function GetElementsByClassname(startNode, nodeType, nodeClass)
{
var possNodes = startNode.getElementsByTagName(nodeType);
var numPoss = possNodes.length;
var nodes = new Array();

for (var i = 0; i < numPoss; i++)
{
var classNames = possNodes+.className.split(’ ');

for (var j = 0; j< classNames.length; j++)
{
if (classNames[j] == nodeClass)
{
nodes.push(possNodes+);
}
}

}
return nodes;
}

This scheme only has one problem I have been unable to solve, and I’m not sure it’s possible (although I’d love somebody to prove me wrong!). If you set the file name in the execCommand call with a .txt extension, it will be ASCII encoded which is necessary to import a CSV file into excel. If you set the file name to .csv, it will be saved as unicode, which won’t import correctly. So either way that leaves a post export step. Either you have to save it as .txt and rename it to .csv, or you have to name it csv and then open it in a text editor and save it as ASCII.

In the end it works though and isn’t too much trouble. :wink:

4

(imported comment written by JasonO91)

Jesse,

Where are you putting the javascript? I’ve tried to load it locally, and it fails. Obviously posting the code into the XML field of the custom report doesn’t work.

I’m quite sure it’s something simple, however I don’t know where it processes the javascript.

Jason

5

(imported comment written by jessewk)

Jason,

Actually it does go in the ‘XML’ field. I’d say that field is misnamed…

You’ll need to enclose any code you want to be evaluated as javascript within

6

(imported comment written by BenKus)

For this type of thing, you might consider using the tool here:

http://forum.bigfix.com/viewtopic.php?id=3564

Ben