-
Notifications
You must be signed in to change notification settings - Fork 3
Home
This tool allows a user to execute multiple searches, and compile the data in a single report. Currently, the only format is Excel (.xlsx). Each tab in Excel would correspond to a search executed in Sumo Logic.
To use the tool, simply run from your command line:
$java -jar sumo-report-generator.jar
usage: SumoLogic Report Generator
-c,--config <arg> Path to the report JSON configuration file.
-d,--debug Enable debug logging.
-h,--help Prints help information.
-t,--trace Enable trace logging.
-v,--version Get the version of the utility.
To generate a report, simply configure JSON configuration file that defines the report. Here is a basic example:
{
"accessId" : "<accessId>",
"accessKey" : "<accessKey>",
"url" : "https://api.sumologic.com/api/v1",
"destinationFile" : "/some/path/workbook.xlsx",
"reportSheets" : [
{
"sheetName" : "errors",
"searchJob" : {
"query": "error | count",
"from": "2015-09-20T00:00:00",
"to": "2015-09-20T23:59:59",
"timezone": "PST"
}
}
]
}You must have a Sumo Logic accessId/accessKey to use this tool. The URL you choose should be the URL based on your deployment. You can define any number of report sheets, each reportSheet represents a sheet in the final Excel Workbook. The sheetName will be the name of the sheet. Note that standard limitations of naming sheets in Excel apply. The searchJob is the Sumo search you want to execute and populate in the sheet. Make sure you properly escape your query as you would with any SumoLogic API call.
If you have a template JSON file that you wish to reuse that contains 10 queries. Updating certain things for every query can be tedious. Enter propertyReplacements. This feature allows you to define a set of key/value pairs. You can then reference the key in any SearchJob you define. The sumo-report-generator will replace that key with the value before executing the Search Job. This means if you have a template with 10 queries, instead of replacing the from/to date (for example) 20 times, you just do it 2x. Here is the same example above, using property replacements for the from/to date.
{
"accessId" : "<accessId>",
"accessKey" : "<accessKey>",
"url" : "https://api.sumologic.com/api/v1",
"destinationFile" : "/some/path/workbook.xlsx",
"propertyReplacementConfig" : {
"propertyReplacements" : [
{
"replacementKey" : "from_date",
"replacementValue" : "2015-09-20T00:00:00"
},
{
"replacementKey" : "to_date",
"replacementValue" : "2015-09-20T23:59:59"
}
]
},
"reportSheets" : [
{
"sheetName" : "errors",
"searchJob" : {
"query": "error | count",
"from": "${from_date}",
"to": "${to_date}",
"timezone": "PST"
}
}
]
}The sumo-report-generator does not do any formatting at the moment. However, there may be situations where you run the same set of SearchJobs and possibly add columns with formulas, and change the spacing of columns, or add conditional formatting. The sumo-report-generator supports using pre defined workbook templates by simply adding the path to the template into your JSON configuration.
{
"accessId" : "<accessId>",
"accessKey" : "<accessKey>",
"url" : "https://api.sumologic.com/api/v1",
"destinationFile" : "/some/path/workbook.xlsx",
"templateFile" : "/some/path/workbook_template.xlsx",
"propertyReplacementConfig" : {
"propertyReplacements" : [
{
"replacementKey" : "from_date",
"replacementValue" : "2015-09-20T00:00:00"
},
{
"replacementKey" : "to_date",
"replacementValue" : "2015-09-20T23:59:59"
}
]
},
"reportSheets" : [
{
"sheetName" : "errors",
"searchJob" : {
"query": "error | count",
"from": "${from_date}",
"to": "${to_date}",
"timezone": "PST"
}
}
]
}The template can add columns, formulas, conditional formatting or change the column/row width and it will be respected when the workbook is generated. This can save you time by eliminating your need to format the excel workbook. Note that added columns are not touched, so formulas will not be replicated down to the number of rows contained in a sheet. In order to ensure the data from your search is correctly populated, you must ensure the column names in your template correspond to the column names in your searches. If you forget a column in the template and it exists in your search, it will be omitted.
If you want to use multiple JSON files (perhaps you have multiple deployments/accounts you want and you want to run the same search across all of them), you can use the appendToDestination property in the JSON config to accomplish this. Let's say you have 2 different accounts and you want to run the same search across and append the results into 1 file. The below examples uses a template, but it could be omitted. Here is the first JSON.
{
"accessId" : "<accessId1>",
"accessKey" : "<accessKey1>",
"url" : "https://api.sumologic.com/api/v1",
"destinationFile" : "/some/path/workbook.xlsx",
"templateFile" : "/some/path/workbook_template.xlsx",
"propertyReplacementConfig" : {
"propertyReplacements" : [
{
"replacementKey" : "from_date",
"replacementValue" : "2015-09-20T00:00:00"
},
{
"replacementKey" : "to_date",
"replacementValue" : "2015-09-20T23:59:59"
}
]
},
"reportSheets" : [
{
"sheetName" : "errors",
"searchJob" : {
"query": "error | count",
"from": "${from_date}",
"to": "${to_date}",
"timezone": "PST"
}
}
]
}Here is the second JSON config. Notice the appendToDestination property is set to true on this one. We also removed the templatePath property because we want to use the file created by the first JSON config.
{
"accessId" : "<accessId2>",
"accessKey" : "<accessKey2>",
"url" : "https://api.sumologic.com/api/v1",
"destinationFile" : "/some/path/workbook.xlsx",
"appendToDestination" : "true",
"propertyReplacementConfig" : {
"propertyReplacements" : [
{
"replacementKey" : "from_date",
"replacementValue" : "2015-09-20T00:00:00"
},
{
"replacementKey" : "to_date",
"replacementValue" : "2015-09-20T23:59:59"
}
]
},
"reportSheets" : [
{
"sheetName" : "errors",
"searchJob" : {
"query": "error | count",
"from": "${from_date}",
"to": "${to_date}",
"timezone": "PST"
}
}
]
}Now when you execute the report generator using the first JSON config, the tool will create a new workbook from the specified template and populate it with the results of the search. The second JSON config will use the same destinationFile generated by the first JSON config, and append the results. Now you can capture the same data from multiple accounts/deployments in a single report.
Why does my date show in milliseconds format?
You likely did a timeslice and forgot to use a formatDate() operator to properly format the timeslice. If you do this, then the date will be output to Excel in the correct format. For example:
formatDate(fromMillis(_timeslice),\"MM/dd/yyyy\") as date
Why do my numeric cells show as string?
This likely means that Sumo Logic is returning the data as String and not a numeric value. Try casting your value back to a number. As long as the field returned from Sumo is a numeric value, then your excel formatting will show correctly. For example, maybe you used the format() operator to round a numeric value. The format operator returns a String. So you have to cast it back to a number.
format(\"%.2f\", size_mb/1024) as size_gb | number(size_gb)
- Requires Java 1.8!
- Only .xlsx format is supported currently
- The results are simply output to excel manual formatting may be required if you do not use a template.
- Any search you execute is executed against the SumoLogic Search Job API, therefore, any limitations of the API apply to this tool.