Need Help with Splunk Query

[nilbak88]

Hello Splunkers,

I need help with Network Security Group flow logs where  each of the tuples should be a single event  with other relevant data for an event.

Sample.log

_raw:
{"time":"2021-10-25T16:17:50.8670851Z","systemId":"1c5751f4-8686-4ea5-82ee-173b64d401dd","macAddress":"xxxxxxxxxx","category":"NetworkSecurityGroupFlowEvent","resourceId":"/SUBSCRIPTIONS/A80612A2-33D6-47FF-817A-283E8BC8EDD2/RESOURCEGROUPS/C-SAP-EUS-NONPROD-01-INT-NETWORKING-RG/PROVIDERS/MICROSOFT.NETWORK/NETWORKSECURITYGROUPS/DATA-INT-SUBNET-NSG","operationName":"NetworkSecurityGroupFlowEvents","properties":{"Version":2,"flows":[{"rule":"DefaultRule_AllowVnetOutBound","flows":[{"mac":"000D3A57248C","flowTuples":["1635178607,,10.123.2.28,46058,9997,T,O,A,E,1,74,1,60","1635178607,10.115.34.31,10.123.2.18,29128,9997,T,O,A,E,19,7292,16,1227","1635178609,10.115.34.31,10.119.241.5,26540,9997,T,O,A,E,47,54806,64,4395","1635178612,10.115.34.31,13.69.239.72,56024,443,T,O,A,B,,,,","1635178613,10.115.34.31,13.69.239.72,56026,443,T,O,A,B,,,,","1635178614,10.115.34.31,10.192.124.221,56488,80,T,O,A,B,,,,","1635178618,10.115.34.31,13.69.239.72,56024,443,T,O,A,E,8,1158,8,4897"]}]},{"rule":"UserRule_AzAppSubnet_access_toAzDBSubnet_Catch-all","flows":[{"mac":"000D3A57248C","flowTuples":["1635178635,10.115.32.28,10.115.34.31,54322,33015,T,I,A,B,,,,"]}]}]}}

Json format

   category: NetworkSecurityGroupFlowEvent
   macAddress: xxxxxxxxxx
   operationName: NetworkSecurityGroupFlowEvents
   properties: { [-]
     Version: 2
     flows: [ [-]
       { [-]
         flows: [ [-]
           { [-]
             flowTuples: [ [-]
               1635172376,ip1,ip2,58636,443,T,O,A,E,6,1611,1,66
               1635172377,ip1,ip2,27910,443,T,O,A,B,,,,
               1635172377,ip1,ip2,59136,443,T,O,A,E,0,0,0,0
               1635172378,ip1,ip2,56756,9997,T,O,A,B,,,,
               1635172378,ip1,ip2,58686,9997,T,O,A,B,,,,
               1635172379,ip1,ip2,53684,9997,T,O,A,B,,,,

Result:
Event 1:

category: NetworkSecurityGroupFlowEvent
   macAddress: xxxxxxxxxx
   operationName: NetworkSecurityGroupFlowEvents
   properties: { [-]
     Version: 2
     flows: [ [-]
       { [-]
         flows: [ [-]
           { [-]
             flowTuples: [ [-]
               1635172376,ip1,ip2,58636,443,T,O,A,E,6,1611,1,66
             
Event2:

category: NetworkSecurityGroupFlowEvent
   macAddress: xxxxxxxxxx
   operationName: NetworkSecurityGroupFlowEvents
   properties: { [-]
     Version: 2
     flows: [ [-]
       { [-]
         flows: [ [-]
           { [-]
             flowTuples: [ [-]
              1635172377,ip1,ip2,27910,443,T,O,A,B,,,,
               

Thanks

[nilbak88]

@ITWhisperer Thanks for looking into it.
I tried with the above suggestion but not getting the desired result.

Here, I want to  keep all of the event details and separate the tuples into events.

[ITWhisperer · Accepted solution]

All the tuples are separate events - each event has the raw data so you can extract the additional information that you need (with separate spath commands if you need to) - if I were you I would decide exactly which pieces of data you want associated with each tuple and just extract that - alternatively, you could extract everything and prune afterwards.

[nilbak88]

Yes, that is the only way I also think so.
Anyways thanks for all the help

[nilbak88]

@harsmarvania57   Can you suggest and help, please ?

[ITWhisperer]

Try something along these lines:

| makeresults
| eval _raw="{\"time\":\"2021-10-25T16:17:50.8670851Z\",\"systemId\":\"1c5751f4-8686-4ea5-82ee-173b64d401dd\",\"macAddress\":\"xxxxxxxxxx\",\"category\":\"NetworkSecurityGroupFlowEvent\",\"resourceId\":\"/SUBSCRIPTIONS/A80612A2-33D6-47FF-817A-283E8BC8EDD2/RESOURCEGROUPS/C-SAP-EUS-NONPROD-01-INT-NETWORKING-RG/PROVIDERS/MICROSOFT.NETWORK/NETWORKSECURITYGROUPS/DATA-INT-SUBNET-NSG\",\"operationName\":\"NetworkSecurityGroupFlowEvents\",\"properties\":{\"Version\":2,\"flows\":[{\"rule\":\"DefaultRule_AllowVnetOutBound\",\"flows\":[{\"mac\":\"000D3A57248C\",\"flowTuples\":[\"1635178607,,10.123.2.28,46058,9997,T,O,A,E,1,74,1,60\",\"1635178607,10.115.34.31,10.123.2.18,29128,9997,T,O,A,E,19,7292,16,1227\",\"1635178609,10.115.34.31,10.119.241.5,26540,9997,T,O,A,E,47,54806,64,4395\",\"1635178612,10.115.34.31,13.69.239.72,56024,443,T,O,A,B,,,,\",\"1635178613,10.115.34.31,13.69.239.72,56026,443,T,O,A,B,,,,\",\"1635178614,10.115.34.31,10.192.124.221,56488,80,T,O,A,B,,,,\",\"1635178618,10.115.34.31,13.69.239.72,56024,443,T,O,A,E,8,1158,8,4897\"]}]},{\"rule\":\"UserRule_AzAppSubnet_access_toAzDBSubnet_Catch-all\",\"flows\":[{\"mac\":\"000D3A57248C\",\"flowTuples\":[\"1635178635,10.115.32.28,10.115.34.31,54322,33015,T,I,A,B,,,,\"]}]}]}}"



| spath output=properties.flows properties.flows{}
| mvexpand properties.flows
| spath output=flowTuples properties.flows{}.flows{}.flowTuples{}
| mvexpand flowTuples

[andygerberkp]

Sigh. The problem with this solution is that the flow log JSON events can be massive (>500K) and spath /mvexpand can't handle it.  Also the best way to use this data would be for each individual flow log entry to have all associated data (Rule included).  There are some other techniques discussed in this 2020 blog post that use an Azure Function to send HEC events. https://www.splunk.com/en_us/blog/platform/splunking-azure-nsg-flow-logs.html