Search Examples

Some search examples for reference.

SQL Expression EQL Expression Definition
select sys_eventTime,sys_body from LogLogic_Appliance where sys_eventTime between '2016-02-02' and '2016-02-03' use LogLogic_Appliance |columns sys_eventTime, sys_body | sys_eventTime between '2016-02-02' and '2016-02-03' Displays results from the LogLogic_Appliance data model where the records have the timestamp between '2014-02-02' and '2014-02-03'.
select * from LogLogic_Appliance where sys_body like '%Authentication%' and sys_eventTime between '2016-02-02' and '2016-02-03' use LogLogic_Appliance | sys_body contains "Authentication" | sys_eventTime between '2016-02-02' and '2016-02-03' Displays results from the LogLogic_Appliance data model with "Authentication" in the event body.
select * from LogLogic_Appliance where sys_body like '%logon%' and sys_eventTime between '2016-02-02' and '2016-02-03' limit 10 use LogLogic_Appliance | sys_body like '%logon%' | limit 10 | sys_eventTime between '2016-02-02' and '2016-02-03' Demonstrates an example of a 'like' statement to display a limit of 10 results.
select * from LogLogic_Appliance where sys_eventKey REGEXP '[az0-9|]*' and sys_eventTime in -10y limit 10 use LogLogic_Appliance | sys_eventKey REGEXP '[a-z0-9|]*' | sys_eventTime in -10y | limit 10 Demonstrates an example of the REGEXP expression matching.
select * from LogLogic_Appliance where sys_eventTime between '2016-02-02' and '2016-02-03' order by sys_eventTime DESC use LogLogic_Appliance | sys_eventTime between '2016-02-02' and '2016-02-03' | sort by sys_eventTime DESC Displays events sorted by time for records having timestamp for the specified dates in descending order.
select * from LogLogic_Appliance where sys_eventTime between

'2016-02-02' and '2016-02-03' order by sys_eventTime DESC limit 100

use LogLogic_Appliance | sys_eventTime between '2016-02-02' and '2016-02-03' | sort by sys_eventTime DESC | limit 100 Displays top 100 results for records sorted by time in descending order.
select sys_eventTime, sys_body from LogLogic_Appliance where sys_eventTime between '2016-02-02 14:34:34' and '2016-02-03 12:00:00' ORDER BY sys_eventTime DESC LIMIT 100 use LogLogic_Appliance | sys_eventTime between '2016-02-02 14:34:34' and '2016-02-03 12:00:00' | sort by sys_eventTime DESC | LIMIT 100 Display sorted first page of results for events ordered by time in descending order.
select ll_sourceUser, count(*) from LogLogic_Appliance where sys_eventTime between '2016-02-02' and '2016-02-03' group by ll_sourceUser use LogLogic_Appliance | group by ll_sourceUser columns ll_sourceUser, count(*) | sys_eventTime between '2016-02-02' and '2016-02-03' Displays grouped results based on the source users.
select ll_sourceUser, max(sys_eventTime), min(sys_eventTime), count(*) from LogLogic_Appliance where sys_eventTime between '2016-02-02' and '2016-02-03' group by ll_sourceUser use LogLogic_Appliance | group by ll_sourceUser columns max(sys_eventTime), min(sys_eventTime), count(*) | sys_eventTime between '2016-02-02' and '2016-02-03' Displays the count of rows for distinct source users and its corresponding maximum timestamp and minimum timestamp.
select ll_sourceUser, (max(ToLong(sys_eventTime ))- min(ToLong(sys_eventTime) ))/1000 as seconds from LogLogic_Appliance where sys_eventTime IN -10y group by ll_sourceUser use LogLogic_Appliance | sys_eventTime in -10y | group by ll_sourceUser COLUMNS ll_sourceUser, (max(ToLong(sys_eventTime) )- min(ToLong(sys_eventTime)) )/1000 as seconds Demonstrates the use of a complex expression in the COLUMNS statement. For each user, calculate the difference in time between the earliest and latest events. The time values are first converted to LONG (milliseconds), then subtracted, and finally divided by 1000 to convert the milliseconds to seconds.

Examples for geoiplookup()

SQL Expression EQL Expression Definition
select geoiplookup(sys_collectIP,"location"), sys_collectIP from Microsoft_Windows where sys_collectIP like '128%' use Microsoft_Windows_Windows | sys_collectIP like '128%' |COLUMNS sys_collectIP, geoiplookup(sys_collectIP,"location") Returns location information such as latitude, longitude, time zone corresponding to the IPv4 address specified in the sys_collectIP parameter.
select geoiplookup(sys_collectIP,"country"), sys_collectIP from Microsoft_Windows where sys_collectIP like '128%' use Microsoft_Windows | sys_collectIP like '128%' |COLUMNS sys_collectIP, geoiplookup(sys_collectIP,"country") Returns the country corresponding to the IPv4 address specified in the sys_collectIP parameter.
select geoiplookup(sys_collectIP,"country"), sys_collectIP from Microsoft_Windows where sys_collectIP like '2001%' use Microsoft_Windows | sys_collectIP like '2001%' | COLUMNS sys_collectIP, geoiplookup(sys_collectIP,"country") Returns the country corresponding to the IPv6 address specified in the sys_collectIP parameter.

Examples for matchcidr() and extractIPs()

SQL Expression EQL Expression Definition
select * from system where matchcidr("198.51.100.101/32", sys_collectIP) If sys_collectIP="198.51.100.101", 203.0.113.101", then:

use system | matchcidr("198.51.100.101/32", sys_collectIP)

 Returns the data that includes sys_collectIP as 198.51.100.101, because the IP address is within the CIDR range of "198.51.100.101/32".
select * from system where matchcidr("198.168.0.0-198.168.255.255",sys_collectIP) If sys_collectIP="198.51.100.101",203.0.113.101", then:

use system | matchcidr("203.0.0.0-203.0.255.255",sys_collectIP)

Returns the data that includes sys_collectIP as 203.0.113.101, because the IP address is within the range of "203.0.0.0-203.0.255.255".
  • select * from system where matchcidr("IPList",sys_collectIP)
  • select * from system where matchcidr("IPList",sys_collectIP, "IP1")
For an enrichment list named IPList:

{"198.51.100.101/32":"IP1","192.0.2.101/32":"IP2","203.0.113.0-203.0.113.255":"IP3","2001:DB8:4860::8888/32":"IP4"}

and if sys_collectIP="198.51.100.101",203.0.113.101", then:

  • use system | matchcidr("IPList",sys_collectIP)
  • use system | matchcidr("IPList",sys_collectIP,"IP1")
 Returns the data that includes sys_collectIP as 198.51.100.101, because this IP address is within the range 198.51.100.101/32 and matches the key IP1.

select minutes(sys_eventTime), count(*) from system where sys_collectIP="198.51.100.0" AND extractIPs(sys_body) !="" AND matchCidr( "192.0.2.0/24" , extractIPs(sys_body) ) GROUP BY minutes(sys_eventTime)

USE system | sys_collectIP="198.51.100.0" AND extractIPs(sys_body) !="" AND matchCidr( "192.0.2.0/24" , extractIPs(sys_body) ) | GROUP BY minutes(sys_eventTime) Returns the output of the query aggregated per minute

Examples for split() and extractIPs()

SQL Expression EQL Expression Definition
select split(sys_body,' ', 2) from system USE system | COLUMNS split(sys_body, ' ', 2)

Parsing rule in a data model:

Returns the third element in the list, after splitting sys_body column using space as the separator.
select * from system where sys_collectIP="192.0.2.0" and extractIPs(sys_body) !="" and split(extractIPs(sys_body),',',1) group by minutes(sys_eventTime) USE system | sys_collectIP="192.0.2.0" AND extractIPs(sys_body) !="" AND split(extractIPs(sys_body),',',1)!="" | GROUP BY minutes(sys_eventTime)

Aggregation rule query:

Returns the output of the query aggregated per minute for results where extractIPs returns the list with more than one values. Otherwise split returns false.

select split(sys_collectIP, '\\.',3) from system USE system | COLUMNS split(sys_collectIP, '\\.', 3) Returns the last numbers of the IP address provided in sys_collectIP and separated by dot (the regex '\\.' is for the dot character)
  • select split(sys_body, ' ', 4) from system
  • select extractIPs(sys_body) from system where extractIPs(sys_body)!="" and split(extractIPs(sys_body),',',1)!=""
  • USE system | COLUMNS split(sys_body, ' ', 4)
  • USE system | extractIPs(sys_body) != "" AND split(extractIPs(sys_body),',',1)!="" | COLUMNS extractIPs(sys_body)

Advanced search queries:

  • Using space as a separator, returns the fifth element from the value of sys_body column.
  • Returns a list of extracted IPs from sys_body with more than one IP addresses.
select * from system where sys_collectIP="192.0.2.0" and extarctIPs(sys_body)!="" and split(extractIPs(sys_body),',',1)!="" group by minutes(sys_eventTime) USE system | sys_collectIP="192.0.2.0" AND extractIPs(sys_body) !="" AND split(extractIPs(sys_body),',',1)!="" | GROUP BY minutes(sys_eventTime) Aggregation rule query:

Returns the output of the query aggregated per minute for results where extractIPs() returns a list with more than one values. Otherwise split() returns false.