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
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
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