ESA Rule casting alias_host array to string

[MaximilianoCitt]

We are trying to build a rule that trigger when the ip_src is equal to the ip assigned to a vpn user.

to do that, I have created a memory window using this:

// Create Window to store users and IP assignments
CREATE WINDOW MAXActiveVPNUsers.win:time(7 days) (user_dst string, ip_src string, Privada string);

// Insert into the Window, user and IP values where connected
INSERT INTO MAXActiveVPNUsers

SELECT user_dst, ip_src, cast(alias_host,string) as Privada
FROM Event(user_dst IS NOT NULL
AND ip_src IS NOT NULL AND alias_host IS NOT NULL
AND device_ip='10.245.197.18'
AND event_desc = 'assigned to session'
AND device_type='ciscoasa');

// Remove users from Window when they disconnect
ON pattern[every s1=Event(user_dst IS NOT NULL
AND ip_src IS NOT NULL
AND event_cat_name = 'Network.Connections.Terminations.VPN'
AND device_type='ciscoasa')]
DELETE FROM MAXActiveVPNUsers
WHERE ip_src=s1.ip_src AND user_dst=s1.user_dst;

As you can see the first part put into memory user as they have vpn sessions (ip_src is for the public ip address and alias_host is for private ip address asigned by the vpn). The thing here is that the parser uses alias.host to retrieve the private ip address...and that metakey is an array so, I have to cast it to a string.

When I check that into the ESA using:

localhost:com.rsa.netwitness.esa:/CEP/Engine/cepWindows>jmx-invoke query --param "SELECT * FROM MAXActiveVPNUsers"

it returns:

{
"MAXActiveVPNUsers": {
"user_dst": "user1",
"ip_src": "190.115.166.193",
"Privada": "[10.245.223.53]"
}
}
, {
"MAXActiveVPNUsers": {
"user_dst": "user2",
"ip_src": "170.11.242.54",
"Privada": "[10.245.223.209]"
}
}

here "Privada" has those [] that has been putted there while casting... there is any way to get that [] out? because I have another rule as follows:

@RSAAlert(oneInSeconds=0)
SELECT * FROM Event(
ec_activity.toLowerCase() = 'logon'
AND
ip_src IS NOT NULL
AND
device_class.toLowerCase() = 'unix'
)
WHERE ip_src IN ( SELECT Privada FROM MAXActiveVPNUsers)
;

I think this second part doesn't tirgger because of that [] created while casting to string

Could please anyone help me  with this?

[robertofasciani]

Ciao Maximiliano,

I'm not sure but you could use :

CREATE WINDOW MAXActiveVPNUsers.win:time(7 days) (user_dst string, ip_src string, alias_host string[]);

.

.

.

.

@RSAAlert
SELECT * FROM Event(
ec_activity.toLowerCase() = 'logon'
AND ip_src IS NOT NULL
AND device_class.toLowerCase() = 'unix'
AND EXISTS
(SELECT * FROM MAXActiveVPNUsers
         WHERE Event.ip_src = ANY(alias_host)
 )
);

[MaximilianoCitt · Accepted solution]

Ciao Roberto!

I've solved this using the substring function of java 

@RSAAlert(oneInSeconds=0)
SELECT * FROM Event(
ec_activity.toLowerCase() = 'logon'
AND
ip_src IS NOT NULL
AND
device_class.toLowerCase() = 'unix'
) As Evento

WHERE ip_src IN (SELECT Privada.substring(1, Privada.length()-1) as ip FROM MAXActiveVPNUsers)

that's the way I've removed those "[ ]" and get the Where working.

Thank you so much for your help

[robertofasciani]

Good,

or, if you do not want to use the substring function, you could use

WHERE ip_src IN (SELECT Privada(0) FROM MAXActiveVPNUsers)

By

[MaximilianoCitt]

Great... I will try that!