-
Notifications
You must be signed in to change notification settings - Fork 4
/
adxScript.kusto
294 lines (147 loc) · 70.7 KB
/
adxScript.kusto
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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
.create table AlertInfoRaw (records:dynamic)
.create-or-alter table AlertInfoRaw ingestion json mapping 'AlertInfoRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table AlertInfoRaw policy retention softdelete = 1d
.create table AlertInfo (Timestamp:datetime,AlertId:string,Title:string,Category:string,Severity:string,ServiceSource:string,DetectionSource:string,AttackTechniques:string)
.alter-merge table AlertInfo policy retention softdelete = 365d recoverability = enabled
.create-or-alter function AlertInfoExpand {AlertInfoRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),AlertId = tostring(events.properties.AlertId),Title = tostring(events.properties.Title),Category = tostring(events.properties.Category),Severity = tostring(events.properties.Severity),ServiceSource = tostring(events.properties.ServiceSource),DetectionSource = tostring(events.properties.DetectionSource),AttackTechniques = tostring(events.properties.AttackTechniques) }
.alter table AlertInfo policy update @'[{"Source": "AlertInfoRaw", "Query": "AlertInfoExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table AlertEvidenceRaw (records:dynamic)
.create-or-alter table AlertEvidenceRaw ingestion json mapping 'AlertEvidenceRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table AlertEvidenceRaw policy retention softdelete = 1d
.create table AlertEvidence (Timestamp:datetime,AlertId:string,Title:string,Categories:string,AttackTechniques:string,ServiceSource:string,DetectionSource:string,EntityType:string,EvidenceRole:string,EvidenceDirection:string,FileName:string,FolderPath:string,SHA1:string,SHA256:string,FileSize:long,ThreatFamily:string,RemoteIP:string,RemoteUrl:string,AccountName:string,AccountDomain:string,AccountSid:string,AccountObjectId:string,AccountUpn:string,DeviceId:string,DeviceName:string,LocalIP:string,NetworkMessageId:string,EmailSubject:string,Application:string,ApplicationId:int,OAuthApplicationId:string,ProcessCommandLine:string,RegistryKey:string,RegistryValueName:string,RegistryValueData:string,AdditionalFields:string,Severity:string)
.alter-merge table AlertEvidence policy retention softdelete = 365d recoverability = enabled
.create-or-alter function AlertEvidenceExpand {AlertEvidenceRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),AlertId = tostring(events.properties.AlertId),Title = tostring(events.properties.Title),Categories = tostring(events.properties.Categories),AttackTechniques = tostring(events.properties.AttackTechniques),ServiceSource = tostring(events.properties.ServiceSource),DetectionSource = tostring(events.properties.DetectionSource),EntityType = tostring(events.properties.EntityType),EvidenceRole = tostring(events.properties.EvidenceRole),EvidenceDirection = tostring(events.properties.EvidenceDirection),FileName = tostring(events.properties.FileName),FolderPath = tostring(events.properties.FolderPath),SHA1 = tostring(events.properties.SHA1),SHA256 = tostring(events.properties.SHA256),FileSize = tolong(events.properties.FileSize),ThreatFamily = tostring(events.properties.ThreatFamily),RemoteIP = tostring(events.properties.RemoteIP),RemoteUrl = tostring(events.properties.RemoteUrl),AccountName = tostring(events.properties.AccountName),AccountDomain = tostring(events.properties.AccountDomain),AccountSid = tostring(events.properties.AccountSid),AccountObjectId = tostring(events.properties.AccountObjectId),AccountUpn = tostring(events.properties.AccountUpn),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),LocalIP = tostring(events.properties.LocalIP),NetworkMessageId = tostring(events.properties.NetworkMessageId),EmailSubject = tostring(events.properties.EmailSubject),Application = tostring(events.properties.Application),ApplicationId = toint(events.properties.ApplicationId),OAuthApplicationId = tostring(events.properties.OAuthApplicationId),ProcessCommandLine = tostring(events.properties.ProcessCommandLine),RegistryKey = tostring(events.properties.RegistryKey),RegistryValueName = tostring(events.properties.RegistryValueName),RegistryValueData = tostring(events.properties.RegistryValueData),AdditionalFields = tostring(events.properties.AdditionalFields),Severity = tostring(events.properties.Severity) }
.alter table AlertEvidence policy update @'[{"Source": "AlertEvidenceRaw", "Query": "AlertEvidenceExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table DeviceInfoRaw (records:dynamic)
.create-or-alter table DeviceInfoRaw ingestion json mapping 'DeviceInfoRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table DeviceInfoRaw policy retention softdelete = 1d
.create table DeviceInfo (Timestamp:datetime,DeviceId:string,DeviceName:string,ClientVersion:string,PublicIP:string,OSArchitecture:string,OSPlatform:string,OSBuild:long,IsAzureADJoined:bool,JoinType:string,AadDeviceId:string,LoggedOnUsers:string,RegistryDeviceTag:string,OSVersion:string,MachineGroup:string,ReportId:long,OnboardingStatus:string,AdditionalFields:string,DeviceCategory:string,DeviceType:string,DeviceSubtype:string,Model:string,Vendor:string,OSDistribution:string,OSVersionInfo:string,MergedDeviceIds:string,MergedToDeviceId:string,IsInternetFacing:bool,SensorHealthState:string,IsExcluded:bool,ExclusionReason:string,ExposureLevel:string,AssetValue:string)
.alter-merge table DeviceInfo policy retention softdelete = 365d recoverability = enabled
.create-or-alter function DeviceInfoExpand {DeviceInfoRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),ClientVersion = tostring(events.properties.ClientVersion),PublicIP = tostring(events.properties.PublicIP),OSArchitecture = tostring(events.properties.OSArchitecture),OSPlatform = tostring(events.properties.OSPlatform),OSBuild = tolong(events.properties.OSBuild),IsAzureADJoined = tobool(events.properties.IsAzureADJoined),JoinType = tostring(events.properties.JoinType),AadDeviceId = tostring(events.properties.AadDeviceId),LoggedOnUsers = tostring(events.properties.LoggedOnUsers),RegistryDeviceTag = tostring(events.properties.RegistryDeviceTag),OSVersion = tostring(events.properties.OSVersion),MachineGroup = tostring(events.properties.MachineGroup),ReportId = tolong(events.properties.ReportId),OnboardingStatus = tostring(events.properties.OnboardingStatus),AdditionalFields = tostring(events.properties.AdditionalFields),DeviceCategory = tostring(events.properties.DeviceCategory),DeviceType = tostring(events.properties.DeviceType),DeviceSubtype = tostring(events.properties.DeviceSubtype),Model = tostring(events.properties.Model),Vendor = tostring(events.properties.Vendor),OSDistribution = tostring(events.properties.OSDistribution),OSVersionInfo = tostring(events.properties.OSVersionInfo),MergedDeviceIds = tostring(events.properties.MergedDeviceIds),MergedToDeviceId = tostring(events.properties.MergedToDeviceId),IsInternetFacing = tobool(events.properties.IsInternetFacing),SensorHealthState = tostring(events.properties.SensorHealthState),IsExcluded = tobool(events.properties.IsExcluded),ExclusionReason = tostring(events.properties.ExclusionReason),ExposureLevel = tostring(events.properties.ExposureLevel),AssetValue = tostring(events.properties.AssetValue) }
.alter table DeviceInfo policy update @'[{"Source": "DeviceInfoRaw", "Query": "DeviceInfoExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table DeviceNetworkInfoRaw (records:dynamic)
.create-or-alter table DeviceNetworkInfoRaw ingestion json mapping 'DeviceNetworkInfoRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table DeviceNetworkInfoRaw policy retention softdelete = 1d
.create table DeviceNetworkInfo (Timestamp:datetime,DeviceId:string,DeviceName:string,NetworkAdapterName:string,MacAddress:string,NetworkAdapterType:string,NetworkAdapterStatus:string,TunnelType:string,ConnectedNetworks:string,DnsAddresses:string,IPv4Dhcp:string,IPv6Dhcp:string,DefaultGateways:string,IPAddresses:string,ReportId:long,NetworkAdapterVendor:string)
.alter-merge table DeviceNetworkInfo policy retention softdelete = 365d recoverability = enabled
.create-or-alter function DeviceNetworkInfoExpand {DeviceNetworkInfoRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),NetworkAdapterName = tostring(events.properties.NetworkAdapterName),MacAddress = tostring(events.properties.MacAddress),NetworkAdapterType = tostring(events.properties.NetworkAdapterType),NetworkAdapterStatus = tostring(events.properties.NetworkAdapterStatus),TunnelType = tostring(events.properties.TunnelType),ConnectedNetworks = tostring(events.properties.ConnectedNetworks),DnsAddresses = tostring(events.properties.DnsAddresses),IPv4Dhcp = tostring(events.properties.IPv4Dhcp),IPv6Dhcp = tostring(events.properties.IPv6Dhcp),DefaultGateways = tostring(events.properties.DefaultGateways),IPAddresses = tostring(events.properties.IPAddresses),ReportId = tolong(events.properties.ReportId),NetworkAdapterVendor = tostring(events.properties.NetworkAdapterVendor) }
.alter table DeviceNetworkInfo policy update @'[{"Source": "DeviceNetworkInfoRaw", "Query": "DeviceNetworkInfoExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table DeviceProcessEventsRaw (records:dynamic)
.create-or-alter table DeviceProcessEventsRaw ingestion json mapping 'DeviceProcessEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table DeviceProcessEventsRaw policy retention softdelete = 1d
.create table DeviceProcessEvents (Timestamp:datetime,DeviceId:string,DeviceName:string,ActionType:string,FileName:string,FolderPath:string,SHA1:string,SHA256:string,MD5:string,FileSize:long,ProcessVersionInfoCompanyName:string,ProcessVersionInfoProductName:string,ProcessVersionInfoProductVersion:string,ProcessVersionInfoInternalFileName:string,ProcessVersionInfoOriginalFileName:string,ProcessVersionInfoFileDescription:string,ProcessId:long,ProcessCommandLine:string,ProcessIntegrityLevel:string,ProcessTokenElevation:string,ProcessCreationTime:datetime,AccountDomain:string,AccountName:string,AccountSid:string,AccountUpn:string,AccountObjectId:string,LogonId:long,InitiatingProcessAccountDomain:string,InitiatingProcessAccountName:string,InitiatingProcessAccountSid:string,InitiatingProcessAccountUpn:string,InitiatingProcessAccountObjectId:string,InitiatingProcessLogonId:long,InitiatingProcessIntegrityLevel:string,InitiatingProcessTokenElevation:string,InitiatingProcessSHA1:string,InitiatingProcessSHA256:string,InitiatingProcessMD5:string,InitiatingProcessFileName:string,InitiatingProcessFileSize:long,InitiatingProcessVersionInfoCompanyName:string,InitiatingProcessVersionInfoProductName:string,InitiatingProcessVersionInfoProductVersion:string,InitiatingProcessVersionInfoInternalFileName:string,InitiatingProcessVersionInfoOriginalFileName:string,InitiatingProcessVersionInfoFileDescription:string,InitiatingProcessId:long,InitiatingProcessCommandLine:string,InitiatingProcessCreationTime:datetime,InitiatingProcessFolderPath:string,InitiatingProcessParentId:long,InitiatingProcessParentFileName:string,InitiatingProcessParentCreationTime:datetime,InitiatingProcessSignerType:string,InitiatingProcessSignatureStatus:string,ReportId:long,AppGuardContainerId:string,AdditionalFields:string)
.alter-merge table DeviceProcessEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function DeviceProcessEventsExpand {DeviceProcessEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),ActionType = tostring(events.properties.ActionType),FileName = tostring(events.properties.FileName),FolderPath = tostring(events.properties.FolderPath),SHA1 = tostring(events.properties.SHA1),SHA256 = tostring(events.properties.SHA256),MD5 = tostring(events.properties.MD5),FileSize = tolong(events.properties.FileSize),ProcessVersionInfoCompanyName = tostring(events.properties.ProcessVersionInfoCompanyName),ProcessVersionInfoProductName = tostring(events.properties.ProcessVersionInfoProductName),ProcessVersionInfoProductVersion = tostring(events.properties.ProcessVersionInfoProductVersion),ProcessVersionInfoInternalFileName = tostring(events.properties.ProcessVersionInfoInternalFileName),ProcessVersionInfoOriginalFileName = tostring(events.properties.ProcessVersionInfoOriginalFileName),ProcessVersionInfoFileDescription = tostring(events.properties.ProcessVersionInfoFileDescription),ProcessId = tolong(events.properties.ProcessId),ProcessCommandLine = tostring(events.properties.ProcessCommandLine),ProcessIntegrityLevel = tostring(events.properties.ProcessIntegrityLevel),ProcessTokenElevation = tostring(events.properties.ProcessTokenElevation),ProcessCreationTime = todatetime(events.properties.ProcessCreationTime),AccountDomain = tostring(events.properties.AccountDomain),AccountName = tostring(events.properties.AccountName),AccountSid = tostring(events.properties.AccountSid),AccountUpn = tostring(events.properties.AccountUpn),AccountObjectId = tostring(events.properties.AccountObjectId),LogonId = tolong(events.properties.LogonId),InitiatingProcessAccountDomain = tostring(events.properties.InitiatingProcessAccountDomain),InitiatingProcessAccountName = tostring(events.properties.InitiatingProcessAccountName),InitiatingProcessAccountSid = tostring(events.properties.InitiatingProcessAccountSid),InitiatingProcessAccountUpn = tostring(events.properties.InitiatingProcessAccountUpn),InitiatingProcessAccountObjectId = tostring(events.properties.InitiatingProcessAccountObjectId),InitiatingProcessLogonId = tolong(events.properties.InitiatingProcessLogonId),InitiatingProcessIntegrityLevel = tostring(events.properties.InitiatingProcessIntegrityLevel),InitiatingProcessTokenElevation = tostring(events.properties.InitiatingProcessTokenElevation),InitiatingProcessSHA1 = tostring(events.properties.InitiatingProcessSHA1),InitiatingProcessSHA256 = tostring(events.properties.InitiatingProcessSHA256),InitiatingProcessMD5 = tostring(events.properties.InitiatingProcessMD5),InitiatingProcessFileName = tostring(events.properties.InitiatingProcessFileName),InitiatingProcessFileSize = tolong(events.properties.InitiatingProcessFileSize),InitiatingProcessVersionInfoCompanyName = tostring(events.properties.InitiatingProcessVersionInfoCompanyName),InitiatingProcessVersionInfoProductName = tostring(events.properties.InitiatingProcessVersionInfoProductName),InitiatingProcessVersionInfoProductVersion = tostring(events.properties.InitiatingProcessVersionInfoProductVersion),InitiatingProcessVersionInfoInternalFileName = tostring(events.properties.InitiatingProcessVersionInfoInternalFileName),InitiatingProcessVersionInfoOriginalFileName = tostring(events.properties.InitiatingProcessVersionInfoOriginalFileName),InitiatingProcessVersionInfoFileDescription = tostring(events.properties.InitiatingProcessVersionInfoFileDescription),InitiatingProcessId = tolong(events.properties.InitiatingProcessId),InitiatingProcessCommandLine = tostring(events.properties.InitiatingProcessCommandLine),InitiatingProcessCreationTime = todatetime(events.properties.InitiatingProcessCreationTime),InitiatingProcessFolderPath = tostring(events.properties.InitiatingProcessFolderPath),InitiatingProcessParentId = tolong(events.properties.InitiatingProcessParentId),InitiatingProcessParentFileName = tostring(events.properties.InitiatingProcessParentFileName),InitiatingProcessParentCreationTime = todatetime(events.properties.InitiatingProcessParentCreationTime),InitiatingProcessSignerType = tostring(events.properties.InitiatingProcessSignerType),InitiatingProcessSignatureStatus = tostring(events.properties.InitiatingProcessSignatureStatus),ReportId = tolong(events.properties.ReportId),AppGuardContainerId = tostring(events.properties.AppGuardContainerId),AdditionalFields = tostring(events.properties.AdditionalFields) }
.alter table DeviceProcessEvents policy update @'[{"Source": "DeviceProcessEventsRaw", "Query": "DeviceProcessEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table DeviceNetworkEventsRaw (records:dynamic)
.create-or-alter table DeviceNetworkEventsRaw ingestion json mapping 'DeviceNetworkEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table DeviceNetworkEventsRaw policy retention softdelete = 1d
.create table DeviceNetworkEvents (Timestamp:datetime,DeviceId:string,DeviceName:string,ActionType:string,RemoteIP:string,RemotePort:int,RemoteUrl:string,LocalIP:string,LocalPort:int,Protocol:string,LocalIPType:string,RemoteIPType:string,InitiatingProcessSHA1:string,InitiatingProcessSHA256:string,InitiatingProcessMD5:string,InitiatingProcessFileName:string,InitiatingProcessFileSize:long,InitiatingProcessVersionInfoCompanyName:string,InitiatingProcessVersionInfoProductName:string,InitiatingProcessVersionInfoProductVersion:string,InitiatingProcessVersionInfoInternalFileName:string,InitiatingProcessVersionInfoOriginalFileName:string,InitiatingProcessVersionInfoFileDescription:string,InitiatingProcessId:long,InitiatingProcessCommandLine:string,InitiatingProcessCreationTime:datetime,InitiatingProcessFolderPath:string,InitiatingProcessParentFileName:string,InitiatingProcessParentId:long,InitiatingProcessParentCreationTime:datetime,InitiatingProcessAccountDomain:string,InitiatingProcessAccountName:string,InitiatingProcessAccountSid:string,InitiatingProcessAccountUpn:string,InitiatingProcessAccountObjectId:string,InitiatingProcessIntegrityLevel:string,InitiatingProcessTokenElevation:string,ReportId:long,AppGuardContainerId:string,AdditionalFields:string)
.alter-merge table DeviceNetworkEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function DeviceNetworkEventsExpand {DeviceNetworkEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),ActionType = tostring(events.properties.ActionType),RemoteIP = tostring(events.properties.RemoteIP),RemotePort = toint(events.properties.RemotePort),RemoteUrl = tostring(events.properties.RemoteUrl),LocalIP = tostring(events.properties.LocalIP),LocalPort = toint(events.properties.LocalPort),Protocol = tostring(events.properties.Protocol),LocalIPType = tostring(events.properties.LocalIPType),RemoteIPType = tostring(events.properties.RemoteIPType),InitiatingProcessSHA1 = tostring(events.properties.InitiatingProcessSHA1),InitiatingProcessSHA256 = tostring(events.properties.InitiatingProcessSHA256),InitiatingProcessMD5 = tostring(events.properties.InitiatingProcessMD5),InitiatingProcessFileName = tostring(events.properties.InitiatingProcessFileName),InitiatingProcessFileSize = tolong(events.properties.InitiatingProcessFileSize),InitiatingProcessVersionInfoCompanyName = tostring(events.properties.InitiatingProcessVersionInfoCompanyName),InitiatingProcessVersionInfoProductName = tostring(events.properties.InitiatingProcessVersionInfoProductName),InitiatingProcessVersionInfoProductVersion = tostring(events.properties.InitiatingProcessVersionInfoProductVersion),InitiatingProcessVersionInfoInternalFileName = tostring(events.properties.InitiatingProcessVersionInfoInternalFileName),InitiatingProcessVersionInfoOriginalFileName = tostring(events.properties.InitiatingProcessVersionInfoOriginalFileName),InitiatingProcessVersionInfoFileDescription = tostring(events.properties.InitiatingProcessVersionInfoFileDescription),InitiatingProcessId = tolong(events.properties.InitiatingProcessId),InitiatingProcessCommandLine = tostring(events.properties.InitiatingProcessCommandLine),InitiatingProcessCreationTime = todatetime(events.properties.InitiatingProcessCreationTime),InitiatingProcessFolderPath = tostring(events.properties.InitiatingProcessFolderPath),InitiatingProcessParentFileName = tostring(events.properties.InitiatingProcessParentFileName),InitiatingProcessParentId = tolong(events.properties.InitiatingProcessParentId),InitiatingProcessParentCreationTime = todatetime(events.properties.InitiatingProcessParentCreationTime),InitiatingProcessAccountDomain = tostring(events.properties.InitiatingProcessAccountDomain),InitiatingProcessAccountName = tostring(events.properties.InitiatingProcessAccountName),InitiatingProcessAccountSid = tostring(events.properties.InitiatingProcessAccountSid),InitiatingProcessAccountUpn = tostring(events.properties.InitiatingProcessAccountUpn),InitiatingProcessAccountObjectId = tostring(events.properties.InitiatingProcessAccountObjectId),InitiatingProcessIntegrityLevel = tostring(events.properties.InitiatingProcessIntegrityLevel),InitiatingProcessTokenElevation = tostring(events.properties.InitiatingProcessTokenElevation),ReportId = tolong(events.properties.ReportId),AppGuardContainerId = tostring(events.properties.AppGuardContainerId),AdditionalFields = tostring(events.properties.AdditionalFields) }
.alter table DeviceNetworkEvents policy update @'[{"Source": "DeviceNetworkEventsRaw", "Query": "DeviceNetworkEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table DeviceFileEventsRaw (records:dynamic)
.create-or-alter table DeviceFileEventsRaw ingestion json mapping 'DeviceFileEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table DeviceFileEventsRaw policy retention softdelete = 1d
.create table DeviceFileEvents (Timestamp:datetime,DeviceId:string,DeviceName:string,ActionType:string,FileName:string,FolderPath:string,SHA1:string,SHA256:string,MD5:string,FileOriginUrl:string,FileOriginReferrerUrl:string,FileOriginIP:string,PreviousFolderPath:string,PreviousFileName:string,FileSize:long,InitiatingProcessAccountDomain:string,InitiatingProcessAccountName:string,InitiatingProcessAccountSid:string,InitiatingProcessAccountUpn:string,InitiatingProcessAccountObjectId:string,InitiatingProcessMD5:string,InitiatingProcessSHA1:string,InitiatingProcessSHA256:string,InitiatingProcessFolderPath:string,InitiatingProcessFileName:string,InitiatingProcessFileSize:long,InitiatingProcessVersionInfoCompanyName:string,InitiatingProcessVersionInfoProductName:string,InitiatingProcessVersionInfoProductVersion:string,InitiatingProcessVersionInfoInternalFileName:string,InitiatingProcessVersionInfoOriginalFileName:string,InitiatingProcessVersionInfoFileDescription:string,InitiatingProcessId:long,InitiatingProcessCommandLine:string,InitiatingProcessCreationTime:datetime,InitiatingProcessIntegrityLevel:string,InitiatingProcessTokenElevation:string,InitiatingProcessParentId:long,InitiatingProcessParentFileName:string,InitiatingProcessParentCreationTime:datetime,RequestProtocol:string,RequestSourceIP:string,RequestSourcePort:int,RequestAccountName:string,RequestAccountDomain:string,RequestAccountSid:string,ShareName:string,SensitivityLabel:string,SensitivitySubLabel:string,IsAzureInfoProtectionApplied:bool,ReportId:long,AppGuardContainerId:string,AdditionalFields:string)
.alter-merge table DeviceFileEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function DeviceFileEventsExpand {DeviceFileEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),ActionType = tostring(events.properties.ActionType),FileName = tostring(events.properties.FileName),FolderPath = tostring(events.properties.FolderPath),SHA1 = tostring(events.properties.SHA1),SHA256 = tostring(events.properties.SHA256),MD5 = tostring(events.properties.MD5),FileOriginUrl = tostring(events.properties.FileOriginUrl),FileOriginReferrerUrl = tostring(events.properties.FileOriginReferrerUrl),FileOriginIP = tostring(events.properties.FileOriginIP),PreviousFolderPath = tostring(events.properties.PreviousFolderPath),PreviousFileName = tostring(events.properties.PreviousFileName),FileSize = tolong(events.properties.FileSize),InitiatingProcessAccountDomain = tostring(events.properties.InitiatingProcessAccountDomain),InitiatingProcessAccountName = tostring(events.properties.InitiatingProcessAccountName),InitiatingProcessAccountSid = tostring(events.properties.InitiatingProcessAccountSid),InitiatingProcessAccountUpn = tostring(events.properties.InitiatingProcessAccountUpn),InitiatingProcessAccountObjectId = tostring(events.properties.InitiatingProcessAccountObjectId),InitiatingProcessMD5 = tostring(events.properties.InitiatingProcessMD5),InitiatingProcessSHA1 = tostring(events.properties.InitiatingProcessSHA1),InitiatingProcessSHA256 = tostring(events.properties.InitiatingProcessSHA256),InitiatingProcessFolderPath = tostring(events.properties.InitiatingProcessFolderPath),InitiatingProcessFileName = tostring(events.properties.InitiatingProcessFileName),InitiatingProcessFileSize = tolong(events.properties.InitiatingProcessFileSize),InitiatingProcessVersionInfoCompanyName = tostring(events.properties.InitiatingProcessVersionInfoCompanyName),InitiatingProcessVersionInfoProductName = tostring(events.properties.InitiatingProcessVersionInfoProductName),InitiatingProcessVersionInfoProductVersion = tostring(events.properties.InitiatingProcessVersionInfoProductVersion),InitiatingProcessVersionInfoInternalFileName = tostring(events.properties.InitiatingProcessVersionInfoInternalFileName),InitiatingProcessVersionInfoOriginalFileName = tostring(events.properties.InitiatingProcessVersionInfoOriginalFileName),InitiatingProcessVersionInfoFileDescription = tostring(events.properties.InitiatingProcessVersionInfoFileDescription),InitiatingProcessId = tolong(events.properties.InitiatingProcessId),InitiatingProcessCommandLine = tostring(events.properties.InitiatingProcessCommandLine),InitiatingProcessCreationTime = todatetime(events.properties.InitiatingProcessCreationTime),InitiatingProcessIntegrityLevel = tostring(events.properties.InitiatingProcessIntegrityLevel),InitiatingProcessTokenElevation = tostring(events.properties.InitiatingProcessTokenElevation),InitiatingProcessParentId = tolong(events.properties.InitiatingProcessParentId),InitiatingProcessParentFileName = tostring(events.properties.InitiatingProcessParentFileName),InitiatingProcessParentCreationTime = todatetime(events.properties.InitiatingProcessParentCreationTime),RequestProtocol = tostring(events.properties.RequestProtocol),RequestSourceIP = tostring(events.properties.RequestSourceIP),RequestSourcePort = toint(events.properties.RequestSourcePort),RequestAccountName = tostring(events.properties.RequestAccountName),RequestAccountDomain = tostring(events.properties.RequestAccountDomain),RequestAccountSid = tostring(events.properties.RequestAccountSid),ShareName = tostring(events.properties.ShareName),SensitivityLabel = tostring(events.properties.SensitivityLabel),SensitivitySubLabel = tostring(events.properties.SensitivitySubLabel),IsAzureInfoProtectionApplied = tobool(events.properties.IsAzureInfoProtectionApplied),ReportId = tolong(events.properties.ReportId),AppGuardContainerId = tostring(events.properties.AppGuardContainerId),AdditionalFields = tostring(events.properties.AdditionalFields) }
.alter table DeviceFileEvents policy update @'[{"Source": "DeviceFileEventsRaw", "Query": "DeviceFileEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table DeviceRegistryEventsRaw (records:dynamic)
.create-or-alter table DeviceRegistryEventsRaw ingestion json mapping 'DeviceRegistryEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table DeviceRegistryEventsRaw policy retention softdelete = 1d
.create table DeviceRegistryEvents (Timestamp:datetime,DeviceId:string,DeviceName:string,ActionType:string,RegistryKey:string,RegistryValueType:string,RegistryValueName:string,RegistryValueData:string,PreviousRegistryKey:string,PreviousRegistryValueName:string,PreviousRegistryValueData:string,InitiatingProcessAccountDomain:string,InitiatingProcessAccountName:string,InitiatingProcessAccountSid:string,InitiatingProcessAccountUpn:string,InitiatingProcessAccountObjectId:string,InitiatingProcessSHA1:string,InitiatingProcessSHA256:string,InitiatingProcessMD5:string,InitiatingProcessFileName:string,InitiatingProcessFileSize:long,InitiatingProcessVersionInfoCompanyName:string,InitiatingProcessVersionInfoProductName:string,InitiatingProcessVersionInfoProductVersion:string,InitiatingProcessVersionInfoInternalFileName:string,InitiatingProcessVersionInfoOriginalFileName:string,InitiatingProcessVersionInfoFileDescription:string,InitiatingProcessId:long,InitiatingProcessCommandLine:string,InitiatingProcessCreationTime:datetime,InitiatingProcessFolderPath:string,InitiatingProcessParentId:long,InitiatingProcessParentFileName:string,InitiatingProcessParentCreationTime:datetime,InitiatingProcessIntegrityLevel:string,InitiatingProcessTokenElevation:string,ReportId:long,AppGuardContainerId:string)
.alter-merge table DeviceRegistryEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function DeviceRegistryEventsExpand {DeviceRegistryEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),ActionType = tostring(events.properties.ActionType),RegistryKey = tostring(events.properties.RegistryKey),RegistryValueType = tostring(events.properties.RegistryValueType),RegistryValueName = tostring(events.properties.RegistryValueName),RegistryValueData = tostring(events.properties.RegistryValueData),PreviousRegistryKey = tostring(events.properties.PreviousRegistryKey),PreviousRegistryValueName = tostring(events.properties.PreviousRegistryValueName),PreviousRegistryValueData = tostring(events.properties.PreviousRegistryValueData),InitiatingProcessAccountDomain = tostring(events.properties.InitiatingProcessAccountDomain),InitiatingProcessAccountName = tostring(events.properties.InitiatingProcessAccountName),InitiatingProcessAccountSid = tostring(events.properties.InitiatingProcessAccountSid),InitiatingProcessAccountUpn = tostring(events.properties.InitiatingProcessAccountUpn),InitiatingProcessAccountObjectId = tostring(events.properties.InitiatingProcessAccountObjectId),InitiatingProcessSHA1 = tostring(events.properties.InitiatingProcessSHA1),InitiatingProcessSHA256 = tostring(events.properties.InitiatingProcessSHA256),InitiatingProcessMD5 = tostring(events.properties.InitiatingProcessMD5),InitiatingProcessFileName = tostring(events.properties.InitiatingProcessFileName),InitiatingProcessFileSize = tolong(events.properties.InitiatingProcessFileSize),InitiatingProcessVersionInfoCompanyName = tostring(events.properties.InitiatingProcessVersionInfoCompanyName),InitiatingProcessVersionInfoProductName = tostring(events.properties.InitiatingProcessVersionInfoProductName),InitiatingProcessVersionInfoProductVersion = tostring(events.properties.InitiatingProcessVersionInfoProductVersion),InitiatingProcessVersionInfoInternalFileName = tostring(events.properties.InitiatingProcessVersionInfoInternalFileName),InitiatingProcessVersionInfoOriginalFileName = tostring(events.properties.InitiatingProcessVersionInfoOriginalFileName),InitiatingProcessVersionInfoFileDescription = tostring(events.properties.InitiatingProcessVersionInfoFileDescription),InitiatingProcessId = tolong(events.properties.InitiatingProcessId),InitiatingProcessCommandLine = tostring(events.properties.InitiatingProcessCommandLine),InitiatingProcessCreationTime = todatetime(events.properties.InitiatingProcessCreationTime),InitiatingProcessFolderPath = tostring(events.properties.InitiatingProcessFolderPath),InitiatingProcessParentId = tolong(events.properties.InitiatingProcessParentId),InitiatingProcessParentFileName = tostring(events.properties.InitiatingProcessParentFileName),InitiatingProcessParentCreationTime = todatetime(events.properties.InitiatingProcessParentCreationTime),InitiatingProcessIntegrityLevel = tostring(events.properties.InitiatingProcessIntegrityLevel),InitiatingProcessTokenElevation = tostring(events.properties.InitiatingProcessTokenElevation),ReportId = tolong(events.properties.ReportId),AppGuardContainerId = tostring(events.properties.AppGuardContainerId) }
.alter table DeviceRegistryEvents policy update @'[{"Source": "DeviceRegistryEventsRaw", "Query": "DeviceRegistryEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table DeviceLogonEventsRaw (records:dynamic)
.create-or-alter table DeviceLogonEventsRaw ingestion json mapping 'DeviceLogonEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table DeviceLogonEventsRaw policy retention softdelete = 1d
.create table DeviceLogonEvents (Timestamp:datetime,DeviceId:string,DeviceName:string,ActionType:string,LogonType:string,AccountDomain:string,AccountName:string,AccountSid:string,Protocol:string,FailureReason:string,IsLocalAdmin:bool,LogonId:long,RemoteDeviceName:string,RemoteIP:string,RemoteIPType:string,RemotePort:int,InitiatingProcessAccountDomain:string,InitiatingProcessAccountName:string,InitiatingProcessAccountSid:string,InitiatingProcessAccountUpn:string,InitiatingProcessAccountObjectId:string,InitiatingProcessIntegrityLevel:string,InitiatingProcessTokenElevation:string,InitiatingProcessSHA1:string,InitiatingProcessSHA256:string,InitiatingProcessMD5:string,InitiatingProcessFileName:string,InitiatingProcessFileSize:long,InitiatingProcessVersionInfoCompanyName:string,InitiatingProcessVersionInfoProductName:string,InitiatingProcessVersionInfoProductVersion:string,InitiatingProcessVersionInfoInternalFileName:string,InitiatingProcessVersionInfoOriginalFileName:string,InitiatingProcessVersionInfoFileDescription:string,InitiatingProcessId:long,InitiatingProcessCommandLine:string,InitiatingProcessCreationTime:datetime,InitiatingProcessFolderPath:string,InitiatingProcessParentId:long,InitiatingProcessParentFileName:string,InitiatingProcessParentCreationTime:datetime,ReportId:long,AppGuardContainerId:string,AdditionalFields:string)
.alter-merge table DeviceLogonEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function DeviceLogonEventsExpand {DeviceLogonEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),ActionType = tostring(events.properties.ActionType),LogonType = tostring(events.properties.LogonType),AccountDomain = tostring(events.properties.AccountDomain),AccountName = tostring(events.properties.AccountName),AccountSid = tostring(events.properties.AccountSid),Protocol = tostring(events.properties.Protocol),FailureReason = tostring(events.properties.FailureReason),IsLocalAdmin = tobool(events.properties.IsLocalAdmin),LogonId = tolong(events.properties.LogonId),RemoteDeviceName = tostring(events.properties.RemoteDeviceName),RemoteIP = tostring(events.properties.RemoteIP),RemoteIPType = tostring(events.properties.RemoteIPType),RemotePort = toint(events.properties.RemotePort),InitiatingProcessAccountDomain = tostring(events.properties.InitiatingProcessAccountDomain),InitiatingProcessAccountName = tostring(events.properties.InitiatingProcessAccountName),InitiatingProcessAccountSid = tostring(events.properties.InitiatingProcessAccountSid),InitiatingProcessAccountUpn = tostring(events.properties.InitiatingProcessAccountUpn),InitiatingProcessAccountObjectId = tostring(events.properties.InitiatingProcessAccountObjectId),InitiatingProcessIntegrityLevel = tostring(events.properties.InitiatingProcessIntegrityLevel),InitiatingProcessTokenElevation = tostring(events.properties.InitiatingProcessTokenElevation),InitiatingProcessSHA1 = tostring(events.properties.InitiatingProcessSHA1),InitiatingProcessSHA256 = tostring(events.properties.InitiatingProcessSHA256),InitiatingProcessMD5 = tostring(events.properties.InitiatingProcessMD5),InitiatingProcessFileName = tostring(events.properties.InitiatingProcessFileName),InitiatingProcessFileSize = tolong(events.properties.InitiatingProcessFileSize),InitiatingProcessVersionInfoCompanyName = tostring(events.properties.InitiatingProcessVersionInfoCompanyName),InitiatingProcessVersionInfoProductName = tostring(events.properties.InitiatingProcessVersionInfoProductName),InitiatingProcessVersionInfoProductVersion = tostring(events.properties.InitiatingProcessVersionInfoProductVersion),InitiatingProcessVersionInfoInternalFileName = tostring(events.properties.InitiatingProcessVersionInfoInternalFileName),InitiatingProcessVersionInfoOriginalFileName = tostring(events.properties.InitiatingProcessVersionInfoOriginalFileName),InitiatingProcessVersionInfoFileDescription = tostring(events.properties.InitiatingProcessVersionInfoFileDescription),InitiatingProcessId = tolong(events.properties.InitiatingProcessId),InitiatingProcessCommandLine = tostring(events.properties.InitiatingProcessCommandLine),InitiatingProcessCreationTime = todatetime(events.properties.InitiatingProcessCreationTime),InitiatingProcessFolderPath = tostring(events.properties.InitiatingProcessFolderPath),InitiatingProcessParentId = tolong(events.properties.InitiatingProcessParentId),InitiatingProcessParentFileName = tostring(events.properties.InitiatingProcessParentFileName),InitiatingProcessParentCreationTime = todatetime(events.properties.InitiatingProcessParentCreationTime),ReportId = tolong(events.properties.ReportId),AppGuardContainerId = tostring(events.properties.AppGuardContainerId),AdditionalFields = tostring(events.properties.AdditionalFields) }
.alter table DeviceLogonEvents policy update @'[{"Source": "DeviceLogonEventsRaw", "Query": "DeviceLogonEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table DeviceImageLoadEventsRaw (records:dynamic)
.create-or-alter table DeviceImageLoadEventsRaw ingestion json mapping 'DeviceImageLoadEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table DeviceImageLoadEventsRaw policy retention softdelete = 1d
.create table DeviceImageLoadEvents (Timestamp:datetime,DeviceId:string,DeviceName:string,ActionType:string,FileName:string,FolderPath:string,SHA1:string,SHA256:string,MD5:string,FileSize:long,InitiatingProcessAccountDomain:string,InitiatingProcessAccountName:string,InitiatingProcessAccountSid:string,InitiatingProcessAccountUpn:string,InitiatingProcessAccountObjectId:string,InitiatingProcessIntegrityLevel:string,InitiatingProcessTokenElevation:string,InitiatingProcessSHA1:string,InitiatingProcessSHA256:string,InitiatingProcessMD5:string,InitiatingProcessFileName:string,InitiatingProcessFileSize:long,InitiatingProcessVersionInfoCompanyName:string,InitiatingProcessVersionInfoProductName:string,InitiatingProcessVersionInfoProductVersion:string,InitiatingProcessVersionInfoInternalFileName:string,InitiatingProcessVersionInfoOriginalFileName:string,InitiatingProcessVersionInfoFileDescription:string,InitiatingProcessId:long,InitiatingProcessCommandLine:string,InitiatingProcessCreationTime:datetime,InitiatingProcessFolderPath:string,InitiatingProcessParentId:long,InitiatingProcessParentFileName:string,InitiatingProcessParentCreationTime:datetime,ReportId:long,AppGuardContainerId:string)
.alter-merge table DeviceImageLoadEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function DeviceImageLoadEventsExpand {DeviceImageLoadEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),ActionType = tostring(events.properties.ActionType),FileName = tostring(events.properties.FileName),FolderPath = tostring(events.properties.FolderPath),SHA1 = tostring(events.properties.SHA1),SHA256 = tostring(events.properties.SHA256),MD5 = tostring(events.properties.MD5),FileSize = tolong(events.properties.FileSize),InitiatingProcessAccountDomain = tostring(events.properties.InitiatingProcessAccountDomain),InitiatingProcessAccountName = tostring(events.properties.InitiatingProcessAccountName),InitiatingProcessAccountSid = tostring(events.properties.InitiatingProcessAccountSid),InitiatingProcessAccountUpn = tostring(events.properties.InitiatingProcessAccountUpn),InitiatingProcessAccountObjectId = tostring(events.properties.InitiatingProcessAccountObjectId),InitiatingProcessIntegrityLevel = tostring(events.properties.InitiatingProcessIntegrityLevel),InitiatingProcessTokenElevation = tostring(events.properties.InitiatingProcessTokenElevation),InitiatingProcessSHA1 = tostring(events.properties.InitiatingProcessSHA1),InitiatingProcessSHA256 = tostring(events.properties.InitiatingProcessSHA256),InitiatingProcessMD5 = tostring(events.properties.InitiatingProcessMD5),InitiatingProcessFileName = tostring(events.properties.InitiatingProcessFileName),InitiatingProcessFileSize = tolong(events.properties.InitiatingProcessFileSize),InitiatingProcessVersionInfoCompanyName = tostring(events.properties.InitiatingProcessVersionInfoCompanyName),InitiatingProcessVersionInfoProductName = tostring(events.properties.InitiatingProcessVersionInfoProductName),InitiatingProcessVersionInfoProductVersion = tostring(events.properties.InitiatingProcessVersionInfoProductVersion),InitiatingProcessVersionInfoInternalFileName = tostring(events.properties.InitiatingProcessVersionInfoInternalFileName),InitiatingProcessVersionInfoOriginalFileName = tostring(events.properties.InitiatingProcessVersionInfoOriginalFileName),InitiatingProcessVersionInfoFileDescription = tostring(events.properties.InitiatingProcessVersionInfoFileDescription),InitiatingProcessId = tolong(events.properties.InitiatingProcessId),InitiatingProcessCommandLine = tostring(events.properties.InitiatingProcessCommandLine),InitiatingProcessCreationTime = todatetime(events.properties.InitiatingProcessCreationTime),InitiatingProcessFolderPath = tostring(events.properties.InitiatingProcessFolderPath),InitiatingProcessParentId = tolong(events.properties.InitiatingProcessParentId),InitiatingProcessParentFileName = tostring(events.properties.InitiatingProcessParentFileName),InitiatingProcessParentCreationTime = todatetime(events.properties.InitiatingProcessParentCreationTime),ReportId = tolong(events.properties.ReportId),AppGuardContainerId = tostring(events.properties.AppGuardContainerId) }
.alter table DeviceImageLoadEvents policy update @'[{"Source": "DeviceImageLoadEventsRaw", "Query": "DeviceImageLoadEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table DeviceEventsRaw (records:dynamic)
.create-or-alter table DeviceEventsRaw ingestion json mapping 'DeviceEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table DeviceEventsRaw policy retention softdelete = 1d
.create table DeviceEvents (Timestamp:datetime,DeviceId:string,DeviceName:string,ActionType:string,FileName:string,FolderPath:string,SHA1:string,SHA256:string,MD5:string,FileSize:long,AccountDomain:string,AccountName:string,AccountSid:string,RemoteUrl:string,RemoteDeviceName:string,ProcessId:long,ProcessCommandLine:string,ProcessCreationTime:datetime,ProcessTokenElevation:string,LogonId:long,RegistryKey:string,RegistryValueName:string,RegistryValueData:string,RemoteIP:string,RemotePort:int,LocalIP:string,LocalPort:int,FileOriginUrl:string,FileOriginIP:string,InitiatingProcessSHA1:string,InitiatingProcessSHA256:string,InitiatingProcessMD5:string,InitiatingProcessFileName:string,InitiatingProcessFileSize:long,InitiatingProcessFolderPath:string,InitiatingProcessId:long,InitiatingProcessCommandLine:string,InitiatingProcessCreationTime:datetime,InitiatingProcessAccountDomain:string,InitiatingProcessAccountName:string,InitiatingProcessAccountSid:string,InitiatingProcessAccountUpn:string,InitiatingProcessAccountObjectId:string,InitiatingProcessVersionInfoCompanyName:string,InitiatingProcessVersionInfoProductName:string,InitiatingProcessVersionInfoProductVersion:string,InitiatingProcessVersionInfoInternalFileName:string,InitiatingProcessVersionInfoOriginalFileName:string,InitiatingProcessVersionInfoFileDescription:string,InitiatingProcessParentId:long,InitiatingProcessParentFileName:string,InitiatingProcessParentCreationTime:datetime,InitiatingProcessLogonId:long,ReportId:long,AppGuardContainerId:string,AdditionalFields:string)
.alter-merge table DeviceEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function DeviceEventsExpand {DeviceEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),ActionType = tostring(events.properties.ActionType),FileName = tostring(events.properties.FileName),FolderPath = tostring(events.properties.FolderPath),SHA1 = tostring(events.properties.SHA1),SHA256 = tostring(events.properties.SHA256),MD5 = tostring(events.properties.MD5),FileSize = tolong(events.properties.FileSize),AccountDomain = tostring(events.properties.AccountDomain),AccountName = tostring(events.properties.AccountName),AccountSid = tostring(events.properties.AccountSid),RemoteUrl = tostring(events.properties.RemoteUrl),RemoteDeviceName = tostring(events.properties.RemoteDeviceName),ProcessId = tolong(events.properties.ProcessId),ProcessCommandLine = tostring(events.properties.ProcessCommandLine),ProcessCreationTime = todatetime(events.properties.ProcessCreationTime),ProcessTokenElevation = tostring(events.properties.ProcessTokenElevation),LogonId = tolong(events.properties.LogonId),RegistryKey = tostring(events.properties.RegistryKey),RegistryValueName = tostring(events.properties.RegistryValueName),RegistryValueData = tostring(events.properties.RegistryValueData),RemoteIP = tostring(events.properties.RemoteIP),RemotePort = toint(events.properties.RemotePort),LocalIP = tostring(events.properties.LocalIP),LocalPort = toint(events.properties.LocalPort),FileOriginUrl = tostring(events.properties.FileOriginUrl),FileOriginIP = tostring(events.properties.FileOriginIP),InitiatingProcessSHA1 = tostring(events.properties.InitiatingProcessSHA1),InitiatingProcessSHA256 = tostring(events.properties.InitiatingProcessSHA256),InitiatingProcessMD5 = tostring(events.properties.InitiatingProcessMD5),InitiatingProcessFileName = tostring(events.properties.InitiatingProcessFileName),InitiatingProcessFileSize = tolong(events.properties.InitiatingProcessFileSize),InitiatingProcessFolderPath = tostring(events.properties.InitiatingProcessFolderPath),InitiatingProcessId = tolong(events.properties.InitiatingProcessId),InitiatingProcessCommandLine = tostring(events.properties.InitiatingProcessCommandLine),InitiatingProcessCreationTime = todatetime(events.properties.InitiatingProcessCreationTime),InitiatingProcessAccountDomain = tostring(events.properties.InitiatingProcessAccountDomain),InitiatingProcessAccountName = tostring(events.properties.InitiatingProcessAccountName),InitiatingProcessAccountSid = tostring(events.properties.InitiatingProcessAccountSid),InitiatingProcessAccountUpn = tostring(events.properties.InitiatingProcessAccountUpn),InitiatingProcessAccountObjectId = tostring(events.properties.InitiatingProcessAccountObjectId),InitiatingProcessVersionInfoCompanyName = tostring(events.properties.InitiatingProcessVersionInfoCompanyName),InitiatingProcessVersionInfoProductName = tostring(events.properties.InitiatingProcessVersionInfoProductName),InitiatingProcessVersionInfoProductVersion = tostring(events.properties.InitiatingProcessVersionInfoProductVersion),InitiatingProcessVersionInfoInternalFileName = tostring(events.properties.InitiatingProcessVersionInfoInternalFileName),InitiatingProcessVersionInfoOriginalFileName = tostring(events.properties.InitiatingProcessVersionInfoOriginalFileName),InitiatingProcessVersionInfoFileDescription = tostring(events.properties.InitiatingProcessVersionInfoFileDescription),InitiatingProcessParentId = tolong(events.properties.InitiatingProcessParentId),InitiatingProcessParentFileName = tostring(events.properties.InitiatingProcessParentFileName),InitiatingProcessParentCreationTime = todatetime(events.properties.InitiatingProcessParentCreationTime),InitiatingProcessLogonId = tolong(events.properties.InitiatingProcessLogonId),ReportId = tolong(events.properties.ReportId),AppGuardContainerId = tostring(events.properties.AppGuardContainerId),AdditionalFields = tostring(events.properties.AdditionalFields) }
.alter table DeviceEvents policy update @'[{"Source": "DeviceEventsRaw", "Query": "DeviceEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table DeviceFileCertificateInfoRaw (records:dynamic)
.create-or-alter table DeviceFileCertificateInfoRaw ingestion json mapping 'DeviceFileCertificateInfoRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table DeviceFileCertificateInfoRaw policy retention softdelete = 1d
.create table DeviceFileCertificateInfo (Timestamp:datetime,DeviceId:string,DeviceName:string,SHA1:string,IsSigned:bool,SignatureType:string,Signer:string,SignerHash:string,Issuer:string,IssuerHash:string,CertificateSerialNumber:string,CrlDistributionPointUrls:string,CertificateCreationTime:datetime,CertificateExpirationTime:datetime,CertificateCountersignatureTime:datetime,IsTrusted:bool,IsRootSignerMicrosoft:bool,ReportId:long)
.alter-merge table DeviceFileCertificateInfo policy retention softdelete = 365d recoverability = enabled
.create-or-alter function DeviceFileCertificateInfoExpand {DeviceFileCertificateInfoRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),DeviceId = tostring(events.properties.DeviceId),DeviceName = tostring(events.properties.DeviceName),SHA1 = tostring(events.properties.SHA1),IsSigned = tobool(events.properties.IsSigned),SignatureType = tostring(events.properties.SignatureType),Signer = tostring(events.properties.Signer),SignerHash = tostring(events.properties.SignerHash),Issuer = tostring(events.properties.Issuer),IssuerHash = tostring(events.properties.IssuerHash),CertificateSerialNumber = tostring(events.properties.CertificateSerialNumber),CrlDistributionPointUrls = tostring(events.properties.CrlDistributionPointUrls),CertificateCreationTime = todatetime(events.properties.CertificateCreationTime),CertificateExpirationTime = todatetime(events.properties.CertificateExpirationTime),CertificateCountersignatureTime = todatetime(events.properties.CertificateCountersignatureTime),IsTrusted = tobool(events.properties.IsTrusted),IsRootSignerMicrosoft = tobool(events.properties.IsRootSignerMicrosoft),ReportId = tolong(events.properties.ReportId) }
.alter table DeviceFileCertificateInfo policy update @'[{"Source": "DeviceFileCertificateInfoRaw", "Query": "DeviceFileCertificateInfoExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table EmailAttachmentInfoRaw (records:dynamic)
.create-or-alter table EmailAttachmentInfoRaw ingestion json mapping 'EmailAttachmentInfoRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table EmailAttachmentInfoRaw policy retention softdelete = 1d
.create table EmailAttachmentInfo (Timestamp:datetime,NetworkMessageId:string,SenderFromAddress:string,SenderDisplayName:string,SenderObjectId:string,RecipientEmailAddress:string,RecipientObjectId:string,FileName:string,FileType:string,SHA256:string,FileSize:long,ThreatTypes:string,ThreatNames:string,DetectionMethods:string,ReportId:string)
.alter-merge table EmailAttachmentInfo policy retention softdelete = 365d recoverability = enabled
.create-or-alter function EmailAttachmentInfoExpand {EmailAttachmentInfoRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),NetworkMessageId = tostring(events.properties.NetworkMessageId),SenderFromAddress = tostring(events.properties.SenderFromAddress),SenderDisplayName = tostring(events.properties.SenderDisplayName),SenderObjectId = tostring(events.properties.SenderObjectId),RecipientEmailAddress = tostring(events.properties.RecipientEmailAddress),RecipientObjectId = tostring(events.properties.RecipientObjectId),FileName = tostring(events.properties.FileName),FileType = tostring(events.properties.FileType),SHA256 = tostring(events.properties.SHA256),FileSize = tolong(events.properties.FileSize),ThreatTypes = tostring(events.properties.ThreatTypes),ThreatNames = tostring(events.properties.ThreatNames),DetectionMethods = tostring(events.properties.DetectionMethods),ReportId = tostring(events.properties.ReportId) }
.alter table EmailAttachmentInfo policy update @'[{"Source": "EmailAttachmentInfoRaw", "Query": "EmailAttachmentInfoExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table EmailEventsRaw (records:dynamic)
.create-or-alter table EmailEventsRaw ingestion json mapping 'EmailEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table EmailEventsRaw policy retention softdelete = 1d
.create table EmailEvents (Timestamp:datetime,NetworkMessageId:string,InternetMessageId:string,SenderMailFromAddress:string,SenderFromAddress:string,SenderDisplayName:string,SenderObjectId:string,SenderMailFromDomain:string,SenderFromDomain:string,SenderIPv4:string,SenderIPv6:string,RecipientEmailAddress:string,RecipientObjectId:string,Subject:string,EmailClusterId:long,EmailDirection:string,DeliveryAction:string,DeliveryLocation:string,ThreatTypes:string,ThreatNames:string,DetectionMethods:string,ConfidenceLevel:string,BulkComplaintLevel:int,EmailAction:string,EmailActionPolicy:string,EmailActionPolicyGuid:string,AuthenticationDetails:string,AttachmentCount:int,UrlCount:int,EmailLanguage:string,Connectors:string,OrgLevelAction:string,OrgLevelPolicy:string,UserLevelAction:string,UserLevelPolicy:string,ReportId:string,AdditionalFields:string,LatestDeliveryLocation:string,LatestDeliveryAction:string)
.alter-merge table EmailEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function EmailEventsExpand {EmailEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),NetworkMessageId = tostring(events.properties.NetworkMessageId),InternetMessageId = tostring(events.properties.InternetMessageId),SenderMailFromAddress = tostring(events.properties.SenderMailFromAddress),SenderFromAddress = tostring(events.properties.SenderFromAddress),SenderDisplayName = tostring(events.properties.SenderDisplayName),SenderObjectId = tostring(events.properties.SenderObjectId),SenderMailFromDomain = tostring(events.properties.SenderMailFromDomain),SenderFromDomain = tostring(events.properties.SenderFromDomain),SenderIPv4 = tostring(events.properties.SenderIPv4),SenderIPv6 = tostring(events.properties.SenderIPv6),RecipientEmailAddress = tostring(events.properties.RecipientEmailAddress),RecipientObjectId = tostring(events.properties.RecipientObjectId),Subject = tostring(events.properties.Subject),EmailClusterId = tolong(events.properties.EmailClusterId),EmailDirection = tostring(events.properties.EmailDirection),DeliveryAction = tostring(events.properties.DeliveryAction),DeliveryLocation = tostring(events.properties.DeliveryLocation),ThreatTypes = tostring(events.properties.ThreatTypes),ThreatNames = tostring(events.properties.ThreatNames),DetectionMethods = tostring(events.properties.DetectionMethods),ConfidenceLevel = tostring(events.properties.ConfidenceLevel),BulkComplaintLevel = toint(events.properties.BulkComplaintLevel),EmailAction = tostring(events.properties.EmailAction),EmailActionPolicy = tostring(events.properties.EmailActionPolicy),EmailActionPolicyGuid = tostring(events.properties.EmailActionPolicyGuid),AuthenticationDetails = tostring(events.properties.AuthenticationDetails),AttachmentCount = toint(events.properties.AttachmentCount),UrlCount = toint(events.properties.UrlCount),EmailLanguage = tostring(events.properties.EmailLanguage),Connectors = tostring(events.properties.Connectors),OrgLevelAction = tostring(events.properties.OrgLevelAction),OrgLevelPolicy = tostring(events.properties.OrgLevelPolicy),UserLevelAction = tostring(events.properties.UserLevelAction),UserLevelPolicy = tostring(events.properties.UserLevelPolicy),ReportId = tostring(events.properties.ReportId),AdditionalFields = tostring(events.properties.AdditionalFields),LatestDeliveryLocation = tostring(events.properties.LatestDeliveryLocation),LatestDeliveryAction = tostring(events.properties.LatestDeliveryAction) }
.alter table EmailEvents policy update @'[{"Source": "EmailEventsRaw", "Query": "EmailEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table EmailPostDeliveryEventsRaw (records:dynamic)
.create-or-alter table EmailPostDeliveryEventsRaw ingestion json mapping 'EmailPostDeliveryEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table EmailPostDeliveryEventsRaw policy retention softdelete = 1d
.create table EmailPostDeliveryEvents (Timestamp:datetime,NetworkMessageId:string,InternetMessageId:string,Action:string,ActionType:string,ActionTrigger:string,ActionResult:string,RecipientEmailAddress:string,DeliveryLocation:string,ThreatTypes:string,DetectionMethods:string,ReportId:string)
.alter-merge table EmailPostDeliveryEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function EmailPostDeliveryEventsExpand {EmailPostDeliveryEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),NetworkMessageId = tostring(events.properties.NetworkMessageId),InternetMessageId = tostring(events.properties.InternetMessageId),Action = tostring(events.properties.Action),ActionType = tostring(events.properties.ActionType),ActionTrigger = tostring(events.properties.ActionTrigger),ActionResult = tostring(events.properties.ActionResult),RecipientEmailAddress = tostring(events.properties.RecipientEmailAddress),DeliveryLocation = tostring(events.properties.DeliveryLocation),ThreatTypes = tostring(events.properties.ThreatTypes),DetectionMethods = tostring(events.properties.DetectionMethods),ReportId = tostring(events.properties.ReportId) }
.alter table EmailPostDeliveryEvents policy update @'[{"Source": "EmailPostDeliveryEventsRaw", "Query": "EmailPostDeliveryEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table EmailUrlInfoRaw (records:dynamic)
.create-or-alter table EmailUrlInfoRaw ingestion json mapping 'EmailUrlInfoRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table EmailUrlInfoRaw policy retention softdelete = 1d
.create table EmailUrlInfo (Timestamp:datetime,NetworkMessageId:string,Url:string,UrlDomain:string,UrlLocation:string,ReportId:string)
.alter-merge table EmailUrlInfo policy retention softdelete = 365d recoverability = enabled
.create-or-alter function EmailUrlInfoExpand {EmailUrlInfoRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),NetworkMessageId = tostring(events.properties.NetworkMessageId),Url = tostring(events.properties.Url),UrlDomain = tostring(events.properties.UrlDomain),UrlLocation = tostring(events.properties.UrlLocation),ReportId = tostring(events.properties.ReportId) }
.alter table EmailUrlInfo policy update @'[{"Source": "EmailUrlInfoRaw", "Query": "EmailUrlInfoExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table UrlClickEventsRaw (records:dynamic)
.create-or-alter table UrlClickEventsRaw ingestion json mapping 'UrlClickEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table UrlClickEventsRaw policy retention softdelete = 1d
.create table UrlClickEvents (Timestamp:datetime,Url:string,ActionType:string,AccountUpn:string,Workload:string,NetworkMessageId:string,ThreatTypes:string,DetectionMethods:string,IPAddress:string,IsClickedThrough:bool,UrlChain:string,ReportId:string)
.alter-merge table UrlClickEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function UrlClickEventsExpand {UrlClickEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),Url = tostring(events.properties.Url),ActionType = tostring(events.properties.ActionType),AccountUpn = tostring(events.properties.AccountUpn),Workload = tostring(events.properties.Workload),NetworkMessageId = tostring(events.properties.NetworkMessageId),ThreatTypes = tostring(events.properties.ThreatTypes),DetectionMethods = tostring(events.properties.DetectionMethods),IPAddress = tostring(events.properties.IPAddress),IsClickedThrough = tobool(events.properties.IsClickedThrough),UrlChain = tostring(events.properties.UrlChain),ReportId = tostring(events.properties.ReportId) }
.alter table UrlClickEvents policy update @'[{"Source": "UrlClickEventsRaw", "Query": "UrlClickEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table IdentityLogonEventsRaw (records:dynamic)
.create-or-alter table IdentityLogonEventsRaw ingestion json mapping 'IdentityLogonEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table IdentityLogonEventsRaw policy retention softdelete = 1d
.create table IdentityLogonEvents (Timestamp:datetime,ActionType:string,Application:string,LogonType:string,Protocol:string,FailureReason:string,AccountName:string,AccountDomain:string,AccountUpn:string,AccountSid:string,AccountObjectId:string,AccountDisplayName:string,DeviceName:string,DeviceType:string,OSPlatform:string,IPAddress:string,Port:int,DestinationDeviceName:string,DestinationIPAddress:string,DestinationPort:int,TargetDeviceName:string,TargetAccountDisplayName:string,Location:string,ISP:string,ReportId:string,AdditionalFields:dynamic)
.alter-merge table IdentityLogonEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function IdentityLogonEventsExpand {IdentityLogonEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),ActionType = tostring(events.properties.ActionType),Application = tostring(events.properties.Application),LogonType = tostring(events.properties.LogonType),Protocol = tostring(events.properties.Protocol),FailureReason = tostring(events.properties.FailureReason),AccountName = tostring(events.properties.AccountName),AccountDomain = tostring(events.properties.AccountDomain),AccountUpn = tostring(events.properties.AccountUpn),AccountSid = tostring(events.properties.AccountSid),AccountObjectId = tostring(events.properties.AccountObjectId),AccountDisplayName = tostring(events.properties.AccountDisplayName),DeviceName = tostring(events.properties.DeviceName),DeviceType = tostring(events.properties.DeviceType),OSPlatform = tostring(events.properties.OSPlatform),IPAddress = tostring(events.properties.IPAddress),Port = toint(events.properties.Port),DestinationDeviceName = tostring(events.properties.DestinationDeviceName),DestinationIPAddress = tostring(events.properties.DestinationIPAddress),DestinationPort = toint(events.properties.DestinationPort),TargetDeviceName = tostring(events.properties.TargetDeviceName),TargetAccountDisplayName = tostring(events.properties.TargetAccountDisplayName),Location = tostring(events.properties.Location),ISP = tostring(events.properties.ISP),ReportId = tostring(events.properties.ReportId),AdditionalFields = todynamic(events.properties.AdditionalFields) }
.alter table IdentityLogonEvents policy update @'[{"Source": "IdentityLogonEventsRaw", "Query": "IdentityLogonEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table IdentityQueryEventsRaw (records:dynamic)
.create-or-alter table IdentityQueryEventsRaw ingestion json mapping 'IdentityQueryEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table IdentityQueryEventsRaw policy retention softdelete = 1d
.create table IdentityQueryEvents (Timestamp:datetime,ActionType:string,Application:string,QueryType:string,QueryTarget:string,Query:string,Protocol:string,AccountName:string,AccountDomain:string,AccountUpn:string,AccountSid:string,AccountObjectId:string,AccountDisplayName:string,DeviceName:string,IPAddress:string,Port:int,DestinationDeviceName:string,DestinationIPAddress:string,DestinationPort:int,TargetDeviceName:string,TargetAccountUpn:string,TargetAccountDisplayName:string,Location:string,ReportId:string,AdditionalFields:dynamic)
.alter-merge table IdentityQueryEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function IdentityQueryEventsExpand {IdentityQueryEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),ActionType = tostring(events.properties.ActionType),Application = tostring(events.properties.Application),QueryType = tostring(events.properties.QueryType),QueryTarget = tostring(events.properties.QueryTarget),Query = tostring(events.properties.Query),Protocol = tostring(events.properties.Protocol),AccountName = tostring(events.properties.AccountName),AccountDomain = tostring(events.properties.AccountDomain),AccountUpn = tostring(events.properties.AccountUpn),AccountSid = tostring(events.properties.AccountSid),AccountObjectId = tostring(events.properties.AccountObjectId),AccountDisplayName = tostring(events.properties.AccountDisplayName),DeviceName = tostring(events.properties.DeviceName),IPAddress = tostring(events.properties.IPAddress),Port = toint(events.properties.Port),DestinationDeviceName = tostring(events.properties.DestinationDeviceName),DestinationIPAddress = tostring(events.properties.DestinationIPAddress),DestinationPort = toint(events.properties.DestinationPort),TargetDeviceName = tostring(events.properties.TargetDeviceName),TargetAccountUpn = tostring(events.properties.TargetAccountUpn),TargetAccountDisplayName = tostring(events.properties.TargetAccountDisplayName),Location = tostring(events.properties.Location),ReportId = tostring(events.properties.ReportId),AdditionalFields = todynamic(events.properties.AdditionalFields) }
.alter table IdentityQueryEvents policy update @'[{"Source": "IdentityQueryEventsRaw", "Query": "IdentityQueryEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table IdentityDirectoryEventsRaw (records:dynamic)
.create-or-alter table IdentityDirectoryEventsRaw ingestion json mapping 'IdentityDirectoryEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table IdentityDirectoryEventsRaw policy retention softdelete = 1d
.create table IdentityDirectoryEvents (Timestamp:datetime,ActionType:string,Application:string,TargetAccountUpn:string,TargetAccountDisplayName:string,TargetDeviceName:string,DestinationDeviceName:string,DestinationIPAddress:string,DestinationPort:int,Protocol:string,AccountName:string,AccountDomain:string,AccountUpn:string,AccountSid:string,AccountObjectId:string,AccountDisplayName:string,DeviceName:string,IPAddress:string,Port:int,Location:string,ISP:string,ReportId:string,AdditionalFields:dynamic)
.alter-merge table IdentityDirectoryEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function IdentityDirectoryEventsExpand {IdentityDirectoryEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),ActionType = tostring(events.properties.ActionType),Application = tostring(events.properties.Application),TargetAccountUpn = tostring(events.properties.TargetAccountUpn),TargetAccountDisplayName = tostring(events.properties.TargetAccountDisplayName),TargetDeviceName = tostring(events.properties.TargetDeviceName),DestinationDeviceName = tostring(events.properties.DestinationDeviceName),DestinationIPAddress = tostring(events.properties.DestinationIPAddress),DestinationPort = toint(events.properties.DestinationPort),Protocol = tostring(events.properties.Protocol),AccountName = tostring(events.properties.AccountName),AccountDomain = tostring(events.properties.AccountDomain),AccountUpn = tostring(events.properties.AccountUpn),AccountSid = tostring(events.properties.AccountSid),AccountObjectId = tostring(events.properties.AccountObjectId),AccountDisplayName = tostring(events.properties.AccountDisplayName),DeviceName = tostring(events.properties.DeviceName),IPAddress = tostring(events.properties.IPAddress),Port = toint(events.properties.Port),Location = tostring(events.properties.Location),ISP = tostring(events.properties.ISP),ReportId = tostring(events.properties.ReportId),AdditionalFields = todynamic(events.properties.AdditionalFields) }
.alter table IdentityDirectoryEvents policy update @'[{"Source": "IdentityDirectoryEventsRaw", "Query": "IdentityDirectoryEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'
.create table CloudAppEventsRaw (records:dynamic)
.create-or-alter table CloudAppEventsRaw ingestion json mapping 'CloudAppEventsRawMapping' '[{"Column":"records","Properties":{"path":"$.records"}}]'
.alter-merge table CloudAppEventsRaw policy retention softdelete = 1d
.create table CloudAppEvents (Timestamp:datetime,ActionType:string,Application:string,ApplicationId:int,AppInstanceId:int,AccountObjectId:string,AccountId:string,AccountDisplayName:string,IsAdminOperation:bool,DeviceType:string,OSPlatform:string,IPAddress:string,IsAnonymousProxy:bool,CountryCode:string,City:string,ISP:string,UserAgent:string,ActivityType:string,ActivityObjects:dynamic,ObjectName:string,ObjectType:string,ObjectId:string,ReportId:string,AccountType:string,IsExternalUser:bool,IsImpersonated:bool,IPTags:dynamic,IPCategory:string,UserAgentTags:dynamic,RawEventData:dynamic,AdditionalFields:dynamic)
.alter-merge table CloudAppEvents policy retention softdelete = 365d recoverability = enabled
.create-or-alter function CloudAppEventsExpand {CloudAppEventsRaw | mv-expand events = records | project Timestamp = todatetime(events.properties.Timestamp),ActionType = tostring(events.properties.ActionType),Application = tostring(events.properties.Application),ApplicationId = toint(events.properties.ApplicationId),AppInstanceId = toint(events.properties.AppInstanceId),AccountObjectId = tostring(events.properties.AccountObjectId),AccountId = tostring(events.properties.AccountId),AccountDisplayName = tostring(events.properties.AccountDisplayName),IsAdminOperation = tobool(events.properties.IsAdminOperation),DeviceType = tostring(events.properties.DeviceType),OSPlatform = tostring(events.properties.OSPlatform),IPAddress = tostring(events.properties.IPAddress),IsAnonymousProxy = tobool(events.properties.IsAnonymousProxy),CountryCode = tostring(events.properties.CountryCode),City = tostring(events.properties.City),ISP = tostring(events.properties.ISP),UserAgent = tostring(events.properties.UserAgent),ActivityType = tostring(events.properties.ActivityType),ActivityObjects = todynamic(events.properties.ActivityObjects),ObjectName = tostring(events.properties.ObjectName),ObjectType = tostring(events.properties.ObjectType),ObjectId = tostring(events.properties.ObjectId),ReportId = tostring(events.properties.ReportId),AccountType = tostring(events.properties.AccountType),IsExternalUser = tobool(events.properties.IsExternalUser),IsImpersonated = tobool(events.properties.IsImpersonated),IPTags = todynamic(events.properties.IPTags),IPCategory = tostring(events.properties.IPCategory),UserAgentTags = todynamic(events.properties.UserAgentTags),RawEventData = todynamic(events.properties.RawEventData),AdditionalFields = todynamic(events.properties.AdditionalFields) }
.alter table CloudAppEvents policy update @'[{"Source": "CloudAppEventsRaw", "Query": "CloudAppEventsExpand()", "IsEnabled": "True", "IsTransactional": true}]'