not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(Tactic, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| timechart(span=1h, function=count())
| rename(_count, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| sankey(target=Technique, source=SeverityName)

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(SeverityName)

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(Technique, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(DetectDescription, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| sankey(target=ParentImageFileName, source=GrandparentImageFileName)

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| sankey(target=FileName, source=ParentImageFileName)

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| sankey(target=Tactic, source=Technique)

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| table([ComputerName, UserName, DetectName, FilePath, FileName, SeverityName, Tactic, Technique, DetectDescription])

not "#event_simpleName" = *
| EventType = "Event_ExternalApiEvent"
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(ComputerName, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(UserName, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(Tactic, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| timechart(span=1h, function=count())
| rename(_count, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| sankey(target=Technique, source=SeverityName)

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(SeverityName)

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(Technique, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(DetectDescription, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| sankey(target=ParentImageFileName, source=GrandparentImageFileName)

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| sankey(target=FileName, source=ParentImageFileName)

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| sankey(target=Tactic, source=Technique)

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| table([ComputerName, UserName, DetectName, FilePath, FileName, SeverityName, Tactic, Technique, DetectDescription])

not "#event_simpleName" = *
| EventType = "Event_ExternalApiEvent"
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(ComputerName, as="#events")

not "#event_simpleName" = *
| ExternalApiType = "Event_DetectionSummaryEvent"
| ComputerName = ?ComputerName AND AgentIdString = ?aid AND CustomerIdString = ?cid
| top(UserName, as="#events")

#kind = "Secondary" SecondaryEventType = "appinfo"
| CompanyName = ?CompanyName | ProductName = ?ProductName
| top([ProductVersion], as="count")

#kind = "Secondary" SecondaryEventType = "appinfo"
| CompanyName = ?CompanyName | ProductName = ?ProductName
| top([ProductName], as="count")

#kind = "Secondary" SecondaryEventType = "appinfo"
| CompanyName = ?CompanyName | ProductName = ?ProductName
| groupBy([CompanyName, FileDescription, FileName, FileVersion, ProductName, ProductVersion], limit=max)
| rename(_count, as="count")
| sort([count,CompanyName, ProductName, FileVersion], order=desc)

#kind = "Secondary" SecondaryEventType = "appinfo"
| CompanyName = ?CompanyName
| top([CompanyName], as="count")

/*
 * This query gives a summary of the domain(s) ('DomainName'
 * parameterized by '?DomainName')
 * It can also give a summary of all the hosts that a specific
 */
#event_simpleName="DnsRequest"
| DomainName = ?DomainName
| match(field=aid, file="fdr_aidmaster.csv", column=aid, include=ComputerName, strict=false)
| groupBy(DomainName, function=[
    { selectLast([ContextTimeStamp, ComputerName]) | rename(ContextTimeStamp, as=LastLookup) | rename(ComputerName, as=LastLookupBy) },
    { select([ContextTimeStamp, ComputerName]) | head(1) | rename(ContextTimeStamp, as=FirstLookup) | rename(ComputerName, as=FirstLookupBy) | drop(@timestamp) },
    { count(ComputerName, as=NumberOfHosts, distinct=true) }
  ], limit=max)
| LastLookup := LastLookup * 1000
| FirstLookup := FirstLookup * 1000
| LastLookup := formatTime(field=LastLookup, format="%Y/%m/%d %H:%M:%S")
| FirstLookup := formatTime(field=FirstLookup, format="%Y/%m/%d %H:%M:%S")
| table([DomainName, NumberOfHosts, FirstLookup, FirstLookupBy, LastLookup, LastLookupBy], limit=200, sortby=NumberOfHosts)
| rename(NumberOfHosts, as="#hosts")

/*
 * This query provices details of specific processes that resolve a
 * specific domain ('DomainName', parameterized by '?DomainName') and that are
 * run from a specific agent ID ('aid', parameterized '?aid') or ComputerName (?ComputerName).
 * This query will only return results if either ?DomainName and ?aid / ?ComputerName is specified.
 */
#event_simpleName="ProcessRollup2" OR #event_simpleName="DnsRequest"
| test(?DomainName != "*")
| aid=?aid
| case {
    test(?aid == "*") | test(?ComputerName == "*") | runQuery:=false;
    * | runQuery:=true;
}
| test(runQuery == true)
| drop(runQuery)
// We wish to join the ContextProcessId of the DnsRequest events to the
// TargetProcessId of the ProcessRollup2 events, so we collect them both
// in a field 'pid' that we can then group on.
| #event_simpleName match {
  "ProcessRollup2" => pid := TargetProcessId;
  "DnsRequest" => pid := ContextProcessId;
}
// Group the agent IDs, and for each group group by the 'pid' field.
| match(field=aid, file="fdr_aidmaster.csv", column=aid, include=ComputerName)
| groupBy("pid", function=
  [
    // Extract the latests ProcessStartTime, ContextProcessId, ImageFileName, MD5HashData, and SHA256HashData fields.
    { #event_simpleName="ProcessRollup2" | selectLast([aid, ComputerName, ProcessStartTime, ContextProcessId, ImageFileName, MD5HashData, SHA256HashData]) },
    // Extract the DomainName for each 'pid'.
    { #event_simpleName="DnsRequest" AND DomainName=?DomainName | groupBy(DomainName) | drop(["_count"]) }
  ], limit=max)
// Remove entries that have no domain name or process start time.
| DomainName = * AND ProcessStartTime = *
| ComputerName = ?ComputerName
// Make the process start time human-readable.
| ProcessStartTime := ProcessStartTime*1000
| ProcessStartTime := formatTime(field=ProcessStartTime, format="%Y/%m/%d %H:%M:%S")
// Present the results.
| table([ProcessStartTime, pid, DomainName, ComputerName, aid, ImageFileName, MD5HashData, SHA256HashData], limit=200)

/**
 * This counts the number of distinct hosts ('ComputerName') that
 * resolved a specific domain ('DomainName', parametrized by '?DomainName').
 * This query will only return results if ?DomainName is specified.
 */
#event_simpleName=DnsRequest
| test(?DomainName != "*")
| aid=?aid
| match(field=aid, file="fdr_aidmaster.csv", column=aid, include=ComputerName, strict=false)
| ComputerName=?ComputerName
| count(ComputerName, distinct=true)

/**
 * This lists the distinct hosts ('ComputerName') that resolve a specific
 * domain ('DomainName', parametrized by '?DomainName') and how many requests
 * they made.
 */
#event_simpleName=DnsRequest
| match(file="fdr_aidmaster.csv", field=aid, column=aid, include=ComputerName, strict=true)
| DomainName = ?DomainName
| groupBy([ComputerName, DomainName], limit=max)
| DomainName = *
| top(ComputerName, sum=_count, as="# lookups")

/**
 * This lists the distinct hosts ('ComputerName') that resolve a specific
 * domain ('DomainName', parametrized by '?DomainName') and how many requests
 * they made.
 */
#event_simpleName="DnsRequest"
| match(file="fdr_aidmaster.csv", field=aid, column=aid, include=ComputerName, strict=true)
| DomainName=?DomainName
| groupBy([ComputerName, DomainName], limit=max)
| DomainName=*
| top(ComputerName, sum=_count, as="#lookups")

| #event_simpleName=DnsRequest
| test(?DomainName != "*")
| DomainName = ?DomainName
| match(field=aid, file="fdr_aidmaster.csv", column=aid, include=ComputerName)
| ComputerName = ?ComputerName
| groupBy([DomainName, ComputerName], function=[
  { count(ContextTimeStamp, as="#lookups", distinct=true) },
  { selectLast(ContextTimeStamp) | rename(ContextTimeStamp, as=LastLookup) },
  { select(ContextTimeStamp) | head(1) | rename(ContextTimeStamp, as=FirstLookup) }
])
| sort(ComputerName, limit=20000)
| LastLookup := LastLookup*1000
| FirstLookup := FirstLookup*1000
| LastLookup := formatTime(field=LastLookup, format="%Y/%m/%d %H:%M:%S")
| FirstLookup := formatTime(field=FirstLookup, format="%Y/%m/%d %H:%M:%S")
| table([DomainName, "#lookups", ComputerName, FirstLookup, LastLookup], limit=199, sortby="#lookups")

"#event_simpleName"=FileIntegrityMonitorRuleMatched AND event_platform=?Platform
| top(UserName, as="#alerts")

"#event_simpleName"=FileIntegrityMonitorRuleMatched AND event_platform=?Platform
| case {
  ObjectType=1 | ObjectChanged:= "File";
  ObjectType=2 | ObjectChanged:= "Folder";
  ObjectType=3 | ObjectChanged:= "Value";
  ObjectType=4 | ObjectChanged:= "Key";}
| case {
  ObjectAccessOperationType = 1 | ObjectOperation:= "Create";
  ObjectAccessOperationType = 2 | ObjectOperation:= "Write";
  ObjectAccessOperationType = 3 | ObjectOperation:= "Delete";
  ObjectAccessOperationType = 4 | ObjectOperation:= "Set";
  ObjectAccessOperationType = 5 | ObjectOperation:= "Rename";}
| table([@timestamp,PolicyRuleSeverity, aid, UID, UserName, CommandLine, ImageFileName, ObjectChanged, ObjectOperation, ObjectName, ObjectNameNew])

"#event_simpleName"=FileIntegrityMonitorRuleMatched AND event_platform=?Platform
| case {
  PolicyRuleSeverity=1 | Severity := "Low";
  PolicyRuleSeverity=2 | Severity := "Medium";
  PolicyRuleSeverity=3 | Severity := "High";
  PolicyRuleSeverity=4 | Severity := "Critical";
}
| groupBy(Severity)

"#event_simpleName"=FileIntegrityMonitorRuleMatched AND event_platform=?Platform
| timechart(span=1h, function=count())
| rename(_count, as="#alerts")

"#event_simpleName"=FileIntegrityMonitorRuleMatched AND event_platform=?Platform
| case {
  ObjectAccessOperationType = 1 | ObjectOperation:= "Create";
  ObjectAccessOperationType = 2 | ObjectOperation:= "Write";
  ObjectAccessOperationType = 3 | ObjectOperation:= "Delete";
  ObjectAccessOperationType = 4 | ObjectOperation:= "Set";
  ObjectAccessOperationType = 5 | ObjectOperation:= "Rename";}
| groupBy(ObjectOperation)
| rename(_count, as="#alerts")

"#event_simpleName"=FileIntegrityMonitorRuleMatched
| groupBy(event_platform)

"#event_simpleName"=FileIntegrityMonitorRuleMatched AND event_platform=?Platform
| ImageFileName =  /(\/|\\)(?<FileName>\w*\.?\w*)$/
| top(FileName, as="#alerts")

"#event_simpleName"=FileIntegrityMonitorRuleMatched AND event_platform=?Platform
| case {
  ObjectType=1 | ObjectChanged:= "File";
  ObjectType=2 | ObjectChanged:= "Folder";
  ObjectType=3 | ObjectChanged:= "Value";
  ObjectType=4 | ObjectChanged:= "Key";}
| groupBy(ObjectChanged)
| rename(_count, as="#alerts")

// This query will only return results if at least one of ?FileName or ?SHA256 is specified.
case {
    test(?FileName == "*") | test(?SHA256 == "*") | runQuery:=false;
    * | runQuery:=true;
}
| test(runQuery == true)
| drop(runQuery)
| #event_simpleName="ProcessRollup2" AND SHA256HashData = ?SHA256
| regex(field=ImageFileName, regex="(?<FileName>[^/\\\]+$)")
| FileName = ?FileName
| table([@timestamp, aid, ImageFileName, CommandLine], limit=20000)

#event_simpleName="ProcessRollup2" AND SHA256HashData = ?SHA256
| regex(field=ImageFileName, regex="(?<FileName>[^/\\\]+$)")
| FileName = ?FileName
| match(file="fdr_aidmaster.csv", field=aid, column=aid, include=ComputerName, strict=true)
| top(field=ComputerName,limit=25)

#event_simpleName="*FileWritten" AND SHA256HashData = ?SHA256
| regex(field=TargetFileName, regex="(?<FileName>[^/\\\]+$)")
| FileName = ?FileName
| match(file="fdr_aidmaster.csv", field=aid, column=aid, include=ComputerName, strict=true)
| top(field=ComputerName,limit=25)

#event_simpleName="*FileWritten" AND SHA256HashData = ?SHA256
| test(?ComputerName != "*")
| match(field=aid, file="fdr_aidmaster.csv", column=aid, include=ComputerName, strict=false)
| ComputerName = ?ComputerName
| regex(field=TargetFileName, regex="(?<FileName>[^/\\\]+$)")
| FileName = ?FileName
| table([@timestamp, aid, ComputerName, TargetFileName], limit=200)

#event_simpleName="*FileWritten" AND SHA256HashData = ?SHA256
| match(field=aid, file="fdr_aidmaster.csv", column=aid, include=ComputerName, strict=false)
| regex(field=TargetFileName, regex="(?<FileName>[^/\\\]+$)")
| FileName = ?FileName
| count(field=ComputerName,distinct=true)

#event_simpleName = "ProcessRollup2" AND SHA256HashData = ?SHA256
| regex(field=ImageFileName, regex="(?<FileName>[^/\\\]+$)")
| FileName = ?FileName
| timeChart(function=count(as="Activity"))

#event_simpleName=ProcessRollup2 AND SHA256HashData = ?SHA256
// This query will only return results if at least one of ?FileName or ?SHA256 is specified.
| case {
    test(?FileName == "*") | test(?SHA256 == "*") | runQuery:=false;
    * | runQuery:=true;
  }
| test(runQuery == true)
| drop(runQuery)
| regex(field=ImageFileName, regex="(?<FileName>[^/\\\]+$)")
| FileName = ?FileName
| groupby([SHA256HashData, FileName], function=[
    count(aid, as="#hosts", distinct=true),
    { selectLast([ProcessStartTime, aid]) | rename(ProcessStartTime, as=LastExecutionDateTime) | rename(aid, as=LastExecutedOn) },
    { head(1) | select([ProcessStartTime, aid]) | rename(ProcessStartTime, as=FirstExecutionDateTime) | rename(aid, as=FirstExecutedOn) }
], limit=max)
| LastExecutionDateTime := LastExecutionDateTime*1000
| FirstExecutionDateTime := FirstExecutionDateTime*1000
| LastExecutionDateTime := formatTime(field=LastExecutionDateTime, format="%Y/%m/%d %H:%M:%S")
| FirstExecutionDateTime := formatTime(field=FirstExecutionDateTime, format="%Y/%m/%d %H:%M:%S")
| drop(@timestamp)
| table([SHA256HashData, FileName, "#hosts", FirstExecutedOn, FirstExecutionDateTime, LastExecutedOn, LastExecutionDateTime], limit=199, sortby="#hosts")

#event_simpleName="ProcessRollup2" AND SHA256HashData = ?SHA256
| match(field=aid, file="fdr_aidmaster.csv", column=aid, include=ComputerName, strict=true)
| regex(field=ImageFileName, regex="(?<FileName>[^/\\\]+$)")
| FileName = ?FileName
| count(field=ComputerName, distinct=true)

#event_simpleName = "ProcessRollup2" AND SHA256HashData = ?SHA256
| regex(field=ImageFileName, regex="(?<FileName>[^/\\\]+$)")
| FileName = ?FileName
| timeChart(function=count(as="Hosts", field=aid, distinct=true))

#event_simpleName=ServiceStarted AND event_platform=Win
| test(?aid != "*")
| aid=?aid AND #cid = ?cid
| table([@timestamp, UserName, ServiceDisplayName, CommandLine], limit=200)

#event_simpleName=NetworkConnectIP4
| test(?aid != "*")
| aid=?aid AND #cid = ?cid
| worldMap(ip=RemoteAddressIP4)

#event_simpleName=ProcessRollup2
| test(?aid != "*")
| aid=?aid AND #cid = ?cid
| sankey(source=ParentBaseFileName, target=ImageFileName)

#event_simpleName=ProcessRollup2 OR #event_simpleName=NetworkListenIP4
| aid=?aid
| test(?aid != "*")
// We essentially want to join the ContextProcessId of the network events
// with the TargetProcessId of the ProcessEvents, so we create a new
// field 'pid' on which we can do a groupby.
| #event_simpleName match {
  ProcessRollup2 => pid := TargetProcessId;
  NetworkListenIP4 => pid := ContextProcessId;
}
// Group by the agent ID and for each group, group by the 'pid' field.
| groupBy("aid", function=[
  { groupBy("pid", function=[
    // Extract the ImageFileName field.
    { #event_simpleName="ProcessRollup2" | selectLast([ImageFileName]) },
    // Extract up to 200 rows of the LocalAddressIP4 and LocalPort
    // fields.
    { #event_simpleName="NetworkListenIP4" | table([LocalAddressIP4, LocalPort]) }
  ], limit=max)
  }
], limit=max)
| match(field=aid, file="fdr_aidmaster.csv", column=aid, include=ComputerName, strict=true)
// Filter off results that do not have the below fields.
| LocalAddressIP4=* AND LocalPort=* AND ImageFileName=*
// Present the results.
      | table([aid, ComputerName, ImageFileName, LocalAddressIP4, LocalPort], limit=200)

#event_simpleName=DnsRequest
| test(?aid != "*")
| aid=?aid AND #cid = ?cid
| top(DomainName)
| rename(_count, as="#requests")

EventType=Event_ExternalApiEvent
| test(?aid != "*")
| AgentIdString = ?aid #cid = ?cid
| sankey(target=Tactic, source=Technique)

#event_simpleName=UserLogon
| test(?aid != "*")
| aid=?aid AND #cid = ?cid
| case { LogonType="2" | Logon_Type:="Interactive"; LogonType="3" | Logon_Type:="Network";
LogonType="4" | Logon_Type:="Batch"; LogonType="5" | Logon_Type:="Service";
LogonType="6" | Logon_Type:="Proxy"; LogonType="7" | Logon_Type:="Unlock";
LogonType="8" | Logon_Type:="Network clear text"; LogonType="9" | Logon_Type:="New Credentials";
LogonType="10" | Logon_Type:="RDP"; LogonType="11" | Logon_Type:="Cached Credentials";
LogonType="12" | Logon_Type:="Auditing"; LogonType="13" | Logon_Type:="Unlock Workstation";*}
| table([@timestamp, UserName, LogonDomain, Logon_Type, UserIsAdmin], limit=200)

#event_simpleName=ScheduledTaskRegistered
| test(?aid != "*")
| aid=?aid AND #cid = ?cid
| table([UserName, TaskName, TaskExecCommand, TaskExecArguments])

#event_simpleName=NetworkConnectIP4
| test(?aid != "*")
| aid=?aid AND #cid = ?cid
| !regex("^(10|172\.16|192\.168)\.", field=RemoteAddressIP4)
| ipLocation(field=RemoteAddressIP4, as=RemoteAddressIP4)
| top([RemoteAddressIP4, RemoteAddressIP4.country])
| rename(_count, as="#connections")

ExternalApiType=Event_DetectionSummaryEvent
| test(?aid != "*")
| AgentIdString = ?aid AND CustomerIdString = ?cid
| groupby([UserName, FileName, DetectName, DetectDescription, SeverityName], function=[count(as=DetectionCount)], limit=max)

#event_simpleName=PackedExecutableWritten
| test(?aid != "*")
| #cid = ?cid AND aid=?aid
| count()

#event_simpleName=PeFileWritten
| test(?aid != "*")
| aid=?aid AND #cid = ?cid
| count(SHA256HashData, distinct=true)

#event_simpleName=ProcessRollup2
| test(?aid != "*")
| aid=?aid AND #cid = ?cid
| regex(field=ImageFileName, regex="(?<FileName>[^/\\\]+$)")
| table([@timestamp, FileName, CommandLine, ParentBaseFileName], limit=200)

#event_simpleName=AgentOnline
| test(?aid != "*")
| aid=?aid AND #cid = ?cid
| ipLocation(field=aip, as=aip)
| groupby([ComputerName, AgentVersion, aip, aip.country, aip.city, BiosManufacturer, BiosVersion, ChassisManufacturer, SystemManufacturer, SystemProductName, aid], limit=max)
| drop(_count)

#event_simpleName=NetworkConnectIP4
| not cidr(RemoteAddressIP4, subnet=["192.168.0.0/16", "176.16.0.0/20", "10.0.0.0/24"])
| aid =~ match(file="fdr_aidmaster.csv", column=aid, include=ComputerName, strict=false)
| groupBy(RemoteAddressIP4, function=[
  { selectLast([ContextTimeStamp]) | rename(ContextTimeStamp, as=LastLookup) },
  { select([ContextTimeStamp]) | head(1) | rename(ContextTimeStamp, as=FirstLookup) | drop(@timestamp) },
  { count(ComputerName, as=NumberOfHosts, distinct=true) }
  ], limit=max)
| sort(NumberOfHosts, order=descending, limit=20000)
// Convert the time fields to human readable format
| LastLookup := LastLookup*1000
| FirstLookup := FirstLookup*1000
| LastLookup := formatTime(field=LastLookup, format="%Y/%m/%d %H:%M:%S")
| FirstLookup := formatTime(field=FirstLookup, format="%Y/%m/%d %H:%M:%S")
| table([RemoteAddressIP4, NumberOfHosts, FirstLookup, LastLookup], limit=199)

/*
 * This query finds the IP Connections for a given host
 */
| #event_simpleName=NetworkConnectIP4
| RemoteAddressIP4=?IP
| match("fdr_aidmaster.csv",field="aid", column="aid", include=ComputerName, strict=true)
| ComputerName=?ComputerName
| groupby([ComputerName, RemoteAddressIP4], limit=max)
| top(ComputerName, sum=_count)
| rename(_sum, as="#connections")

/*
 * This query finds the IP Connections for a given host
 */
#event_simpleName=NetworkConnectIP4
| RemoteAddressIP4=?IP
| match("fdr_aidmaster.csv",field="aid", column="aid", include=ComputerName, strict=true)
| ComputerName=?ComputerName
| groupBy([ComputerName, RemoteAddressIP4], limit=max)
| top(ComputerName, sum=_count)

#event_simpleName=ProcessRollup2
| ImageFileName=*
| aid=*
| join(max=10000, field=[TargetProcessId], key=ContextProcessId, include=[_count], mode=inner, query={
    #event_simpleName=NetworkConnectIP4
  | groupBy([RemoteAddressIP4], function=[
    { count(RemoteAddressIP4, distinct=true) | _count > 0 },
    { selectLast(ContextProcessId) }
  ], limit=max)
})
| groupby([ImageFileName], function=[sum(_count, as="#ips"), { tail(1) | selectLast(aid) }], limit=max)
| "#ips" > 0
| sort("#ips", order=descending, limit=100)
| table([ImageFileName, aid, "#ips"])

| test(?IP != "*")
| #event_simpleName=NetworkConnectIP4
| RemoteAddressIP4 = ?IP
| match(field=aid, file="fdr_aidmaster.csv", column=aid, include=ComputerName)
| groupBy([RemoteAddressIP4, ComputerName], function=[
  { count(ContextTimeStamp, as="#lookups", distinct=true) },
  { selectLast(ContextTimeStamp) | rename(ContextTimeStamp, as=LastLookup) },
  { select(ContextTimeStamp) | head(1) | rename(ContextTimeStamp, as=FirstLookup) }
], limit=max)
| sort(ComputerName, limit=20000)
| LastLookup := LastLookup*1000
| FirstLookup := FirstLookup*1000
| LastLookup := formatTime(field=LastLookup, format="%Y/%m/%d %H:%M:%S")
| FirstLookup := formatTime(field=FirstLookup, format="%Y/%m/%d %H:%M:%S")
| table([RemoteAddressIP4, ComputerName, FirstLookup, LastLookup, "#lookups"], limit=199)

#event_simpleName="AgentOnline"
| worldMap(ip=aip)

"#event_simpleName"=AgentOnline
| timechart(span=6h,function=count(field=aid,distinct=true))
| rename(_count, as="#hosts")

#kind=Secondary | SecondaryEventType=aidmaster
| GroupBy(event_platform, function=count(field=aid, distinct=true), limit=max)

#kind=Secondary | SecondaryEventType=aidmaster
| GroupBy(event_platform, function=count(field=aid, distinct=true, as="#hosts"))

test(?ProcessId != "*")
| (#event_simpleName=NetworkConnectIP4 OR #event_simpleName=NetworkConnectIP6) ContextProcessId = ?ProcessId aid = ?aid
| case {
Protocol=6 | eval(aProtocol="TCP");
Protocol=1 | eval(aProtocol="ICMP");
Protocol=17 | eval(aProtocol="UDP");
Protocol=58 | eval(aProtocol="IPv6-ICMP");*}
| table([@timestamp, aid, aProtocol, LocalAddressIP4, LocalAddressIP6, LocalPort, RemoteAddressIP4, RemoteAddressIP6, RemotePort])

test(?ProcessId != "*")
| ContextProcessId = ?ProcessId
| groupBy([aid, #event_simpleName])

test(?ProcessId != "*")
| #event_simpleName=*Written ContextProcessId = ?ProcessId aid = ?aid
| groupBy([#event_simpleName, TargetFileName])
| rename(_count, as="#")

test(?FileName != "*")
| #event_simpleName=ProcessRollup2 aid=?aid TargetProcessId = ?ProcessId
| ImageFileName =  /(\/|\\)(?<FileName>\w*\.?\w*)$/i
| FileName = ?FileName
| rename(field=ImageFileName, as=OriginalFileName) | rename(field=CommandLine, as=OriginalCommandLine)
| groupBy([aid, TargetProcessId, OriginalFileName, OriginalCommandLine])
| sort(_count, order=asc)
| rename(_count, as="#")

test(?ProcessId != "*")
| #event_simpleName=FileDeleteInfo ContextProcessId = ?ProcessId aid = ?aid
| groupBy([#event_simpleName, TargetFileName])
| rename(_count, as="#")

(#event_simpleName=NetworkConnectIP4 OR #event_simpleName=NetworkConnectIP6) ContextProcessId = ?ProcessId aid = ?aid
| test(?ProcessId != "*")
| groupBy([RemoteAddressIP4, RemoteAddressIP6])

test(?ProcessId != "*")
| #event_simpleName=DnsRequest ContextProcessId = ?ProcessId aid = ?aid
| groupBy([DomainName])
| rename(_count, as="#requests")

test(?ProcessId != "*")
| ContextProcessId = ?ProcessId aid = ?aid

test(?ProcessId != "*")
| #event_simpleName=ImageHash ContextProcessId = ?ProcessId aid = ?aid
| groupBy([ImageFileName])
| rename(_count, as="#events")

// Looking for random generated keys
// CSIT-21052 - QakBot
#event_simpleName = "AsepValueUpdate"
| RegObjectName = "*\Software\Microsoft\Windows\CurrentVersion\Run"
| RegStringValue =~ /[a-z0-9]{6,10}/
| groupBy(field=[RegStringValue,aid], limit=max)
| _count < 3
| "#" := rename(_count)

#event_simpleName="NetworkConnectIP4"
| ipLocation(field=RemoteAddressIP4)
| top(RemoteAddressIP4.country, as="#")

in(#event_simpleName, values=[
	"DetectAnalysis",
	"FileDetectInfo",
	"HttpRequestDetect",
	"KillThreadStatus",
	"ModuleBlockedEventWithPatternId",
	"NetworkOutboundPortScanDetectInfo",
	"PackedExecutableWritten",
	"RansomwareCreateFile",
	"RansomwareFileAccessPattern",
	"RansomwareRenameFile",
	"RegGenericValueUpdate",
	"RegistryOperationBlocked",
	"RegistryOperationDetectInfo",
	"RemediationActionKillProcess",
	"RemediationActionQuarantineFile",
	"RemediationActionRegistryRemoval",
	"RemediationMonitorKillProcess",
	"RemediationMonitorQuarantineFile",
	"RemediationMonitorRegistryRemoval",
	"RemoteBruteForceDetectInfo",
	"ScriptControlBlocked",
	"ScriptControlDetectInfo",
	"SuspiciousCreateSymbolicLink",
	"SuspiciousPeFileWritten",
	"SuspiciousRegAsepUpdate",
	"SuspiciousRegAsepUpdateFromUnsignedModule",
	"SuspiciousRegAsepUpdateLacksContextSignature",
	"SuspiciousRegAsepUpdateLacksTargetSignature",
	"SuspiciousRegAsepUpdateToUnsignedTarget",
	"SuspiciousUserRemoteAPCAttempt",
	"TokenImpersonated",
	"ZerologonExploitAttempt",
	"CriticalFileModified"
])
| groupBy(#event_simpleName, limit=max)
| "#" := rename(_count)

#event_simpleName="DnsRequest"
| entropy:=shannonEntropy(DomainName)
| not akamaihd
| GroupBy([DomainName, entropy], limit=max)
| "#" := rename(_count)
| sort(entropy, limit=20)

/*
 * This query finds the open ports for a given agent ID (aid,
 * parameterized '?aid').
 * This query will only give results if the ?aid parameter is specified.
 */
| test(?aid != "*")
| #event_simpleName="ProcessRollup2" OR #event_simpleName=NetworkListenIP4
| aid=?aid
// We want to join the ContextProcessId of the network events with the
// TargetProcessId of the process events, so we create a new field 'pid'
// that we can group on.
| #event_simpleName match {
  "ProcessRollup2" => pid := TargetProcessId;
  "NetworkListenIP4" => pid := ContextProcessId;
}
| groupBy("pid", function=[
    // Extract the agentID and process path
    { #event_simpleName="ProcessRollup2" | selectLast([aid, ImageFileName])},
    // Extract up to 200 addresses and ports
    { #event_simpleName=NetworkListenIP4 | table([LocalAddressIP4,LocalPort]) }
  ], limit=max)
// Filter off the results that do not have a value for LocalAddressIP4,
// LocalPort and ImageFileName fields.
| LocalAddressIP4=* AND LocalPort=* | ImageFileName=*
// Present the results.
| table([aid, ImageFileName,LocalAddressIP4,LocalPort])

#event_simpleName="NetworkConnectIP4"
| top(RemoteAddressIP4, as="#")

#event_simpleName="ScriptControl"
| ScriptEntropy:=shannonEntropy(ScriptContent)
| GroupBy([ScriptEntropy, ScriptContent], limit=max)
| "#" := rename(_count)
| sort(ScriptEntropy, limit=20)

#event_simpleName="ProcessRollup2" AND OriginalFilename=*
| regex("(?<FileName>.+(\.exe)+)", field=OriginalFilename)
| test(OriginalFilename != FileName)
| table([ImageFileName, OriginalFilename])