Example Queries

Use this document to see example queries to use in InsightIDR's Log Search.

Example Log Search Queries

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

• Active Directory Admin Activity
• Asset Authentication
• Asset Authentication, Active Directory Domain Activity, File Access Activity
• Ingress Authentication
• Firewall Activity
• DNS Query
• File Access Activity

For additional example queries on Process Start activity view the Process Start Queries.

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)”)
• where(source_user=”rrn:uba:us:14f8eba8-46c8-474b-a982-29476e7a8bd8:user:JA5G9PI3PC9M”)

For a case-insensitive search, use NOCASE

• where(source_user=NOCASE("arnold holt"))
• where(source_user=NOCASE("tina gonzales (admin)"))
• where(source_user=NOCASE("tina gonzales (admin)")) groupby(action)

Find all users with “admin” in their user name

These return case insensitive results.

• where(source_user ICONTAINS admin)groupby(source_user)
• where(source_user ICONTAINS admin)groupby(action)

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

• where(source_user ICONTAINS admin 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 IN [ MEMBER_ADDED_TO_SECURITY_GROUP, MEMBER_REMOVED_FROM_SECURITY_GROUP] AND group CONTAINS -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)

However, please note that if there is a space in the value of SUCCESS, you can use "SUCCESS" in the query.

Show all failed authentication activity

• where(result STARTS-WITH 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 login - Non Kerberos

• where(service NOT IN [krbtgt, kerberos] AND result=FAILED_BAD_PASSWORD)

Non-Kerberos Login by Dest Asset

• where(service NOT IN [krbtgt, kerberos] AND result="FAILED_BAD_PASSWORD") groupby("destination_asset")

Invalid Logins

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

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

These queries only work with Microsoft Logs.

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 users accessing the network from a list of cities

• where(geoip_city=/Providence|Framingham|Dallas|Minneapolis|Appleton|Phoenix|Omaha|Melbourne|Tuzla|Leeds|Zurich|Singapore|Toronto/i)groupby(geoip_city)

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)

Show countries with successful authentication outside those listed

• where(geoip_country_name AND geoip_country_name!=/United States|Canada|Mexico/i AND result=SUCCESS)groupby(geoip_country_name)limit(100)

Firewall Activity

Show countries that users downloaded data from

• where(incoming_bytes>0 AND geoip_country_code NOT IN [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) This query is useful to see trends in the 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 NOT IN [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 allowlisted countries

• where(geoip_country_name IN [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 NOT IN [ IP(10.0.0.0/8), IP(172.27.0.0/16), IP(169.254.0.0/16), IP(192.168.0.0/16), IP(172.16.0.0/16) ])

Visual Search for Firewalls

• user!="unknown" AND connection_status = DENY AND source_address NOT IN [IP(10.0.0.0/8), IP(172.27.0.0/16), IP(169.254.0.0/16), IP(192.168.0.0/16), IP(172.16.0.0/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 NOT IN [com, net, 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)