topic Re: Need Help with Splunk Query in Splunk Search

Need Help with Splunk Query

nilbak88 — Mon, 25 Oct 2021 16:46:15 GMT

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

Re: Need Help with Splunk Query

nilbak88 — Tue, 26 Oct 2021 07:27:27 GMT

@harsmarvania57 Can you suggest and help, please ?

Re: Need Help with Splunk Query

ITWhisperer — Tue, 26 Oct 2021 09:32:26 GMT

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

Re: Need Help with Splunk Query

nilbak88 — Tue, 26 Oct 2021 09:59:25 GMT

@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.

Re: Need Help with Splunk Query

ITWhisperer — Tue, 26 Oct 2021 12:55:59 GMT

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.

Re: Need Help with Splunk Query

nilbak88 — Wed, 27 Oct 2021 14:07:06 GMT

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

Re: Need Help with Splunk Query

andygerberkp — Wed, 22 Jun 2022 14:11:20 GMT

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