ESA rule broken at 11.3

[BohdanR]

We recently upgraded from NetWitness 10.6.6 to 11.3. Several rules got disabled during the upgrade and they no longer work.
I suppose it is mainly because directory meta changed type from string to string[], so that it became an array.

Simplified version of the rule, which compares directory in the event with directory in enrichment and it fires alert if there is match on the directory (or its subdirectory, that is why there is .startsWith):

@RSAAlert(oneInSeconds=0)
@UsesEnrichment(name='folders_test')
SELECT * FROM Event (
(device_type IN ( 'emcisilon' ) AND directory IS NOT NULL AND
EXISTS (SELECT * FROM folders_test WHERE(Event.directory.toLowerCase().startsWith(directory)) )
) );

Enrichment RSI1_folders_test is in-memory table and contains several folder paths, named as directory of type string.

The rule worked fine at Netwitness 10.6.6.
The rule is no longer possible to be deployed to ESA with 11.3, because directory doesn't have method toLowerCase now (it is not a string anymore).

I tried converting directory to string using cast(Event.directory,string), which works when it is directly in event filters (except there is [ character at start and ] at end of the string), but it doesn't seem to work properly when used together with the enrichment.

The bellow rule fires alert on all events of type emcisilon, no matter if startsWith on the directory matches or not (EXISTS part seems to result always to TRUE for some unclear reason):

@RSAAlert(oneInSeconds=0)
@UsesEnrichment(name='folders_test')

SELECT * FROM Event (
(device_type IN ( 'emcisilon' ) AND directory IS NOT NULL AND
EXISTS (SELECT * FROM folders_test WHERE( cast(Event.directory,string).toLowerCase().startsWith(directory,1) ) )
) ) ;

I tried various things, but didn't manage to get this rule working with 11.3.

Can you please advice how to fix the rule to work in Netwitness 11.3 ?

[BohdanR]

The rule after the suggested modification is here:

@UsesEnrichment(name = 'I2_folders')
@RSAAlert(oneInSeconds=0)
SELECT * FROM Event.win:length(1),I2_folders (
(
device_type = 'emcisilon' AND
event_type IN ('OPEN', 'DELETE', 'RENAME', 'WRITE', 'SET_SECURITY') AND
username IS NOT NULL AND
directory IS NOT NULL AND
directory.toArray().anyOf(i => cast(i,string).toLowerCase() LIKE I2_folders.directory || '%')
AND
Event.username.toArray().anyOf(i => cast(i,string).toLowerCase() != I2_folders.username)
) );

But this rule fails to deploy with the error message in log that I mentioned

[LeeKirkpatrick]

Your filter for the Event stream is in the wrong location and why you are receiving the error, the WHERE clause also needs to be there - this will take place after the join between the Event stream and I2_folders window. Make the following change and redeploy:

@UsesEnrichment(name = 'I2_folders')
@RSAAlert(oneInSeconds=0) 
SELECT * FROM Event(
	device_type = 'emcisilon' 
	AND 
	event_type IN ('OPEN', 'DELETE', 'RENAME', 'WRITE', 'SET_SECURITY') 
	AND
	username IS NOT NULL 
	AND
	directory IS NOT NULL).win:length(1),I2_folders

WHERE
	directory.toArray().anyOf(i => cast(i,string).toLowerCase() LIKE I2_folders.directory || '%')
	AND
	Event.username.toArray().anyOf(i => cast(i,string).toLowerCase() != I2_folders.username)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[BohdanR]

Thank you very much Lee! It seems it works now 🙂

[BohdanR]

Hi Lee, I am still facing issues with that EPL rule with directory meta involved
I am receiving a lot of these warnings in /var/log/messages on ESA every few seocnds:

May 20 12:58:54 esa-server correlation-server.jar: 2019-05-20 12:58:54.775  WARN 1391 --- [-managed-stream] bselectEvalStrategyRowUnfilteredSelected : Subselect of statement 'APP-SI-001' returned more then one row in subselect 2 'directory', returning null result

The complete rule is:

@UsesEnrichment(name = 'SI1_folders')
@UsesEnrichment(name = 'SI1_blacklist')
@RSAAlert(oneInSeconds=0)
SELECT * FROM Event (
(
device_type = 'emcisilon' AND
event_type IN ('OPEN', 'DELETE', 'RENAME', 'WRITE', 'SET_SECURITY') AND
reference_id IS NOT NULL AND
directory IS NOT NULL AND
EXISTS (SELECT * FROM SI1_blacklist WHERE ( Event.reference_id = SID )) AND /* blacklist */
Event.directory.toArray().anyOf(i => cast(i,string).toLowerCase().concat('/') LIKE (SELECT directory FROM SI1_folders) || '%' )
) );

Do you have any idea what could be wrong there? I have a suspicion that it expects  (SELECT directory FROM RSI1_folders)  to return only one result, not the whole list...

[NikolayKlender]

In our environment we user log part only so this new array metas also broke lots of my rules. Recently we user cast approach to transofrm array values to string like: cast(host_alias,string) but in this case casting add '[' and ']' character. I found good way how to deal with it. At the beginning of rule add  expression like this

create expression string one(arr)[ arr==null?'':arr.toString().substring(1, arr.toString().length-1) ];

next in rule you can use this function like this

where one(alias_host)='myvalue'

so it is more readable than

where cast(alias_host,string)='[myvalue]'

[BohdanR]

I found a better and more effective solution. Your expression won't work in case that more than one alias_host apperars in the event.

This one seems to work fine, at least in NW 11.3:

expression string firstString(arr)[ arr==null?'':(arr.get(0))]‍‍‍

It returns first string from the array.

then using e.g.:

firstString(alias_host).toLowerCase() = 'myvalue' ‍

works fine.

Surprisingly, if I use just alias_host.get(0), without using a defined expression, doesn't work - it thinks that .get is some method for date and complains that alias_host is not a date. Although in expression it works fine 🙂

Built-in functions like ANY, ALL, matchLike are useless if I need to compare keys case-insensitive (e.g. a user name in events with users in enrichment), so either this workaround or iterating via e.g. asStringArray(username).anyOf(v => v  = something ) is needed.