Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Anonymous_User Absent Member.
Absent Member.
312 views

Lucene Bottom N type report


I have a requirement to write a report which shows the least occurances
of some events. I have found example of Top N type reports, but is it
possible to reverse the order to get Bottom N report in lucene?

For Top N we have following:
GROUP BY $P{EventTag} TOP $P{TopN}, sev TOP 6

Changing TOP to BOTTOM in this query doesn't work. Also, I can change
TOP to WHATEVER and it still does not complain about syntax.

Could you help?
--
Adam


--
admroz
------------------------------------------------------------------------
admroz's Profile: https://forums.netiq.com/member.php?userid=3440
View this thread: https://forums.netiq.com/showthread.php?t=46307

0 Likes
3 Replies
Anonymous_User Absent Member.
Absent Member.

Re: Lucene Bottom N type report

Hi,

>>> On 03.12.2012 at 10:44, admroz<admroz@no-mx.forums.netiq.com> wrote:


> I have a requirement to write a report which shows the least occurances
> of some events. I have found example of Top N type reports, but is it
> possible to reverse the order to get Bottom N report in lucene?


Try doing a GROUP BY without the TOP. That should give you all values.
Then do an ascending sort on the "count" column and display only the first N
rows.

See also http://www.novell.com/developer/slm_event_queries.html

Norbert

>
> For Top N we have following:
> GROUP BY $P{EventTag} TOP $P{TopN}, sev TOP 6
>
> Changing TOP to BOTTOM in this query doesn't work. Also, I can change
> TOP to WHATEVER and it still does not complain about syntax.
>
> Could you help?


0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Lucene Bottom N type report


Norbert,

Thanks for your reply. We are trying to go a way you described, but we
still have some problems - let me explain.

The Top 10 Report (which is our base for Bottom 10 Report) uses
following lucene query:
SELECT
$P{EventTag} AS event_tag_value,
sev AS severity
WHERE
sev:[$P{MinSevStr} TO $P{MaxSevStr}] AND notnull:$P{EventTag}
$P{VendorProduct_Query} $P{SEARCH_QUERY_USE}
GROUP BY $P{EventTag} TOP $P{TopN},sev TOP 6
OVER $P{FromDtQry}, $P{ToDtQry}
$P{MaximumResultsStr}

and there is also:
<field name="count" class="java.lang.Integer"/>

Now, I don't know how the magic "TOP" clause is implemented, but I
assume that it does its job. Important thing is that there is grouping
on both on EventTag (eg. sip) and severity. Which means that "count"
columns contain amout of occurances of sip/sev pairs rather than sum of
sip occurances with any sev.

So the first question is - is the Top 10 Report working correctly? Does
it take biggest values in regards of sums for sip with any sev?

As you advised, to have Bottom 10 we've removed TOP clause for EventTag
from lucene query. We also have to add sorting on count:
<sortField name="count" order="Ascending"/>

and filter to limit results on a chart:
<filterExpression><![CDATA[new Boolean($V{SumOfCNT}.intValue() <=
Integer.parseInt($P{TopN}))]]></filterExpression>

With an assumption that $P{TopN} is 10 then it should show 5 bars on a
chart. However, let's consider following data:

sip | sev | count
10.0.0.1 | 1 | 1
10.0.0.1 | 2 | 1
10.0.0.1 | 3 | 1
10.0.0.2 | 1 | 2
10.0.0.3 | 1 | 2
10.0.0.4 | 1 | 2
10.0.0.5 | 1 | 2
10.0.0.6 | 1 | 2
10.0.0.7 | 1 | 2

Because data is ordered by "count" then it takes three times 10.0.0.1
and puts it on a first bar with a value of 3 (which is a sum for each
severity). It also increases counter by 3, so it shows only two more
bars on a chart for 10.0.0.2 and 10.0.0.3. Of cours the value for
10.0.0.1 should not appear at all, because its sum is bigger than values
for other IPs.

The only solution which we have for now is removing "sev" from group by
clause, but that way we are loosing some functionality of original Top
10 Report.

Is it possible to solve it in any other way? Even if it was normal sql
query I would doubt if it was possible to be done with one query only.

Thanks
Adam


--
admroz
------------------------------------------------------------------------
admroz's Profile: https://forums.netiq.com/member.php?userid=3440
View this thread: https://forums.netiq.com/showthread.php?t=46307

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Lucene Bottom N type report

Hi Adam,
you could try to use a PrintWhen expression based on the build-in
REPORT_COUNT variable:
http://jasperreports.sourceforge.net/api/net/sf/jasperreports/engine/JRVaria
ble.html#REPORT_COUNT

Norbert

>>> On 12.12.2012 at 14:14, admroz<admroz@no-mx.forums.netiq.com> wrote:


> Norbert,
>
> Thanks for your reply. We are trying to go a way you described, but we
> still have some problems ‑ let me explain.
>
> The Top 10 Report (which is our base for Bottom 10 Report) uses
> following lucene query:
> SELECT
> $P{EventTag} AS event_tag_value,
> sev AS severity
> WHERE
> sev:[$P{MinSevStr} TO $P{MaxSevStr}] AND notnull:$P{EventTag}
> $P{VendorProduct_Query} $P{SEARCH_QUERY_USE}
> GROUP BY $P{EventTag} TOP $P{TopN},sev TOP 6
> OVER $P{FromDtQry}, $P{ToDtQry}
> $P{MaximumResultsStr}
>
> and there is also:
> <field name="count" class="java.lang.Integer"/>
>
> Now, I don't know how the magic "TOP" clause is implemented, but I
> assume that it does its job. Important thing is that there is grouping
> on both on EventTag (eg. sip) and severity. Which means that "count"
> columns contain amout of occurances of sip/sev pairs rather than sum of
> sip occurances with any sev.
>
> So the first question is ‑ is the Top 10 Report working correctly? Does
> it take biggest values in regards of sums for sip with any sev?
>
> As you advised, to have Bottom 10 we've removed TOP clause for EventTag
> from lucene query. We also have to add sorting on count:
> <sortField name="count" order="Ascending"/>
>
> and filter to limit results on a chart:
> <filterExpression><![CDATA[new Boolean($V{SumOfCNT}.intValue() <=
> Integer.parseInt($P{TopN}))]]></filterExpression>
>
> With an assumption that $P{TopN} is 10 then it should show 5 bars on a
> chart. However, let's consider following data:
>
> sip | sev | count
> 10.0.0.1 | 1 | 1
> 10.0.0.1 | 2 | 1
> 10.0.0.1 | 3 | 1
> 10.0.0.2 | 1 | 2
> 10.0.0.3 | 1 | 2
> 10.0.0.4 | 1 | 2
> 10.0.0.5 | 1 | 2
> 10.0.0.6 | 1 | 2
> 10.0.0.7 | 1 | 2
>
> Because data is ordered by "count" then it takes three times 10.0.0.1
> and puts it on a first bar with a value of 3 (which is a sum for each
> severity). It also increases counter by 3, so it shows only two more
> bars on a chart for 10.0.0.2 and 10.0.0.3. Of cours the value for
> 10.0.0.1 should not appear at all, because its sum is bigger than values
> for other IPs.
>
> The only solution which we have for now is removing "sev" from group by
> clause, but that way we are loosing some functionality of original Top
> 10 Report.
>
> Is it possible to solve it in any other way? Even if it was normal sql
> query I would doubt if it was possible to be done with one query only.
>
> Thanks
> Adam


0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.