RSA Envision custom report using aggregates in SQL clause gives error

[BalajiSastry]

I would like to create a Windows failed logons report that shows users exceeding 50 failed logons. How can I specify criteria to show me failed logons that exceed a number say 50. When I put in criteria where count(username) > 50, I get an error

Invalid SQL WHERE clause: .
ASA Error -150: Invalid use of an aggregate function.

Thanks for your help.

[RSAAdmin]

Please consider moving this question as-is (no need to recreate) to the proper forum for maximum visibility.  Questions written to the users' own "Discussions" space don't get the same amount of attention and can go unanswered for a long time.

You can do so by selecting "Move" under ACTIONS along the upper-right.  Then search for and select: "RSA enVisilon".

For further guidance on engaging with the communities please refer to the [DEAD LINK /docs/DOC-24433]IIG Communities Getting Started Guide.

[BalajiSastry]

Thanks, I have moved the discussion.

[UMBERTOZANATTA1]

Hi,

you can't do that. In my opinion the easiest way is use a correlation rule and an alarm.

[DelfinAbzueta]

Hi BalajiSastry

Consider to include a field like count(MessageID) as select fields when you are creating the report, then you can use it to validate the numbers of ocurrences in the where clause.

Cheers,

[PavelYosifov]

Hi BalajiSastry,

I would say that uzanatta is right. You can use a correlation rule and then create an alarm. You can then create a report based on that alarm.

Reports in enVision are ... limited in functionality. Everything you do when you configure a report is to filter only.

So actually you cannot relate events in reports. The system will never count how many times this has happened for this particular user, etc. That is a drawback of IPDB, actually.

DelfinAbzueta's suggestion will give you them total number of failed login events, but will not relate them to the same user...

So you will only see a number there - 2146 - and it will be for all users.

One report, that might just do it for you is TOP 10 failed logins by account. I think you can find it pre-built in enVision, or if not let me know and I can share the report with you. It's a graph chart, that will show you for a certain period the amount of failed logins that users have generated and show them for a specific user.

For example it will show you sysadmin - 56, tsmuser - 34, admin -12, etc.

The problem is that you cannot put a threshold of 50 here, so it could only show you the ones with more than 50...

[RSAAdmin]

I want to get Top 10 failed login by account using backup end sql query. For that how to to sql connectivity using command prompt ?

This is required to combine two or more dashboard view using backend utility. However i tried using lsdata but no result.

Regards

Anant

[RafaelAggeler]

There is actually a way of doing this, but you will not be able to modify the report through the  Web-GUI anymore, as the Web-Gui doesn't support it. But you can still run the report from Web-GUI of course!

So if you're interested in that anyway,...let me know 😉

[KevinBanyai]

I am interested... let's hear it

[RafaelAggeler]

Hi there

sorry for the late answer, Christmas and so on,...:-)

Well, I'll try to explain with an own report at first:

We create a report on the Windows table and select SourceAddress, Username, count(UserName) as fields.

Next we filter for MessageID Security_675, Username shall be longer than 4 characters and shall not end on '$'. We sort by count(Username) descending, the order of the fields is: SourceAddress, UserName, count(UserName).

The resulting XML looks like this:

<?xml version="1.0" encoding="UTF-8"?>

<report name="SomeReportName" dateTimeStamp="false" runtimeChecked="false" resultLimit="-1" distinct="false" regex="false" resolve="false"><description/><table name="Windows"/><chart><fieldList><field name="SourceAddress" sort="none" title="SourceAddress"/><field name="UserName" sort="none" title="UserName"/><field name="count(UserName)" sort="descending" title="count(UserName)"/></fieldList></chart><variables/><sql><![CDATA[MessageID = 'Security_675_Security' AND char_length(UserName) > 4 AND UserName NOT LIKE '%$' ]]></sql></report>

The interesting part is BOLD. As we know there's a Sybase DB in the back-end, so we might use all commands a Sybase database accepts. Unfortunately the enVision Web-UI does not accept them (partly because we have no control over ordering the statements, so they end up in a wrong order for Sybase and it would not work anyway...).

Let's change the bold statement above to the following:

MessageID = 'Security_675_Security' AND char_length(UserName) > 4 AND UserName NOT LIKE '%$') GROUP BY "saddr","username" HAVING COUNT(UserName) > 30 ORDER BY 3 DESC /*

Ok. What does it do? First part till NOT LIKE '%$' is same as before. Now we got a closing bracket there. Why? Because RSA enVision creates an opening bracket before the whole filter construct, but we just don't ever see that one. So we need to close it to be able to continue with our own statement.

Next comes the GROUP BY statement, which groups the results by sourceaddress and username. Then we got a HAVING COUNT(UserName) > 30 which will only show results that have a count higher than 30 and finally the ORDER BY 3 DESC /*.

ORDER BY 3 DESC orders the result by the count (it's in third position of the fields we selected, therefore 3).

And really important: /* comments out the whole rest of the SQL statement that RSA enVision would append to the report query. As we just created our own query here we don't need the rest that RSA enVision would append, so we comment it out. Actually we have to comment it out as the query will not be accepted by Sybase anymore,...it would be a really ugly mix between our own query and RSA enVision statements 😉

As mentioned before: You need to do those changes in the report XML directly, and afterwards you need to restart the NIC Webserver Service. You can't modify that report in the WEB-UI anymore.

Hope that's clear more or less,...otherwise just ask.

Regards

Rafael