forked from rod-trent/SentinelKQL
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathTracking Privileged Account Rare Activity without AWS.txt
129 lines (129 loc) · 8.16 KB
/
Tracking Privileged Account Rare Activity without AWS.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
let LocalSID = "S-1-5-32-5[0-9][0-9]$";
let GroupSID = "S-1-5-21-[0-9]*-[0-9]*-[0-9]*-5[0-9][0-9]$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-1102$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-1103$";
let timeframe = 8d;
let p_Accounts = SecurityEvent
| where TimeGenerated > ago(timeframe)
| where EventID in ("4728", "4732", "4756") and AccountType == "User" and MemberName == "-"
// Exclude Remote Desktop Users group: S-1-5-32-555 and IIS Users group S-1-5-32-568
| where TargetSid !in ("S-1-5-32-555", "S-1-5-32-568")
| where TargetSid matches regex LocalSID or TargetSid matches regex GroupSID
| summarize by DomainSlashAccount = tolower(SubjectAccount), NtDomain = SubjectDomainName,
AccountAtDomain = tolower(strcat(SubjectUserName,"@",SubjectDomainName)), AccountName = tolower(SubjectUserName);
// Build custom high value account list
let cust_Accounts = datatable(Account:string, NtDomain:string, Domain:string)[
"john", "Contoso", "contoso.com", "greg", "Contoso", "contoso.com", "larry", "Domain", "contoso.com"];
let c_Accounts = cust_Accounts
| extend AccountAtDomain = tolower(strcat(Account,"@",Domain)), AccountName = tolower(Account),
DomainSlashAccount = tolower(strcat(NtDomain,"\\",Account));
let AccountFormat = p_Accounts | union c_Accounts | project AccountName, AccountAtDomain, DomainSlashAccount;
// Normalize activity from diverse sources into common schema using a function
let activity = view (a_StartTime:datetime, a_EndTime:datetime) {
(union isfuzzy=true
(AccountFormat | join kind=inner
(SigninLogs
| where TimeGenerated >= a_StartTime and TimeGenerated <= a_EndTime
| extend AccountName = tolower(split(UserPrincipalName, "@")[0]), WinSecEventDomain = "-"
| project-rename EventType = strcat(OperationName, "-", ResultType, "-", ResultDescription), ServiceOrSystem = AppDisplayName, ClientIP = IPAddress)
on AccountName),
(AccountFormat | join kind=inner
(OfficeActivity
| where TimeGenerated >= a_StartTime and TimeGenerated <= a_EndTime
| extend AccountName = tolower(split(UserId, "@")[0]), WinSecEventDomain = "-"
| project-rename EventType = strcat(Operation, "-", ResultStatus), ServiceOrSystem = OfficeWorkload)
on AccountName),
(AccountFormat | join kind=inner
(SecurityEvent
| where TimeGenerated >= a_StartTime and TimeGenerated <= a_EndTime
| where EventID in (4624, 4625)
| extend ClientIP = "-"
| extend AccountName = tolower(split(Account,"\\")[1]), Domain = tolower(split(Account,"\\")[0])
| project-rename EventType = Activity, ServiceOrSystem = Computer, WinSecEventDomain = Domain)
on AccountName),
(AccountFormat | join kind=inner
(W3CIISLog
| where TimeGenerated >= a_StartTime and TimeGenerated <= a_EndTime
| where csUserName != "-" and isnotempty(csUserName)
| extend AccountName = tolower(csUserName), WinSecEventDomain = "-"
| project-rename EventType = csMethod, ServiceOrSystem = sSiteName, ClientIP = cIP)
on AccountName),
(AccountFormat | join kind=inner
(W3CIISLog
| where TimeGenerated >= a_StartTime and TimeGenerated <= a_EndTime
| where csUserName != "-" and isnotempty(csUserName)
| extend AccountAtDomain = tolower(csUserName), WinSecEventDomain = "-"
| project-rename EventType = csMethod, ServiceOrSystem = sSiteName, ClientIP = cIP)
on AccountAtDomain));
};
// Rare activity today versus prior week
let LastDay = startofday(ago(1d));
let PrevDay = endofday(ago(2d));
let Prev7Day = startofday(ago(8d));
let ra_LastDay = activity(LastDay, now())
| summarize ra_StartTime = min(TimeGenerated), ra_EndTime = max(TimeGenerated),
ra_Count = count() by Type, AccountName, EventType, ClientIP, ServiceOrSystem, WinSecEventDomain;
let a_7day = activity(Prev7Day, PrevDay)
| summarize ha_Count = count() by Type, AccountName, EventType, ClientIP, ServiceOrSystem, WinSecEventDomain;
let ra_Today = ra_LastDay | join kind=leftanti (a_7day) on Type, AccountName, ServiceOrSystem
| extend RareServiceOrSystem = ServiceOrSystem;
// Retrieve related activity as context
let a_Related =
(union isfuzzy=true
(// Make sure we at least publish the unusual activity we identified above - even if no related context activity is found in the subsequent union
ra_Today),
// Remaining elements of the union look for related activity
(ra_Today | join kind=inner
(OfficeActivity
| where TimeGenerated > LastDay
| summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), rel_ServiceOrSystemCount = dcount(OfficeWorkload),
rel_ServiceOrSystemSet = makeset(OfficeWorkload), rel_ClientIPSet = makeset(ClientIP),
rel_Count = count() by AccountName = tolower(UserId), rel_EventType = Operation, Type
) on AccountName),
(ra_Today | join kind=inner
(SecurityEvent | where TimeGenerated > LastDay
| where EventID in (4624, 4625)
| where AccountType == "User"
| summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), rel_ServiceOrSystemCount = dcount(Computer),
rel_ServiceOrSystemSet = makeset(Computer), rel_ClientIPSet = makeset("-"),
rel_Count = count() by DomainSlashAccount = tolower(Account), rel_EventType = Activity, Type
) on DomainSlashAccount),
(ra_Today | join kind=inner
(Event | where TimeGenerated > LastDay
// 7045: A service was installed in the system
| where EventID == 7045
| summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), rel_ServiceOrSystemCount = dcount(Computer),
rel_ServiceOrSystemSet = makeset(Computer), rel_ClientIPSet = makeset("-"),
rel_Count = count() by DomainSlashAccount = tolower(UserName), rel_EventType = strcat(EventID, "-", tostring(split(RenderedDescription,".")[0])), Type
) on DomainSlashAccount),
(ra_Today | join kind=inner
(SecurityEvent | where TimeGenerated > LastDay
// 4720: Account created, 4726: Account deleted
| where EventID in (4720,4726)
| summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), rel_ServiceOrSystemCount = dcount(UserPrincipalName),
rel_ServiceOrSystemSet = makeset(UserPrincipalName), rel_ClientIPSet = makeset("-"),
rel_Count = count() by DomainSlashAccount = tolower(Account), rel_EventType = Activity, Type
) on DomainSlashAccount),
(ra_Today | join kind=inner
(SigninLogs | where TimeGenerated > LastDay
| extend RemoteHost = tolower(tostring(parsejson(DeviceDetail.["displayName"])))
| extend OS = DeviceDetail.operatingSystem, Browser = DeviceDetail.browser, StatusCode = tostring(Status.errorCode),
StatusDetails = tostring(Status.additionalDetails), State = tostring(LocationDetails.state)
| summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), a_RelatedRemoteHostSet = makeset(RemoteHost),
rel_ServiceOrSystemSet = makeset(AppDisplayName), rel_ServiceOrSystemCount = dcount(AppDisplayName), rel_ClientIPSet = makeset(IPAddress),
rel_StateSet = makeset(State),
rel_Count = count() by AccountAtDomain = tolower(UserPrincipalName), rel_EventType = iff(isnotempty(ResultDescription), ResultDescription, StatusDetails), Type
) on AccountAtDomain),
(ra_Today | join kind=inner
(SecurityAlert | where TimeGenerated > LastDay
| extend ExtProps=parsejson(ExtendedProperties)
| extend AccountName = tostring(ExtProps.["user name"])
| summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), rel_ServiceOrSystemCount = dcount(AlertType),
rel_ServiceOrSystemSet = makeset(AlertType),
rel_Count = count() by DomainSlashAccount = tolower(AccountName), rel_EventType = ProductName, Type
) on DomainSlashAccount)
);
a_Related
| project Type, RareActivtyStartTimeUtc = ra_StartTime, RareActivityEndTimeUtc = ra_EndTime, RareActivityCount = ra_Count,
AccountName, WinSecEventDomain, EventType, RareServiceOrSystem, RelatedActivityStartTimeUtc = rel_StartTime,
RelatedActivityEndTimeUtc = rel_EndTime, RelatedActivityEventType = rel_EventType, RelatedActivityClientIPSet = rel_ClientIPSet,
RelatedActivityServiceOrSystemCount = rel_ServiceOrSystemCount, RelatedActivityServiceOrSystemSet = rel_ServiceOrSystemSet, RelatedActivityCount = rel_Count
| extend timestamp = RareActivtyStartTimeUtc, AccountCustomEntity = AccountName