SLMEventQueries
NOTE: When writing an SLM report that queries for events, in the iReport Report Query dialog the "Read Fields" functionality does not work due to an error in iReport related to the classpath/classloader. Therefore, iReport is not able to automatically determine the fields from the lucene query you type in. To workaround this issue, you can add and edit the fields using the "Document Structure" docking pane by opening and right clicking on the "Fields" folder.
Writing a Report that Queries for Events In Sentinel Log Manager
This section will describe what needs to be done in order to create a report that queries for events. It is assumed you will be using the Log Manager Event report template to develop the report.
Required Parameters
The event report template already comes with some common parameters defined. These parameters are described below so that you know what they are used for:
- INITIATING_USER (required): The INITIATING_USER parameter is automatically set to the user that ran the report to generate the report result. This parameter is required for the Lucene query to work properly. All that needs to be done is define the parameter, as follows:
<parameter name="INITIATING_USER" isForPrompting="false" class="java.lang.String"/>
- VendorProduct (optional): This is a string that indicates which Vendor Product events this report is querying for. The value of this parameter is used both in the query and in other placing in the report, such as in the report title. The parameter is declared as follows in the .jrxml file. This parameter is automatically set to the default value that is set in the package.xml of the report definition plugin. It is possible that the package.xml does not specify a Vendor Product, for example in reports that apply to all vendors and all products. For this reason, there is an additional parameter declared that is the one that is actually used to in the search query and report title and the value of this parameter defaults to an appropriate value if the Vendor Product is not set:
<parameter name="VendorProduct_Query" isForPrompting="false" class="java.lang.String">
<defaultValueExpression><![CDATA[$P{VendorProduct}.equals("All Vendors All Products")? "" :("AND agent:\"" + $P{VendorProduct} + "\"")]]></defaultValueExpression>
</parameter>
The VendorProduct_Query parameter is then used in the Lucene search query as follows:
<queryString language="LUCENE_EVENT"><![CDATA[SELECT
dt AS event_time,
sev AS severity
WHERE
sev:[$P{MinSevStr} TO $P{MaxSevStr}] $P{VendorProduct_Query} $P{SEARCH_QUERY_USE}
OVER $P{FromDtQry}, $P{ToDtQry}
$P{MaximumResultsStr}
]]>
</queryString>
By including this parameter in the search query, the events included in the report will be restricted to only events with the 'agent' field equal to the specified VendorProduct when it's not 'All Vendor All Product'.
- MaxResults (optional): It takes a longer amount of time to generate a report result with more events. Therefore, to make it more efficient to generate report results, we allow the user to specify the maximum number of events to retrieve in the Lucene search query. This parameter is used for this purpose and is declared and used in the .jrxml file as follows:
<parameter name="MaxResults" isForPrompting="false" class="java.lang.Integer">
</parameter>
<parameter name="MaximumResultsStr" isForPrompting="false" class="java.lang.String">
<defaultValueExpression><![CDATA[ ($P{MaxResults} == null ? "" : ( "MAXCOUNT=" + ($P{MaxResults}.toString())))]]></defaultValueExpression>
</parameter>
The MaximumResultsStr parameter is then used in the Lucene search query as follows:
<queryString language="LUCENE_EVENT"><![CDATA[SELECT
dt AS event_time,
sev AS severity
WHERE
sev:[$P{MinSevStr} TO $P{MaxSevStr}] $P{VendorProduct_Query} $P{SEARCH_QUERY_USE}
OVER $P{FromDtQry}, $P{ToDtQry}
$P{MaximumResultsStr}
]]>
</queryString>
- SEARCH_QUERY (optional): It is possible to send the search result of a query that the user typed on the Web GUI search page into an existing report, to combine the user's search query with the pre-defined search criteria in the report jrxml file. This kind of report we call a _template_ report because it is a template that the user can use to plug in their own search query. Template reports are used by the user by clicking on the _save as report_ link from within the search tab. After the user clicks on the link, they'll see a dialog where they can select the template report they wish to use. There are three things that need to be done in order to get a _template_ report to work properly.
They are:
- Set the !IsQueryParameterized element in the report plugin's package.xml to 1, as follows:
<IsQueryParameterized>1</IsQueryParameterized>
- At least at this time.... The name of the report must also start with “All-Vendors-All-Products”.
- Define the SEARCH_QUERY parameter, which is done by adding the following to the .jrxml file. The query defined in the report can then use this parameter as follows:
<queryString language="LUCENE_EVENT"><![CDATA[SELECT
dt AS event_time,
sev AS severity
WHERE
sev:[$P{MinSevStr} TO $P{MaxSevStr}] $P{VendorProduct_Query} $P{SEARCH_QUERY_USE}
OVER $P{FromDtQry}, $P{ToDtQry}
$P{MaximumResultsStr}
]]>
</queryString>
Report Query
In order to notify Jasper that it needs to use the special Lucene based query engine to run a report to query for events, the report queryString language must be set to LUCENE_EVENT. Here is what that looks like in the .jrxml file.
The general format of the LUCENE_EVENT report query language is:
SELECT csv list of event field tuples WHERE lucene style search query [OVER start time, end time] [MAXCOUNT=max # events]
Where:
- The _csv list of event fields_ is a comma separated list of event fields that should be returned by the query. The event fields listed here must be the tag name (short name) of the event field. Refer to the _search tips_ link in the web interface to see the list of event fields that can be searched on and what their tag name is. This list can also contain the AS operator (also present in SQL) for each event field, which indicates what the alias of the field should be when presented to Jasper.
- The _lucene style search query_ is a query exactly as described in the Lucene query language specification (http://lucene.apache.org/java/2_4_1/queryparsersyntax.html). This is the same query language used in the search user interface. A good way to build and test your query is to use the search feature in the web interface, then copy/paste the query into your report.
- The OVER operator specifies the time range to search over. It is optional.
- If not present, the time range is "All Time".
- If present, the time range is the time range specified.
- The date format is "yyyy-MM-dd kk:mm:ss" (in Java SimpleDateFormat format).
- The _max # events_ is maximum number of events that will be returned by the query.
Important Notes:
- The lucene query language returns the date fields as the type _java.util.Date_, for example:
<field name="event_parse_time" class="java.util.Date"/>
Below is a sample of the relevant portion of a jrxml file using all the information described above:
<parameter name="VendorProduct" isForPrompting="true" class="java.lang.String">
<defaultValueExpression ><![CDATA["%"]]></defaultValueExpression>
</parameter>
<parameter name="ReportType" isForPrompting="true" class="java.lang.String">
<parameterDescription><![CDATA[D - Current Day, PD - Previous Day, W - Week To Date, PW - Previous Week, M - Month To Date, PM - Previous Month, DR - Custom Date Range]]>
</parameterDescription>
<defaultValueExpression ><![CDATA[new String()]]></defaultValueExpression>
</parameter>
<parameter name="FromDate" isForPrompting="true" class="java.util.Date">
<defaultValueExpression ><![CDATA[new Date()]]></defaultValueExpression>
</parameter>
<parameter name="ToDate" isForPrompting="true" class="java.util.Date">
<defaultValueExpression ><![CDATA[new Date()]]></defaultValueExpression>
</parameter>
<parameter name="FromDtQry" isForPrompting="false" class="java.lang.String">
<defaultValueExpression ><![CDATA[$P{REPORT_SCRIPTLET}.DatePrepare($P{ReportType},"F",((new SimpleDateFormat("dd/MM/yyyy HH:mm:ss")).format($P{FromDate})),"")]]>
</defaultValueExpression>
</parameter>
<parameter name="ToDtQry" isForPrompting="false" class="java.lang.String">
<defaultValueExpression ><![CDATA[$P{REPORT_SCRIPTLET}.DatePrepare($P{ReportType},"T",((new SimpleDateFormat("dd/MM/yyyy HH:mm:ss")).format($P{ToDate})),"")]]>
</defaultValueExpression>
</parameter>
<parameter name="MaxResults" isForPrompting="false" class="java.lang.Integer">
<!-- Do not set a default value here -->
</parameter>
<parameter name="MaximumResultsStr" isForPrompting="false" class="java.lang.String">
<defaultValueExpression ><![CDATA[ ($P{MaxResults} == null ? "" : ( "MAXCOUNT=" + ($P{MaxResults}.toString())))]]></defaultValueExpression>
</parameter>
<parameter name="PLUGIN_DIRECTORY" isForPrompting="false" class="java.lang.String">
<parameterDescription><![CDATA[Location of plugin files]]></parameterDescription>
</parameter>
<parameter name="INITIATING_USER" isForPrompting="false" class="java.lang.String"/>
<parameter name="SEARCH_QUERY" isForPrompting="false" class="java.lang.String">
<defaultValueExpression ><![CDATA[""]]></defaultValueExpression>
</parameter>
<parameter name="SEARCH_QUERY_USE" isForPrompting="false" class="java.lang.String">
<defaultValueExpression ><![CDATA[($P{SEARCH_QUERY} == null || $P{SEARCH_QUERY}.trim().length() == 0) ? "" : " AND " + $P{SEARCH_QUERY}]]></defaultValueExpression>
</parameter>
<parameter name="VendorProduct_Query" isForPrompting="false" class="java.lang.String">
<defaultValueExpression ><![CDATA[$P{VendorProduct}.equals("All Vendors All Products")? "" : ("AND agent:\"" + $P{VendorProduct} + "\"")]]></defaultValueExpression>
</parameter>
<queryString language="LUCENE_EVENT"><![CDATA[SELECT
dt AS event_parse_time,
evt AS event_name,
dun AS targetusername,
sun AS initusername,
rv45 AS target_user_domain,
dhn AS target_host_name,
dip AS target_ip,
fn AS file_name,
ei AS extended_info,
xdasoutcome AS xdasoutcome,
agent AS agent
WHERE
xdasoutcome:[0 TO 2] AND xdasclass:0 AND xdasreg:0 AND xdasprov:0 AND xdasid:7 $P{VendorProduct_Query} $P{SEARCH_QUERY_USE}
OVER $P{FromDtQry}, $P{ToDtQry}
$P{MaximumResultsStr} ]]></queryString>
<field name="event_parse_time" class="java.util.Date"/> <field name="event_name" class="java.lang.String"/> <field name="targetusername" class="java.lang.String"/> <field name="initusername" class="java.lang.String"/> <field name="target_user_domain" class="java.lang.String"/> <field name="target_host_name" class="java.lang.String"/> <field name="target_ip" class="java.lang.String"/> <field name="file_name" class="java.lang.String"/> <field name="extended_info" class="java.lang.String"/> <field name="xdasoutcome" class="java.lang.Integer"/>
Note that we used the Event attribute short name in the SELECT part, and that this query does not have a 'FROM' section because the Lucene query is only used for searching Event data from the file system. The 'OVER $P{FromDtQry}, $P{ToDtQry}' clause is used to specify the time range of the events to search for.
Extensions to the Lucene Query Language for Reporting
Sentinel Log Manager contains extensions to the Lucene query language to facilitate report writing. These extensions are only available in reports and are not available anywhere else where the Lucene query language is used (e.g., the extensions are not available in Search, Rules, Data Retention, etc.) . Below is a list of the available extensions
- Date Rounding
- Function: TruncatedDate
- Purpose: This function is used to round date values to whole hours or days. This may be desirable when creating a chart or graph and you would like to group dates that happened on the same day, for example. By rounding the date value, you can then group records by checking for equivalency on the rounded date value.
- Function Definition: TruncatedDate(<event field tag> | {DAY | HOUR}) This function is similar to the SQL function Date_Trunc() . It takes two parameters:
- The first parameter 'dt' is a date object, you can use any Event tag of date type as a parameter.
- The second parameter is a String, you can use DAY or HOUR ( note: do not need to quote the string.)
- This function returns a object that truncates the input date object to the precision of Day or Hour.
- Usage: An example of using this function is as follows:
<queryString language="LUCENE_EVENT"><![CDATA[SELECT
dt AS event_parse_time,
xdasoutcome AS xdasoutcome,
TruncatedDate(dt| DAY) AS eventtime_day,
TruncatedDate(dt| HOUR) AS eventtime_hour
WHERE
xdasreg:0 AND xdasprov:0 AND xdasclass:1 AND xdasid:7 AND xdasoutcome:[0 TO 2] $P{VendorProduct_Query} $P{SEARCH_QUERY_USE}
OVER $P{FromDate}, $P{ToDate}
$P{MaximumResultsStr} ]]></queryString>
<field name="event_parse_time" class="java.sql.Timestamp"/>
<field name="xdasoutcome" class="java.lang.Integer"/>
<field name="eventtime_day" class="java.util.Date"/>
Converting SQL queries to Lucene queries
This section describes how to covert a SQL query that you may have in an existing report into a Lucene query so that you can create a report that runs on Log Manager. One thing to note immediately is that the SQL language has many more capabilities than the Lucene query. This is partially due to the fact that some SQL functions are very database specific, while Lucene does not use a database. Another reason is because SQL has been around longer than Lucene and, therefore, more capabilities have been built into it over time.
Converting the WHERE criteria
Converting SQL functions
When converting from a SQL query that uses SQL functions into a Lucene query, when there are two different approaches that can be taken. Use the one that fits your situation the best.
Use snippet of Java code in the report to get same result as the SQL built-in function (easier)
This approach involves using snippets of java code in Jasper Variable definitions in order to take Jasper Field values as input and set the Variable to the output of the snippet of java code.
An example of how this can is used is to replace the use of the CASE statement in a SQL query. For example, say we have the following SQL query and Jasper Field definition:
SELECT (CASE WHEN EVT_AGENT_RPT_V.DEVICE_CATEGORY = 'OS' THEN 'Operating System' WHEN EVT_AGENT_RPT_V.DEVICE_CATEGORY = 'DB' THEN 'Database' ELSE 'Network Devices' END) AS DEV_CAT ...
<field name="dev_cat" class="java.lang.String"/>
The SQL above checks the EVT_AGENT_RPT_V.DEVICE_CATEGORY value and, depending on its value, sets DEV_CAT accordingly. It is not possible to implement such a query directly in the Lucene query language. Instead, to implement this so that it works with a Lucene query, first retrieve the original value of the Event device category, store it in a Field, then define a Variable which contains a snippet of java code that checks the Field value and sets the value of the Variable accordingly. Here is the code example:
SELECT rv32 as DEVICE_CATEGORY ......
<field name="DEVICE_CATEGORY" class="java.lang.String" />
<variable name="DEV_CAT" class="java.lang.String" resetType="Report" calculation="Nothing">
<variableExpression><![CDATA[$F{DEVICE_CATEGORY} == "OS" ? "Operating System" : ( $F{DEVICE_CATEGORY} == "DB" ? "Database" : "Network Devices")]]></variableExpression>
</variable>
As a result of this, the report will now have a Variable ($V{DEV_CAT}) that is equivalent to what the Field ($F{DEV_CAT}) would have been set to in the SQL report. Note that since a Variable is being used here instead of a Field, the way to get the value changes from using $F{} to $V{}.
Writing "Top N" Type Reports
In order to provide a simple means for developing efficient "Top N"-type reports, the SLM Jasper Reporting language has been expanded as follows:
A new GROUP BY element has been added to the SELECT statement of the LUCENE_EVENT query language, for example:
<queryString language="LUCENE_EVENT"> <![CDATA[SELECT
evt AS event_name
WHERE
sev:[$P{MinSevStr} TO $P{MaxSevStr}] $P{VendorProduct_Query} $P{SEARCH_QUERY_USE}
GROUP BY evt TOP 10
OVER $P{FromDtQry}, $P{ToDtQry}
$P{MaximumResultsStr}
]]></queryString>
<field name="event_name" class="java.lang.String"/> <field name="count" class="java.lang.Integer"/> <field name="row_num" class="java.lang.Integer"/>
When a "GROUP BY" value is specified, the data source will return the top N most frequent values of the desired event field(s) along with the count of each of those values.
Notice that the event field(s) specified in the "GROUP BY" element are also specified in the SELECT "AS" portion as well (just like other LUCENE_EVENT type reports). This is still done in order to map the event field to the corresponding JRField in the report.
In this example, the Top 10 "evt" (Event Name) values will be returned via the "event_name" JRField and the number of occurrences of each field value will be returned via the pre-defined "count" JRField. The pre-defined "row_num" JRField is provided as a convenience for reports that want to change visualization of the results based on the row number.
The returned data list for the above query might look like:
row_num event_name count 1 Test Event 1022 2 Correlated Event 60 3 Failed Login 12 4 Login 5 5 Collector Message 3
Note as in this example, that less than N values will be returned if there are less than N different values for the field.
You may specify any integer value in the "TOP N" portion of the GROUP BY statement.
If no "TOP N" value is specified, then ALL values for the given event field will be returned... most frequent first.
You may specify multiple event fields in the "GROUP BY" element, each separated by a comma, in order to further group the results of the previous GROUP BY element. For example:
<queryString language="LUCENE_EVENT"> <![CDATA[SELECT
evt AS event_name,
sev AS severity
WHERE
sev:[$P{MinSevStr} TO $P{MaxSevStr}] $P{VendorProduct_Query} $P{SEARCH_QUERY_USE}
GROUP BY evt TOP 10,sev TOP 6
OVER $P{FromDtQry}, $P{ToDtQry}
$P{MaximumResultsStr}
]]></queryString>
<field name="event_name" class="java.lang.String"/> <field name="severity" class="java.lang.String"/> <field name="count" class="java.lang.Integer"/> <field name="row_num" class="java.lang.Integer"/>
In this example, the Top 10 "Event Name" values will be returned - each grouped by the Top 6 severity values of the associated events. This is equivalent to doing a query to get the top 10 Event Name values... and then for each of those returned Top 10 Event Name values, doing a query to get the Top 6 severity values.
The returned data list for the above query might look like:
row_num event_name severity count 1 Test Event 4 1000 2 Test Event 5 10 3 Test Event 3 9 4 Test Event 2 2 5 Test Event 1 1 6 Correlated Event 4 60 7 Failed Login 3 9 8 Failed Login 5 3 9 Login 0 5 10 Collector Message 5 2 11 Collector Message 1 1
TIP: If you put all of the queries in subreports and don't have any query in your main report, you may not be able to run the report through "iReport". In order to get around this, just add the following to your main report .jrxml file if you don't have any other queries that file:
<queryString language="LUCENE_EVENT"/>
EventTag Parameter
As of SLM 1.0.0.3, a new EventTag type parameter has been added to the SLM codebase. This new "EventTag" parameter provides and easy way to give users a drop-down selection parameter that automatically lists all supported Event Field Display Names. The corresponding event "tag" of the selected event field will be passed to the report. The "display name" of the event field will also be passed to the report as a parameter with the same name as the parameter with the suffix "_DISPLAY".
Example:
<PluginParameterDefinition> <Type>EventTag</Type> <DisplayName>My Event Field</DisplayName> <InternalName>MyEventField</InternalName> <Description>Select an event field.</Description> <DefaultValue>evt</DefaultValue> <IsForPrompting>1</IsForPrompting> </PluginParameterDefinition>
In this case, the "tag" of the selected event field will be passed to the report as the "MyEventField" parameter and the "display name" of the selected event field will be passed to the report as the "MyEventField_DISPLAY" parameter.