PowerQuery Examples

|join
public_login = ( EventID in (1149) serverHost contains 'Windows'| columns Source_Network_Address, private_ipv4_address, Computer),

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

|columns source_ip = Source_Network_Address,
first_computer_name = public_login.Computer,
first_computer_ip = public_login.private_ipv4_address,
second_computer_name = lateral_movement.Computer,
second_computer_ip = lateral_movement.private_ipv4_address

parser = 'zia' login matches ".*@.*" reqdatasize = * respdatasize = *  appclass = 'File Share'  !(appname in ('OneDrive'))

| group GB_upload = sum(reqdatasize / (1024*1024*1024)) by login, appname

| sort -GB_upload

| limit 10

parser = 'zia' threatname != 'None' threatname = *

| group count = count() by threatname, host

| sort  -count

parser = 'zia' login matches ".*@.*" reqdatasize = * respdatasize = *  appclass = 'Webmail'  !(appname in ('Outlook (Office 365)' ))

| group GB_upload = sum(reqdatasize / (1024*1024*1024)) by login, appname

| sort -GB_upload

| limit 10

parser = 'zia' refererhost!="None" respcode in ("301","302","403","404","450","502","503","NA") | filter len(url)>150
| group count=count(), statuses=estimate_distinct(respcode), user_count=estimate_distinct(login), referring=estimate_distinct(host), sample_user=any(login), sample_url=any(url), timestamp=any(timestamp) by refererhost
| filter count/user_count > 150 AND referring > 15 AND statuses>4

| inner join 

 all = (parser contains 'zia' ua = * ua != 'Unknown' devicehostname != 'NA' | parse "$ua_os_c{regex=(?:win|andr|linu)}$" from ua | let ua_os=lower(ua_os_c)| filter ua_os matches '(win|andr|linu)'| group count=count(),timestamp=max(timestamp) by cip,devicehostname,location,ua=substr(ua,0,60)),

filter = (parser contains 'zia' ua = * ua != 'Unknown' devicehostname != 'NA' | parse "$ua_os_c{regex=(?:win|andr|linu)}$" from ua | let ua_os=lower(ua_os_c) | filter ua_os matches '(win|andr|linu)' | group count_ua_os =estimate_distinct(ua_os),any(ua_os),any(ua) by cip,devicehostname,login,location | filter count_ua_os>=2 | sort - count_ua_os | columns count_ua_os,cip,devicehostname,login )

on filter.cip=all.cip, filter.devicehostname = all.devicehostname | sort - count_ua_os,cip,devicehostname

parser='zia-fw' action='Allow' 

| group count=count(),out_bytes=sum(outbytes),in_bytes=sum(inbytes),destinations=estimate_distinct(sdip),sample_dest=any(sdip) by login,datacenter,csip

| let out_per_dest=out_bytes/destinations 

| sort -out_per_dest

parser='zia' datacenter=* 

| group dc_users=estimate_distinct(login) by timestamp=timebucket(timestamp,'1hour'),datacenter=substr(datacenter,0,3) 

| transpose datacenter

| union (

logfile matches 'windows_event_log_monitor\.log$' 

| group count=count(),computers=estimate_distinct(Event.System.Computer),sample_log=substr(any(Event.RenderingInfo.Message),0,200) by Event.System.EventID,Event.System.Channel,Event.System.Provider.Name

),

(

logfile matches 'windows_event_log_monitor\.log$' 

| group count=count(),dc=estimate_distinct(Event.System.Computer),sample_log='***Total'

)

Create a burndown of errors (think 99.9% 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.9)/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

(k8s-cluster contains 'nginx')

|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
|sort -average
|limit 100

outcome_reason = 'LOCKED_OUT' (serverHost contains 'okta') !((((((((((((client_geographicalContext_country = 'Bolivia’ || client_geographicalContext_country = ‘Portugal’) || client_geographicalContext_country = 'Belarus') || client_geographicalContext_country = ‘Venezuela’) || client_geographicalContext_country = 'Spain') || client_geographicalContext_country = 'Syria') || client_geographicalContext_country = ‘North Korea’) || client_geographicalContext_country = ‘Cameroon’) || client_geographicalContext_country = 'Bahrain') || client_geographicalContext_country = ‘Mali’)) | group count = count (outcome_reason = 'LOCKED_OUT') by actor_secondaryId | filter count > 0