Bitwise operators in AQL queries

Enhance the filtering capability and performance of your AQL queries that include IP addresses by using bitwise operators. Specify filters at the IP address octet level to return specific results.

By filtering on octets in an IP address, you can refine the IP address search criteria.

For example, to search for specific device types whose last octet in a source IP address ends in 100, such as x.y.z.100, you can use the following query:

SELECT LONG(sourceip)AS long_ip, 
sourceip
FROM events into <cursor_name>
WHERE (long_ip & 0x000000ff)=0x00000064
GROUP BY long_ip
ORDER BY long_ip

In the example, the <sourceip> is returned as an integer. The integer is used by the bitwise AND operator. The hexadecimal value <ff> in the last octet position for the source IP address specifies a filter in the corresponding IP address octet position of 0x000000<IP address octet hexidecimal value>. In this case, the hexadecimal value <64> is substituted for the decimal value 100 in the IP address.

The result is all source IP addresses that end in 100. The results can be a list for a specific device type for a company, if the last octet of all of the IP addresses is 100.

The following examples outline scenarios to use when you search with bitwise operators.

Bitwise AND (&) examples

Returns all IP addresses that match 10.xxx.xxx.xxx

SELECT LONG(sourceip)AS long_ip, 
sourceip
FROM events into t1
WHERE (long_ip & 0xff000000)=0x0a000000 
GROUP BY long_ip 
LIMIT 50

Returns all IP addresses that match xxx.100.xxx.xxx

SELECT LONG(sourceip)AS long_ip, 
sourceip
FROM events into t2
WHERE (long_ip & 0x00ff0000)=0x0064000
GROUP BY long_ip
ORDER BY long_ip

Returns all IP addresses that match xxx.xxx.220.xxx

SELECT LONG(sourceip)AS long_ip, 
sourceip
FROM events into t3
WHERE (long_ip & 0x0000ff00)=0x000dc00
GROUP BY long_ip
ORDER BY long_ip

Returns all IP addresses that match xxx.xxx.xxx.1

SELECT LONG(sourceip)AS long_ip, 
sourceip
FROM events
WHERE (long_ip & 0x000000ff)=0x0000001
GROUP BY long_ip
ORDER BY long_ip

Bitwise NOT (~) examples

Use the following examples to convert each 1-bit value to a 0-bit value, or each 0-bit value to a 1-bit value, in a given binary pattern.

SELECT ~123456789
FROM events
LIMIT 1

Returns 123456790

SELECT ~0 
FROM events
LIMIT 1

Returns -1

SELECT ~2147483647
FROM events
LIMIT 1

Returns - 2147483648

Bitwise OR examples

Use the following examples compare two bits. If both bits have a value of "1", then the query returns a 1. If both bits have a value of "0", then the query returns a 0.

SELECT destinationip, 
LONG(destinationip),
sourceip,
LONG(sourceip)AS source_ip,
LONG(destinationip)|source_ip
FROM events
WHERE destinationip='127.0.0.1' 
LIMIT 1

SELECT destinationip, 
LONG(destinationip),
sourceip,
~LONG(sourceip)AS not_source_ip,
LONG(destinationip)|not_source_ip
FROM events 
WHERE destinationip='127.0.0.1'
LIMIT 1

SELECT-2147483648|2147483647
FROM events
LIMIT 1

Returns -1

Bitwise XOR examples

The following examples can be used to take 2-bit patterns, or a pair of bits from each position, and convert them to either a 1 or a 0. If the bits are different, the result in that position is 1. If the bits are identical, the result in that position is 0.

SELECT 2147483647#2147483647
FROM events
LIMIT 1

Returns 0

SELECT 12345#6789 
AS A,
(~12345 & 6789)|(12345 & ~6789)
AS B
FROM events
LIMIT 1

Returns 10940, 10940

ShiftLeft examples

The number of places to shift is given as the second argument to the shift operator.

SELECT -1<<1 
AS A 
FROMS events 
LIMIT 1

Returns -2

SELECT 16<<1 
AS A 
FROMS events 
LIMIT 1

Returns 128

ShiftRight examples

The operator >> uses the sign bit, which is the left-most bit, to fill the trailing positions after the shift. If the number is negative, then 1 is used as a filter and if the number is positive, then 0 is used as a filter.

SELECT 16>>3
AS A 
FROMS events 
LIMIT 1

Returns 2

SELECT -32768>>15 
AS A 
FROMS events 
LIMIT 1

Returns -1

ShiftRightUnsigned example

Always fills 0 regardless of the sign of the number.

SELECT -1>>>33
FROM events
LIMIT 1

Returns 2147483647

Dividing by the power of 2.

SELECT (20+44)>>>1 A,
(20+44)>>>2 B,
(20+44)>>>3 C,
(20+44)>>>4 D,
(20+44)>>>5 E
FROM events
LIMIT 1