-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathvnetflowlogparser.txt
64 lines (54 loc) · 14.4 KB
/
vnetflowlogparser.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
let
Source = AzureStorage.Blobs(StorageAccountName),
#"insights-logsneh-flowlogflowevent1" = Source{[Name="insights-logs-flowlogflowevent"]}[Data],
#"Sorted Rows" = Table.Sort(#"insights-logsneh-flowlogflowevent1",{{"Date modified", Order.Ascending}}),
#"Kept First Rows" = Table.LastN(#"Sorted Rows",NumberOfLogFiles),
#"Added Custom" = Table.AddColumn(#"Kept First Rows", "Custom", each Query1([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom", {"records"}, {"Custom.records"}),
#"Expanded Custom.records" = Table.ExpandListColumn(#"Expanded Custom", "Custom.records"),
#"Expanded Custom.records1" = Table.ExpandRecordColumn(#"Expanded Custom.records", "Custom.records", {"time", "flowLogVersion", "flowLogGUID", "macAddress", "category", "flowLogResourceID","targetResourceID","operationName","flowRecords"}, {"Custom.records.time", "Custom.records.flowLogVersion", "Custom.records.flowLogGUID", "Custom.records.macAddress", "Custom.records.category", "Custom.records.flowLogResourceID","Custom.records.targetResourceID","Custom.records.operationName","Custom.records.flowrecords"}),
#"Expanded Custom.records.flowrecords" = Table.ExpandRecordColumn(#"Expanded Custom.records1", "Custom.records.flowrecords", {"flows"},{"Custom.records.flowrecords.flows"}),
#"Expanded Custom.records.flowrecords.flows" = Table.ExpandListColumn(#"Expanded Custom.records.flowrecords", "Custom.records.flowrecords.flows"),
#"Expanded Custom.records.flowrecords.flows1" = Table.ExpandRecordColumn(#"Expanded Custom.records.flowrecords.flows", "Custom.records.flowrecords.flows", {"aclID", "flowGroups"}, {"Custom.records.flowrecords.flows.aclID", "Custom.records.flowrecords.flows.flowgroups"}),
#"Expanded Custom.records.flowrecords.flows.flowgroups" = Table.ExpandListColumn(#"Expanded Custom.records.flowrecords.flows1", "Custom.records.flowrecords.flows.flowgroups"),
#"Expanded Custom.records.flowrecords.flows.flowgroups1" = Table.ExpandRecordColumn(#"Expanded Custom.records.flowrecords.flows.flowgroups", "Custom.records.flowrecords.flows.flowgroups", {"rule", "flowTuples"}, {"Custom.records.flowrecords.flows.flowgroups.rule", "Custom.records.flowrecords.flows.flowgroups.flowtuples"}),
#"Expanded Custom.records.flowrecords.flows.flowgroups.flowtuples" = Table.ExpandListColumn(#"Expanded Custom.records.flowrecords.flows.flowgroups1", "Custom.records.flowrecords.flows.flowgroups.flowtuples"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom.records.flowrecords.flows.flowgroups.flowtuples","Custom.records.flowrecords.flows.flowgroups.flowtuples",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Custom.records.flowrecords.flows.flowgroups.flowtuples.1", "Custom.records.flowrecords.flows.flowgroups.flowtuples.2", "Custom.records.flowrecords.flows.flowgroups.flowtuples.3", "Custom.records.flowrecords.flows.flowgroups.flowtuples.4", "Custom.records.flowrecords.flows.flowgroups.flowtuples.5", "Custom.records.flowrecords.flows.flowgroups.flowtuples.6", "Custom.records.flowrecords.flows.flowgroups.flowtuples.7", "Custom.records.flowrecords.flows.flowgroups.flowtuples.8","Custom.records.flowrecords.flows.flowgroups.flowtuples.9","Custom.records.flowrecords.flows.flowgroups.flowtuples.10","Custom.records.flowrecords.flows.flowgroups.flowtuples.11","Custom.records.flowrecords.flows.flowgroups.flowtuples.12","Custom.records.flowrecords.flows.flowgroups.flowtuples.13"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.records.flowrecords.flows.flowgroups.flowtuples.1", type number}, {"Custom.records.flowrecords.flows.flowgroups.flowtuples.2", type text}, {"Custom.records.flowrecords.flows.flowgroups.flowtuples.3", type text},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.4", type number}, {"Custom.records.flowrecords.flows.flowgroups.flowtuples.5", type number}, {"Custom.records.flowrecords.flows.flowgroups.flowtuples.6", type number}, {"Custom.records.flowrecords.flows.flowgroups.flowtuples.7", type text}, {"Custom.records.flowrecords.flows.flowgroups.flowtuples.8", type text},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.9", type text},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.10", type number},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.11", type number},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.12", type number},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.13", type number}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type","Custom.records.targetResourceID",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"Custom.records.targetResourceID.1","Custom.records.targetResourceID.2","Custom.records.targetResourceID.3","Custom.records.targetResourceID.4","Custom.records.targetResourceID.5","Custom.records.targetResourceID.6","Custom.records.targetResourceID.7","Custom.records.targetResourceID.8","Custom.records.targetResourceID.9"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.records.targetResourceID.5",type text},{"Custom.records.targetResourceID.9", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom.records.category","Custom.records.operationName","Custom.records.targetResourceID.1","Custom.records.targetResourceID.2","Custom.records.targetResourceID.3","Custom.records.targetResourceID.4","Custom.records.targetResourceID.6","Custom.records.targetResourceID.7","Custom.records.targetResourceID.8"}),
#"Renamed Columns" = Table.RenameColumns( #"Removed Columns",{{"Custom.records.time","LogTime"},{"Custom.records.flowLogVersion","FlowLogVersion"},{"Custom.records.macAddress","MacAddress"},{"Custom.records.flowLogResourceID","FlowLogResourceID"},{"Custom.records.flowrecords.flows.aclID","aclID"},{"Custom.records.flowrecords.flows.flowgroups.rule","Rule"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.1","FlowTime"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.2","SourceIP"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.3","DestinationIP"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.4","SourcePort"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.5","DestinationPort"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.6","Protocol"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.7","FlowDirection"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.8","FlowState"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.9","FlowEncryption"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.10","PacketsSent"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.11","BytesSent"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.12","PacketsReceived"},{"Custom.records.flowrecords.flows.flowgroups.flowtuples.13","BytesReceived"},{"Custom.records.targetResourceID.5", "VNET Resourcegroup"},{"Custom.records.targetResourceID.9","VNET Name"}})
in
#"Renamed Columns"
{"Custom.records.targetResourceID.2", type text},{"Custom.records.targetResourceID.3", type text},{"Custom.records.targetResourceID.4", type text},{"Custom.records.targetResourceID.5", type text},{"Custom.records.targetResourceID.6", type text},{"Custom.records.targetResourceID.7", type text},{"Custom.records.targetResourceID.8", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type","Custom.records.flowLogResourceID",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"Custom.records.flowLogResourceID.1","Custom.records.flowLogResourceID.2","Custom.records.flowLogResourceID.3","Custom.records.flowLogResourceID.4","Custom.records.flowLogResourceID.5","Custom.records.flowLogResourceID.6","Custom.records.flowLogResourceID.7","Custom.records.flowLogResourceID.8","Custom.records.flowLogResourceID.9"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.records.flowLogResourceID.1", type text},{"Custom.records.flowLogResourceID.2", type text},{"Custom.records.flowLogResourceID.3", type text},{"Custom.records.flowLogResourceID.4", type text},{"Custom.records.flowLogResourceID.5", type text},{"Custom.records.flowLogResourceID.6", type text},{"Custom.records.flowLogResourceID.7", type text},{"Custom.records.flowLogResourceID.8", type text},{"Custom.records.flowLogResourceID.9", type text}})
let
Source = AzureStorage.Blobs(StorageAccountName),
#"insights-logsneh-networksecuritygroupflowevent1" = Source{[Name="insights-logs-networksecuritygroupflowevent"]}[Data],
#"Sorted Rows" = Table.Sort(#"insights-logsneh-networksecuritygroupflowevent1",{{"Date modified", Order.Ascending}}),
#"Kept First Rows" = Table.LastN(#"Sorted Rows",NumberOfLogFiles),
#"Added Custom" = Table.AddColumn(#"Kept First Rows", "Custom", each Query1([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom", {"records"}, {"Custom.records"}),
#"Expanded Custom.records" = Table.ExpandListColumn(#"Expanded Custom", "Custom.records"),
#"Expanded Custom.records1" = Table.ExpandRecordColumn(#"Expanded Custom.records", "Custom.records", {"time", "systemId", "category", "resourceId", "operationName", "properties"}, {"Custom.records.time", "Custom.records.systemId", "Custom.records.category", "Custom.records.resourceId", "Custom.records.operationName", "Custom.records.properties"}),
#"Expanded Custom.records.properties" = Table.ExpandRecordColumn(#"Expanded Custom.records1", "Custom.records.properties", {"Version", "flows"}, {"Custom.records.properties.Version", "Custom.records.properties.flows"}),
#"Expanded Custom.records.properties.flows" = Table.ExpandListColumn(#"Expanded Custom.records.properties", "Custom.records.properties.flows"),
#"Expanded Custom.records.properties.flows1" = Table.ExpandRecordColumn(#"Expanded Custom.records.properties.flows", "Custom.records.properties.flows", {"rule", "flows"}, {"Custom.records.properties.flows.rule", "Custom.records.properties.flows.flows"}),
#"Expanded Custom.records.properties.flows.flows" = Table.ExpandListColumn(#"Expanded Custom.records.properties.flows1", "Custom.records.properties.flows.flows"),
#"Expanded Custom.records.properties.flows.flows1" = Table.ExpandRecordColumn(#"Expanded Custom.records.properties.flows.flows", "Custom.records.properties.flows.flows", {"mac", "flowTuples"}, {"Custom.records.properties.flows.flows.mac", "Custom.records.properties.flows.flows.flowTuples"}),
#"Expanded Custom.records.properties.flows.flows.flowTuples" = Table.ExpandListColumn(#"Expanded Custom.records.properties.flows.flows1", "Custom.records.properties.flows.flows.flowTuples"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom.records.properties.flows.flows.flowTuples","Custom.records.properties.flows.flows.flowTuples",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Custom.records.properties.flows.flows.flowTuples.1", "Custom.records.properties.flows.flows.flowTuples.2", "Custom.records.properties.flows.flows.flowTuples.3", "Custom.records.properties.flows.flows.flowTuples.4", "Custom.records.properties.flows.flows.flowTuples.5", "Custom.records.properties.flows.flows.flowTuples.6", "Custom.records.properties.flows.flows.flowTuples.7", "Custom.records.properties.flows.flows.flowTuples.8"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.records.properties.flows.flows.flowTuples.1", type number}, {"Custom.records.properties.flows.flows.flowTuples.2", type text}, {"Custom.records.properties.flows.flows.flowTuples.3", type text}, {"Custom.records.properties.flows.flows.flowTuples.4", Int64.Type}, {"Custom.records.properties.flows.flows.flowTuples.5", Int64.Type}, {"Custom.records.properties.flows.flows.flowTuples.6", type text}, {"Custom.records.properties.flows.flows.flowTuples.7", type text}, {"Custom.records.properties.flows.flows.flowTuples.8", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Custom.records.systemId", "Custom.records.category", "Custom.records.operationName", "Custom.records.properties.Version"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns","Custom.records.resourceId",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"Custom.records.resourceId.1", "Custom.records.resourceId.2", "Custom.records.resourceId.3", "Custom.records.resourceId.4", "Custom.records.resourceId.5", "Custom.records.resourceId.6", "Custom.records.resourceId.7", "Custom.records.resourceId.8", "Custom.records.resourceId.9"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.records.resourceId.1", type text}, {"Custom.records.resourceId.2", type text}, {"Custom.records.resourceId.3", type text}, {"Custom.records.resourceId.4", type text}, {"Custom.records.resourceId.5", type text}, {"Custom.records.resourceId.6", type text}, {"Custom.records.resourceId.7", type text}, {"Custom.records.resourceId.8", type text}, {"Custom.records.resourceId.9", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Custom.records.resourceId.1", "Custom.records.resourceId.2", "Custom.records.resourceId.4", "Custom.records.resourceId.6", "Custom.records.resourceId.7", "Custom.records.resourceId.8"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom.records.properties.flows.flows.flowTuples.8", "Decision"}, {"Custom.records.properties.flows.flows.flowTuples.7", "Direction"}, {"Custom.records.properties.flows.flows.flowTuples.6", "Protocol"}, {"Custom.records.properties.flows.flows.flowTuples.5", "Destination.Port"}, {"Custom.records.properties.flows.flows.flowTuples.4", "Source.Port"}, {"Custom.records.properties.flows.flows.flowTuples.3", "Destination.IP"}, {"Custom.records.properties.flows.flows.flowTuples.2", "Source.IP"}, {"Custom.records.properties.flows.flows.flowTuples.1", "FlowTime"}, {"Custom.records.properties.flows.flows.mac", "MAC"}, {"Custom.records.properties.flows.rule", "Rule"}, {"Custom.records.resourceId.9", "NSGName"}, {"Custom.records.resourceId.5", "ResourceGroupName"}, {"Custom.records.resourceId.3", "SubscriptionId"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Custom.records.time", type datetimezone}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Custom.records.time", "LogTime"}})
in
#"Renamed Columns1"