InsightIDR

Example Queries

Use in Advanced Mode

You can use these example queries to craft what you need for your own logs. Use them in Advanced Mode.

Browse through one of the categories below for an example query that fits your needs:

Active Directory Admin Activity

Find all users who completed an “admin action”

  • groupby(source_user)

Show all “admin actions”

  • groupby(action)

Find all activity taken by a specific user

  • where(source_user=”Arnold Holt”)
  • where(source_user=”Tina Gonzales (Admin)”)

For a case-insensitive search, use /i

  • where(source_user=/arnold holt/i)
  • where(source_user=/tina gonzales \(admin\)/i)
  • where(source_user=/tina gonzales \(admin\)/i)groupby(action)

Find all users with “admin” in their user name

These return case insensitive results.

  • where(source_user=/.*admin.*/i)groupby(source_user)
  • where(source_user=/.*admin.*/i)groupby(action)

Find all groups that a user was added to by someone with “admin” in their name

  • where(source_user=/.*admin.*/i AND action="MEMBER_ADDED_TO_SECURITY_GROUP")groupby(group)

Show all users added to a particular group

  • where(action=”MEMBER_ADDED_TO_SECURITY_GROUP” AND group="vpn-users")groupby(target_user)

Show accounts that added users to groups

  • where(action=”MEMBER_ADDED_TO_SECURITY_GROUP”)groupby(source_user)

Show group changes made to a certain group

  • where((action="MEMBER_ADDED_TO_SECURITY_GROUP" OR action="MEMBER_REMOVED_FROM_SECURITY_GROUP") AND group=/.*-job-admins/)

Replace /*.-job-admins/ with the appropriate group name

Admin Account Created by Host

  • where(/:\d{2} (?P<host>\w+)./ AND /4732 EVENT/ OR /\s636 EVENT/) groupby(host)

Accounts Locked out by Host

  • where(/:\d{2} (?P<host>\w+)./ AND /4740 EVENT/ OR /\s644 EVENT/) groupby(host)

Audit Log cleared by Host

  • where(/:\d{2} (?P<host>\w+)./ AND /1102 EVENT/ OR /\s517 EVENT/) groupby(host)

Audit Policy Changed

  • where(/4719 EVENT/ OR /\s612 EVENT/)

Asset Authentication

Show all authentication types

  • groupby(logon_type)

Show all authentication results

  • groupby(result)

Show all failed authentication activity

  • where(result!=”SUCCESS”)groupby(destination_user)calculate(count)

Show all failed authentication activity

  • where(result=/FAILED.*/)groupby(result)

Failed Logins by IP (ReGex)

  • where(/(?P<ip>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})/) groupby(ip) calculate(count)

Failed logon - Non Kerberos

  • where(service!="krbtgt" AND service!="kerberos" AND result="FAILED_BAD_PASSWORD" )

Non-Kerberos Logon by Dest Asset

  • where(service!="krbtgt" AND service!="kerberos" AND result="FAILED_BAD_PASSWORD" )groupby("destination_asset")

Invalid Logins

  • where(/4625 EVENT/ OR /\s529 EVENT/)

Invalid Logons by Host

  • where(/:\d{2} (?P<host>\w+)./ AND /4625 EVENT/ OR /\s529 EVENT/) groupby(host)

Asset Authentication, Active Directory Domain Activity, File Access Activity

Works with Microsoft Logs only.

Show all Microsoft Event IDs for collected events

  • where(/eventCode\\":(?P<EVID>\d{4})/)groupby(EVID)
  • where(/eventCode\\":\\"(?P<EVID>\d{4})/)groupby(EVID)

Show all hosts that logs have been collected from

  • where(/computerName\\":\\"(?P<HostName>[\w\d\-]*)/)groupby(HostName)

Ingress Authentication

Show all events where the user logged in from a certain country

  • where(geoip_country_name="United States")calculate(count)
  • where(geoip_country_name="United States")groupby(user)calculate(count)

Show users accessing the network from a specific City

  • where(geoip_city="San Jose")groupby(user)

Show ingress from a certain country

  • where(geoip_country_name="Russia")

Show users accessing the network from a particular service

  • where(service="box")groupby(user)sort(desc)
  • where(service="o365")groupby(user)sort(desc)

Show users accessing the network from countries other than the United States

  • where(geoip_country_code!="US") groupby(geoip_country_name) sort(desc)

Firewall Activity

Show countries that users downloaded data from

  • where(incoming_bytes>0 AND geoip_country_code!=/US|IE|GB|DE|JP|CA|AU/)groupby(geoip_country_code)

The list of excluded countries should be modified as needed

Show all firewall traffic from countries other than the US

  • where(geoip_country_name!="United States")groupby(geoip_country_name)

Display a count of all firewall logs

  • calculate(count)
    Useful to see trend in amount of logs collected

Display top 10 external systems (outside of the US) receiving the most data

  • where(direction=OUTBOUND AND geoip_country_code!=US)groupby(destination_address)calculate(sum:outgoing_bytes)sort(desc)limit(10)

Use “limit” to set the number of results to return

Display the top 10 internal systems receiving the most data

  • where(direction=INBOUND)groupby(destination_address)calculate(sum:incoming_bytes)sort(desc)limit(10)

Show all users accessing a particular destination

  • where(direction="OUTBOUND" AND destination_address="52.205.169.150")groupby(user)sort(desc)

Show countries with a connection status of deny

  • where(connection_status AND connection_status="DENY" AND geoip_country_code!="US")groupby(geoip_country_name) sort(desc)

Show denied outbound traffic

  • where(direction="OUTBOUND" AND connection_status="DENY")calculate(count)

Display all used outbound ports except for 443, 80, and 53

  • where(connection_status AND connection_status="ACCEPT" AND direction="OUTBOUND" AND destination_port!=/443|80|53/)groupby(destination_port)

Show top outbound destinations

  • where(direction=OUTBOUND)groupby(destination_address)calculate(sum:outgoing_bytes)sort(desc)

Show top inbound destinations

  • where(direction=INBOUND)groupby(source_address)calculate(sum:incoming_bytes)sort(desc)

Demo for Blacklisted Countries

  • where(geoip_country_name = /Czechia|Russia|Hong Kong/ AND connection_status = "ACCEPT" AND direction="INBOUND")groupby(geoip_country_name)

External Firewall Denies by subnet

  • where(connection_status = DENY AND source_address != /10.*/ AND source_address != /172.27.*/ AND source_address != /169.254.*/ AND source_address != /192.168.*/ AND source_address != /172.16.*/)

Visual Search for Firewalls

  • user!="unknown" AND connection_status = DENY AND source_address != /10.*/ AND source_address != /172.27.*/ AND source_address != /169.254.*/ AND source_address != /192.168.*/ AND source_address != /172.16.*/

Foreign Invalid connection attempts

  • where(connection_status="DENY" AND geoip_country_name!="United States") groupby(geoip_country_name) calculate(count)

Inbound Denies by Country

  • where(connection_status=DENY AND direction=INBOUND and geoip_country_name!="United States") groupby(geoip_country_name) calculate(count)

Large Data Transmission - Box

  • where(direction="OUTBOUND" AND outgoing_bytes>50000000 AND geoip_organization="Box.com")

Example for Docker Traffic (RAW) - Received in Bytes

  • where(stats.networks.eth0.rx_bytes!=null) calculate(average:stats.networks.eth0.rx_bytes)

DNS Query

Show where users are browsing outside of .com, .net, and .org domains

  • where(public_suffix AND public_suffix!="com" AND public_suffix!="net"AND public_suffix!="org") groupby(public_suffix) sort(desc)

Display websites in Russia visited by users

  • where(public_suffix="ru")groupby(query)sort(desc)

Display graph showing when users access a website the most

  • where(/facebook/ AND user!="unknown")calculate(count)

Show all users who have accessed Dropbox

  • where(/dropbox/ AND user!="unknown")groupby(user)

Show all users who have accessed Facebook

  • where(/facebook/ AND user!="unknown")groupby(user)

File Access Activity

Display files accessed by a certain user

  • where(user="Pete Coors")groupby(file_name)

Show users who accessed a certain file

  • where(file_name="audit.csv")groupby(user)

Show Known Users Accessing Safebrowsing

This query limits results to 20 users.

  • where(query="safebrowsing.google.com" AND user!="unknown")groupby(user)limit(20)