Powerqueries are a useful feature in Dataset because they have a simple syntax that is easy to learn, can efficiently generate simple SQL-like summary tables over enormous datasets, and can function via the API.
They can also be set up as a cron job via the PowerQuery monitor and write summaries back to Dataset.
Below are some examples of PowerQueries you can perform. These are meant to function as a skeleton query to help with syntax, the logic, and what is possible for your unique usecase.
| Description | PowerQueries | Systems |
| Authentication Success from 2 or more countries within 1 hour | outcome_result = "SUCCESS" ] country = * !(actor_alternateId matches ("^foo@domain.com$", "^system@okta.com$", "^demo@domain.com$", "^0")) !(actorDisplayName matches ("^svc", "^ops")) !(ipAddress matches ("^22.55.\\d+.", "^192.168.[0-2].\d+")) | group countries=estimate_distinct(geographicalContext_country) by actorAlternateId | filter countries > 1 |
Mimecast |
| Lateral Movement RDP |
|join lateral_movement = (EventID in (1149) serverHost contains 'Windows'| columns Source_Network_Address, private_ipv4_address, Computer) on lateral_movement.Source_Network_Address = public_login.private_ipv4_address
|
Windows |
| Zscaler User Volume by App | serverHost contains 'zscaler' requestsize = * responsesize = * | group total_requests = sum(requestsize), total_responses = sum(responsesize) by user, appname |let total = total_requests + total_responses |sort -total |
Zscaler |
| Event Log Cleared | EventID = 1102 Computer = * | columns timestamp, Computer, Account_Name, Account_Domain, Message |
Windows Event Log |
| Public Ip Login classify by number of attempts. | EventID in (4624,4625) NOT(Source_Network_Address = "-") Source_Network_Address = * NOT(Source_Network_Address = "0.0.0.0" OR Source_Network_Address matches("^10", "^192\\.168", "^172\\.(1[6-9]|2[0-9]|3[01])",", "^::1")) "An account failed to log on" |group attempts = count() by Source_Network_Address, Account_Name |let score = attempts >= 3 AND attempts <= 7 ? "low" : attempts > 7 AND attempts <= 15 ? "medium" : attempts >= 15 ? "high" : "none" |
Windows Event Log |
| Query datain buckets. Get the first and last timestamp and compare changes |
tag="slowFutureExecution"
| group first = min(timestamp), last = max(timestamp) by serverHost | columns serverHost, missingInFirstHour = first > querystart() + 3_600_000_000_000, missingInLastHour = last < queryend() - 3_600_000_000_000 |
Dataset
|
|
Create a burndown of errors (think 99.999% uptime SLA) Define error bucket, success bucket then graph it over time. Gives a solid usecase for measuring service level indicators as a burndown. From a high level, a user would be able to define a few params and would be able to determine how much of their error budget is spent for the time duration set in the dashboard. |
|join a = ( serverHost='Kafka-aa.nginx' |let pass = (payload\.status >= 200 && payload\.status < 300) |let fail = (payload\.status >= 500) |let total_pass_fail = pass + fail |columns payload\.status, status = pass ? "pass" : fail ? "fail" : "indifferent", payload\.request_time, pass, fail, timestamp, total_pass_fail |group error_rate = count(status = "fail")/(count(status)), pass = count(status = "pass"), fail = count(status = "fail"), requests = count(status) by timestamp = timebucket(timestamp, "1d"), key = "1" ), //get number of days in query ( tag = "audit" |group count() by timestamp = timebucket(timestamp, "1d") |group days = count(), key = "1" ) on key |let goal = (100 - 99.0)/100 |let limit = 1 - goal |let daily_limit = goal/days |let error_bucket = running_sum(error_rate) |let daily_budget = (error_rate/daily_limit) * 100 + "%" |let burndown = ( 1 - (error_bucket / goal) ) * 100 |columns timestamp, burndown | Access Logs |
| Authentication Success from 2 or more countries within 1 hour for VIP user | actor_displayName in ("Abc Xyz", "foo") outcome_result = "SUCCESS" client_geographicalContext_country = * !(actor_displayName in ("Okta System", "svc.okta_ad_agent")) | group countries=estimate_distinct(client_geographicalContext_country) by actor_displayName | filter countries > 1 |
Okta |
| Suspicious admin account usage | displayMessage='User accessing Okta admin app'(actor_displayName = 'foo foo' OR actor_displayName = 'Jane Foo') !(client_geographicalContext_country = 'United States' OR client_geographicalContext_country = 'India') serverHost='Okta' | Okta |
| Authentication Success from 3 or more states within 1 hour | outcome_result = "SUCCESS" client_geographicalContext_state = * actor_type = "User" actor_displayName != "Portal" !(actor_displayName matches ("^svc$")) !(actor_alternateId matches ("^system@okta.com$", "^svc")) !(client_ipAddress matches ("^192.168", "^10.10.")) !(debugContext_debugData_url matches ("^\\\\/api", "\\\\/oauth2")) | group states=estimate_distinct(client_geographicalContext_state) by actor_alternateId | filter states > 2 |
Okta |
| Authentication Success from 4 or more IPs within 1 hour | outcome_result = \"SUCCESS\" AND client_ipAddress = * AND actor_type = \"User\" AND NOT(client_ipAddress contains ( '192.168.')) AND NOT(actor_displayName contains 'svc' ) AND NOT(actor_alternateId contains 'svc') AND NOT(debugContext_debugData_url contains '/api') AND NOT(debugContext_debugData_url contains '/api') AND NOT('/oauth2') | group count = estimate_distinct(client_ipAddress) by actor_alternateId | filter count >= 4 |
Okta |
| Bruteforce | outcome_result = "FAILURE" outcome_reason = "LOCKED_OUT" | Okta |
| In this query, we are grouping all searches and counting by users to get the number of searches a particular user has done. |
(tag='audit' queryGroupRequest.queries contains '"origin":"SEARCH"' action='launchQuery') OR //Search
(tag='audit' action='launchQuery' queryGroupRequest.queries contains 'type":"PLOT' not (queryGroupRequest.queries contains 'origin":"SEARCH')) //Graph | group searches = count() by user |
Dataset Audit
|
| In this query, we are grouping on searches and counting by users to get the number of searches a particular user has done. In order to ensure we are pulling all log searches, we are doing a filter where queryGroupRequest.queries contain Origin and Search. | tag='audit' queryGroupRequest.queries contains '"origin":"SEARCH"' action='launchQuery' | group searches = count() by user |
Dataset Audit |
| In this query, we are grouping on searches and counting by users where the queryGroupRequest contains the type of PQ to bring back a count of the Power Query searched by a specific user. | tag='audit' action='launchQuery' queryGroupRequest.queries contains 'type":"PQ' | group searches = count() by user |
Dataset Audit |
| In this query, we are grouping and counting the users that are currently logged in. We are pulling in the user field to show this. | action= * user = * (serverHost contains 'meta' || k8s-cluster contains 'meta') |group count() by user | columns user |
Dataset Audit |
| In this query, we are grouping so we can count the number of times a dashboard has been loaded by a specific user. We are sorting by timestamp. | tag='audit' action='getDashboard' | sort timestamp | group loads = count(), timestamp = last(timestamp) by user, dashboardName | sort -timestamp | columns user, dashboardName, loads, timestamp |
Dataset Audit |
| In this query, we are querying on the last 50 searches, we are using the parse command to extract information contained in the filter and show what was searched by. |
(tag='audit' action='launchQuery' queryGroupRequest.queries contains 'type":"PQ') OR //PQ
(tag='audit' queryGroupRequest.queries contains '"origin":"SEARCH"' action='launchQuery') OR //Search (tag='audit' action='launchQuery' queryGroupRequest.queries contains 'type":"PLOT' not (queryGroupRequest.queries contains 'origin":"SEARCH')) //Graph |parse "\"filter\":\"$filter$\"," from queryGroupRequest.queries | columns user, filter, timestamp |sort -timestamp |limit 50 |
Dataset Audit
|
| In this query, we are showing the columns for when an alert was created. | tag='audit' action='createAlert' | columns alert.alertAddresses , alert.description , alert.gracePeriod , alert.renotifyPeriod ,alert.trigger |
Dataset Audit |
| In this query we are sorting by timestamp, we are also showing the columns timestamp, laststate, trigger and description to show the last state of an alert along with a description of that alert. (note the use of ternary operator) | tag contains 'alert' lastState= * |sort -timestamp | columns description, trigger , lastState = lastState = 1 ? "Not Triggered" : "Triggered" , timestamp | group timestamp = first(timestamp), description = any(description) by lastState, trigger |sort -timestamp | columns timestamp, lastState, trigger, description |
Dataset Audit |
| In this query, we are the Union Command in order to execute two subqueries to show the total number of alerts triggered over a set period of time. | |union ( tag contains 'alert' lastState= * |sort -timestamp | columns description, trigger , lastState = lastState = 1 ? "Not Triggered" : "Triggered" , timestamp | group timestamp = first(timestamp), description = any(description) by lastState, trigger |sort -timestamp | columns timestamp, lastState, trigger, description |filter lastState = "Triggered" ), ( tag contains 'alert' lastState= * |sort -timestamp | columns description, trigger , lastState = lastState = 1 ? "Not Triggered" : "Triggered" , timestamp | group timestamp = first(timestamp), description = any(description) by lastState, trigger |sort -timestamp | columns timestamp, lastState, trigger, description |filter lastState = "Triggered" |group value = count(lastState) |columns lastState = value, timestamp = "Sum of Triggered Alerts" ) |
Dataset Audit |
| Latencies p(%n) |
k8s-cluster contains 'nginx') \n|group average = average(payload.request_time), p10(payload.request_time), pct(25, payload.request_time), p50(payload.request_time), pct(75, payload.request_time), p95(payload.request_time), pct(99, payload.request_time) by payload.pathname\n|sort -average\n|limit 100
|
Nginx
|
| Invocations by user |
event_name=* (serverHost contains 'model' || k8s-cluster contains 'model') | group count=count() by metadata.http_headers.Owner-ID | sort -count | sort -count
|
Nginx
|
| Api Functions By Usage |
event_name=* (serverHost contains 'model' || k8s-cluster contains 'model-envelope-usage') | group count=count() by event_name | sort -count
|
Nginx
|
| Stacked bar of downstream errors |
serverHost = 'nginx' payload.status != 200 payload.http_user_agent = * | group total = count() by payload.host, timestamp=timebucket('auto') | transpose payload.host
|
Nginx
|
| Authentication Success from 2 or more countries within 1 hour for VIP user |
(serverHost contains 'okta' || k8s-cluster contains 'okta') client_geographicalContext_country= * outcome_result = 'SUCCESS' actor_type = 'User' actor_displayName = 'Foo Bar' OR actor_displayName = 'Jane Var' | filter client_ipAddress != '66.44.22.111'|group count = estimate_distinct(client_geographicalContext_country) by actor_displayName, actor_alternateId\n|filter count >2
|
Okta
|
| Authentication Success from 4 or more IPs within 1 hour |
outcome_result = \"SUCCESS\" AND client_ipAddress = * AND actor_type = \"User\" AND NOT(actor_alternateId contains 'svc') AND NOT(debugContext_debugData_url contains '/api') AND NOT(debugContext_debugData_url contains '/api') AND NOT('/oauth2')\n| filter client_ipAddress != '11.11.111.111' |group count = estimate_distinct(client_ipAddress) by actor_alternateId |filter count >= 4
|
OKta
|
| OKTA Bruteforce |
(serverHost contains 'okta' || k8s-cluster contains 'okta')\noutcome_reason = 'LOCKED_OUT' outcome_result = 'FAILURE' \n\n| group count = count (outcome_result = 'FAILURE') by actor_alternateId\n| filter count > 200\n!(actor_alternateId = 'it@domain.com' )
|
Okta
|
| OKTA User Locked out from Non-domain foreign country |
outcome_reason = 'LOCKED_OUT' (serverHost contains 'okta') !((((((((((((client_geographicalContext_country = 'Brazil' || client_geographicalContext_country = 'Canada') || client_geographicalContext_country = 'Finland') || client_geographicalContext_country = 'Germany') || client_geographicalContext_country = 'India') || client_geographicalContext_country = 'Spain') || client_geographicalContext_country = 'United Arab Emirates') || client_geographicalContext_country = 'United Kingdom') || client_geographicalContext_country = 'United States') || client_geographicalContext_country = 'Bahrain') || client_geographicalContext_country = 'Ireland') || client_geographicalContext_country = 'Oman')) serverHost='Okta'\n\n| group count = count (outcome_reason = 'LOCKED_OUT') by actor_alternateId\n| filter count > 0
|
Okta
|
| AD attempt to install a service |
(serverHost contains 'Active Directory')\nEVENT_NUMBER = 4697 \n!(FORMAT_MESSAGE contains 'security_tool_a' OR FORMAT_MESSAGE contains 'Rapid' OR FORMAT_MESSAGE contains 'MpKs' OR FORMAT_MESSAGE contains 'CS' OR FORMAT_MESSAGE contains 'Cs' OR FORMAT_MESSAGE contains 'CDPUserSvc' OR FORMAT_MESSAGE contains 'OneSyncSvc' OR FORMAT_MESSAGE contains 'PimIndexMaintenanceSvc' OR FORMAT_MESSAGE contains 'UnistoreSvc' OR FORMAT_MESSAGE contains 'UserDataSvc' OR FORMAT_MESSAGE contains 'WpnUserService')\n| group count= count(EVENT_NUMBER = 4697) by FORMAT_MESSAGE, CALLER_USER_NAME\n| filter count >=1
|
Active Directory
|
| AD Disabled Account Login Attempts |
(serverHost contains 'Active Directory' || k8s-cluster contains 'Active Directory')\nERROR_CODE_TEXT = 'Account disabled, expired, or locked out' EVENT_NUMBER = 4768\n| group count= count(EVENT_NUMBER = 4768) by USERNAME\n| filter count >=1
|
Active Directory
|
| AD Suspicious Failed Logins |
(serverHost contains 'Active Directory' || k8s-cluster contains 'Active Directory')\nEVENT_NUMBER = 4625\n| group count= count(EVENT_NUMBER = 4768) by USERNAME, CLIENT_HOST_NAME\n| filter count >=1
|
Active Directory
|
| AD Use of Terminated Account |
(serverHost contains 'Active Directory' || k8s-cluster contains 'Active Directory')\nEVENT_TYPE_TEXT = 'Success' AND ATTRIBUTES_NEW_VALUE matches 'OU=Processed, OU=US Terminated Users, OU=Terminated Users, DC=corp, DC=domain, DC=com'\n| group count= count(EVENT_TYPE_TEXT = 'Success') by USERNAME, CLIENT_HOST_NAME\n| filter count >=1
|
Active Directory
|
| AD user account Created/Enabled by the user without Admin rights (1/15) |
(serverHost contains 'Active Directory' || k8s-cluster contains 'Active Directory')\nEVENT_NUMBER = 4722 !(CALLER_USER_NAME matches 'ad' OR CALLER_USER_NAME = 'DomJoin' OR CALLER_USER_NAME = 'domainexchange' OR CALLER_USER_NAME = 'svc.cloner')\n| group count= count(EVENT_NUMBER = 4722) by ACCOUNT_DISPLAY_NAME, CALLER_DISPLAY_NAME, FORMAT_MESSAGE\n| filter count >=1
|
Active Directory
|
| AD Admin Account created |
query: "(serverHost contains 'Active Directory' || k8s-cluster contains 'Active Directory')\nEVENT_NUMBER = 4720 USERNAME matches 'ad.*'\n| group count= count(EVENT_NUMBER = 4720) by ACCOUNT_DISPLAY_NAME, CALLER_DISPLAY_NAME, FORMAT_MESSAGE\n| filter count >=1",
|
Active Directory
|
Comments
0 comments
Article is closed for comments.