Structure keywords
from |
Point to a table for the query. Specify a table name using tags. from my.app.web.auth |
where |
Where clause to filter results.
where [filter1 expression], [filter2 expression]
String values in expressions have to be surrounded by double quotes, single quotes are not allowed. |
select |
Add column to result set: select source_column or column operation as destination_column select uriHost(uri) as host |
group every / by |
Group clause with an optional server and client aggregation period filter: group [every server_period] [by column] [every client_period] group by statusCode group every 10m group every 10m by statusCode every 1m |
every |
Client aggregation filter: every period
Period syntax: an integer number follow by a symbol indicating the time period:[s:seconds, m:minutes, h:hours, d:days, no suffix:milliseconds]
every 0 means no client period.
every 5m by serverIp
|
ifthenelse |
if/then/else equivalent clause to set conditionally column values:
ifthenelse(condition, errorValue, successValue )
select ifthenelse(statusCode != 200,"Error","Success") as statusCodeDesc |
decode |
switch/case equivalent clause to set conditionally column values:
decode(column, checkValue, value,[checkValue2, value2])
Each pair of arguments [checkValue, value] is equivalent to a case sentence of a switch statement.
decode(statusCode, 200, "Success", 400, "Not Found", 406, "Error", 404, "Error") as statusCodeDesc |
nvl |
Null-Coalescing operator. Allow to set an alternate value when input value is null.
nvl(column, alternate_value_when_null)` |
Aggregation functions and operators
avg |
Returns the average of a range of values [avg] or only over not null values [nnavg] of the results on each group:
avg(column); nnavg(column) |
count |
Returns the count of results on each group:
count([column])
With argument, include only not null entries in the count. |
first / nnfirst |
Returns the first or the not null first entry of the results on each group:
first(column); nnfirst(column) |
last / nnlast |
Returns the last or the not null last entry of the results on each group:
last(column); nnlast(column) |
max / min |
Returns the maximum or the minimum value for the columns provided, on each group:
max/min(col1, [col2], [col3]…) |
median |
Returns the statistical median for a column on each group:
median(column)
Restricted to columns of integer type |
sum |
Returns the sum of the results on each group:
sum(column) |
sum2 |
Returns the sum of the squares of the results on each group:
sum2(column) |
percentile5 percentile10 percentile25 percentile75 percentile90 percentile95 |
Returns the specific statistic percentileN, using linear interpolation, of the results on each group:
percentile[N](column) |
stddev / nnstddev |
Returns the biased standard deviation [stddev] of the values or not null values [nnstddev] of the results on each group:
stddev/nnstddev(column)
Biased |
ustddev / nnustddev |
Returns the unbiased standard deviation [ustddev] of the values or not null values [unnstddev] of the results on each group:
ustddev/unnstddev(column)
Unbiased |
var / nnvar |
Returns the biased variance [var] of the values or not null values [nnvar] of the results on each group:
var/nnvar(column)
Biased |
uvar / nnuvar |
Returns the unbiased variance [uvar] of the values or not null values [unnvar] of the results on each group:
uvar/unnvar(column)
Unbiased |
hllpp |
Returns the estimated count of distinct values of the results on each group using the HyperLogLog++ algorithm:
hllpp(column)
Applies on DC (distinct count) data types. |
hllppcount |
Returns the estimated count of distinct values of the results on each group using the HyperLogLog++ algorithm:
hllppcount(column)
Applies on float or integer data types. |
String operators and functions
has, [->] |
Case sensitive contains comparison. Using the operator -> only allows check one value:
has(column, value1, [value2],…)
column -> value1 |
weakhas |
Case insensitive contains comparison:
weakhas(column, value) |
in, [<-] |
Case sensitive is contained comparison. Using the operator '<-' allows only one value:
in(value1, [value2],[...], column) value1 <- column |
weakin |
Case insensitive is contained comparison:
weakin(value, column) |
startswith |
Returns strings that start with specific value:
startswith(column, value) |
endswith |
Returns strings that end with a specific value:
endswith(column, value) |
toktains |
Specialized contains function for ASCII delimited tokens:
toktains(column, value, [bool_left], [bool_right]) |
length |
Returns the length of a string value:
length(column) |
locate |
Returns the position of a substring, indexOf function:
locate(column, substring_toLocate) |
lower |
Returns the transformation to lower case:
lower(column) |
upper |
Returns the transformation to upper case:
upper(column) |
replace |
Replaces only first occurrence of a string with a substitute string:
replace(column, stringToSearch, stringToReplace) |
replaceall |
Replaces all occurrences of a search string with a substitute string:
replaceall(column, stringToSearch, stringToReplace) |
split |
Returns a specific piece of splitting operation by a separator:
pieceNumber begin at 0. split(column, separatorString, pieceNumber) |
splitre |
Returns a specific piece of splitting operation by a regular expression:
pieceNumber begin at 0. splitre(column, re(string) or regexp, pieceNumber) |
substring |
Returns a substring beginning at specific index with the provided length:
substring(column, index, length) |
subs |
Returns a string replacing first substring occurrence based on a regular expression using a template string as substitution value:
FailValue is returned when is provided and no occurrences found subs(column, regexp, template, [failValue]) subs(column, re(string), template(string), [failValue]) |
subsall |
Returns a string replacing all substring occurrences based on a regular expression using a template string as substitution value:
FailValue is returned when is provided and no occurrences found subs(column, regexp, template, [failValue]) subs(column, re(string), template(string), [failValue]) |
trim |
Returns the result of trimming both sides:
trim(column) |
ltrim |
Returns the result of trimming left side:
ltrim(column) |
rtrim |
Returns the result of trimming right side:
rtrim(column) |
matches, [~] |
Matches function that finds occurrences in a column using a regular expression:
matches(column, re(string) or regexp value) column ~ re(string) or regexp value |
peek |
Returns the part of a string based on a regular expression, optionally indicating a specific part occurrence:
If no partNumber is provided then returns first part occurrence. peek(column, re(string) or regexp, [partNumber]) |
formatnumber |
Format a number with a specific mask and locale:
formatnumber(numberColumn, mask, locale) formatnumber(totalAmount, "###.##", "en-GB") |
damerau |
Returns Damerau distance:
damerau(column, value) |
hamming |
Returns Hamming distance:
hamming(column, value) |
levenshtein |
Returns Levenstein distance:
levenshtein(column, value) |
osa |
Returns osa distance:
osa(column, value) |
publicsuffix |
Returns the main public suffix of a hostname:
publicsuffix(hostnameColumn)) 'www.my.site.co.uk' = 'co.uk' |
rootdomain |
Returns the root domain of a hostname part of an url:
rootdomain(hostnameUrlColumn) 'www.my.site.com' = 'site' |
rootprefix |
Returns the root prefix of a hostname part of an url:
rootpredix(hostnameUrlColumn)) 'www.my.site.com' = 'www.my.site' |
rootsuffix |
Returns the root suffix of a hostname part of an url:
rootsuffix(hostnameUrlColumn)) 'www.my.site.com' = 'my.site.com' |
subdomain |
Returns the subdomain of a hostname part of an url:
subdomain(hostnameUrlColumn) 'www.my.site.com' = 'www' |
topleveldomain |
Returns the top level domain of a hostname part of an url::
topleveldomain(hostnameUrlColumn) 'www.my.site.co.uk' = 'uk' |
shannonentropy |
Web functions
urischeme |
urihost |
uriport |
uripath |
urifragment |
uriquery |
uriuser |
urissp |
uriauthority |
absoluteuri |
opaqueuri |
urldecode |
uaurl |
uaname |
uatype |
uaversion |
uaicon |
uarobot |
uainfourl |
uafamily |
uacompany |
uacompanyurl |
uadeviceicon |
uadeviceinfourl |
uadevicetype |
uaosurl |
uaosname |
uaosicon |
uaosfamily |
uaoscompany |
uaoscompanyurl |
uaosversion |
Possible missing function to filter by OS version. |
|
|
Data Types
str |
String |
int |
Integer number: 1,58,12598 |
float |
Floating point number:24.256 |
boolean |
Boolean: true, false |
timestamp |
Timestamp date in format: yyyy-MM-dd HH:mm:ss.SSS |
boxar(int) |
Byte array in hexadecimal string format |
duration |
Amount of time: an integer following by a letter [d]ays, [h]ours, [m]inutes, [s]econds, [No suffix]:milliseconds |
geocord |
Geographic coordinates set: Latitude/longitude sexagesimal values: 40º24'N 3º41'W Hash representation of coordinates (geohash) |
ip |
IPv4 address format: 192.168.5.56 |
ip6 |
IPv6 address format: 2001:0db8:85a3:0000:0000:8a2e:0370:7334 |
net4 |
IPv4 address in format: {x.x.x.x/0 |
net6 |
IPv6 address in format: x.x.x.x.x.x/s |
regexp |
Regular expression: [^\w] |
template |
Represents a substitution string mask. |
dc |
Represents a estimated count of distinct elements in a data stream. |
image |
Image as Base64 encoding image. |
mac |
MAC address in format: 00:0a:95:9d:68:16 |
namepattern |
Represents a part of a table name: my.app, demo, ... |
set(name) |
Represents a set of table names: {my.app.test, my.app.test2} |
json |
String in json format: {"id":345, "name":"John"} |
jq |
Represents a jq filter, jq is a command line json processor.
.email |
Common comparison functions and operators
eq, [=] |
Equals to function and operator:
eq(column, value or column) column1 = value or column |
eqic |
Case insensitive Equals to function:
eqic(column, value or column) |
ge, [>=] |
Greater or equal function and operator:
ge(column, value or column) column >= value or column |
gt, [>] |
Greater than function and operator:
gt(column, value or column) column > value or column |
le, [<=] |
Less or equal function and operator:
le(column, value or column) column <= value or column |
lt, [<] |
Less than function and operator:
lt(column, value or column) column < value or column |
ne, [/=] |
Not equal function and operator:
ne(column, value or column) column /= value or column |
isnull |
Check if is null function:
isnull(column) |
isnotnull |
Check if is not null function:
isnotnull(column) |
Math functions and operators
abs |
add / [+] |
sub / [-] |
mul / [*] |
div / [\] |
rdiv / [/] |
Real division function and operator: |
mod / [%%] |
Module function: |
rem / [%] |
Return the remain of a division operation: |
pow |
Power function: |
cbrt |
Cube root function: |
sqrt |
Square root function: |
ceil |
floor |
round |
signum |
Statistical and specialised statistical functions
estimation |
pack |
unpackhllpp |
Network functions
ispublic |
isprivate |
ipip4 |
ipprotocol |
purpose |
host |
routing |
httpstatusdescription |
httpstatustype |
reputation |
score |
sbl |
Conversion functions
int |
str |
bool |
float |
image |
ip4 |
net4 |
ip6 |
net6 |
mac |
to16 |
from16 |
to64 |
from64 |
toutf8 |
fromutf8 |
toz85 |
fromz85 |
compatible |
mapped |
translated |
template |
timestamp |
duration |
re |
parsedate |
formatdate |
humansize |
mkboxar |
Special comparison functions
matches |
Matches function finds occurrences in a column using a regular expression:
matches(column, re(string) or regexp value) column ~ re(string) or regexp value |
anymatches |
Find occurrences in a set of names type column against a namepattern:
anymatches(setOfNames, nameglob(string) or namepattern) anymatches(tables, nameglob("my.app.*.*")) |
nameglob |
Return a formmated string as a namepattern to use with anymatches:
nameglob(string) |
Date and time functions
day |
dayofweek |
dayofyear |
month |
year |
epoch |
hour |
minute |
second |
millisecond |
today |
tomorrow |
yesterday |
period |
Packet functions
hasio4 |
hastcp |
hasudp |
hasether |
ip4proto |
ip4src |
ip4dst |
ip4status |
ip4ttl |
ip4len |
ip4payload |
ip4flags |
ip4fragment |
ip4cs |
ip4hl |
ip4ds |
ip4ecn |
ip4tos |
etherdst |
ethersrc |
etherpayload |
etherstatus |
ethertag |
ethertype |
tcpdst |
tcpsrc |
tcpstatus |
tcpflags |
tcppack |
tcpcs |
tcpseq |
tcphl |
tcppayload |
tcpurg |
tcpwin |
udpsrc |
udpport |
udpstatus |
udpcs |
udplen |
udppayload |
|
Created By
Metadata
Comments
Hi Can u help me to organize blocks in proper manner. It aligns itself and not utilizing the page fully.
Add a Comment
Related Cheat Sheets