Parse string in Azure Sentinel

%3CLINGO-SUB%20id%3D%22lingo-sub-3098385%22%20slang%3D%22en-US%22%3EParse%20string%20in%20Azure%20Sentinel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3098385%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20we%20want%20to%20parse%20out%20the%20%22subscription%20ID%22%20as%20a%20field%20from%20the%20following%20string.%20Please%20let%20me%20know%20how%20can%20I%20do%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%221185%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22128%22%3EColumn%20Name%3C%2FTD%3E%3CTD%20width%3D%221057%22%3Estring%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EprivateLinkServiceId_%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%2Fsubscriptions%2F%22SubsriptionID%22%2FresourceGroups%2F%22RG-Name%22%2Fproviders%2FMicrosoft.Storage%2FstorageAccounts%2F%22Name%22%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExisting%20query%26nbsp%3B%20(%20please%20suggest%20what%20needs%20to%20be%20appended)%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAzureActivity%20%7C%20search%20%22entity%22%3CBR%20%2F%3E%7C%20extend%20privateLinkServiceId_%20%3D%20tostring(parse_json(tostring(parse_json(tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).privateLinkServiceConnections))%5B0%5D.properties)).privateLinkServiceId)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3099638%22%20slang%3D%22en-US%22%3ERe%3A%20Parse%20string%20in%20Azure%20Sentinel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3099638%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1293385%22%20target%3D%22_blank%22%3E%40SiddharthRajD%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EAzureActivity%20%0A%7C%20extend%20entity_%20%3D%20tostring(parse_json(Properties).entity)%0A%7C%20where%20isnotempty(entity_)%0A%7C%20parse%20entity_%20with%20*%20'%2Fsubscriptions%2F'%20subscription_%20'%2F'%20*%0A%7C%20project%20subscription_%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EHowever%20SubscriptionId%20is%20already%20a%20column%20in%20that%20Table%2C%20so%20you%20can%20just%20do%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EAzureActivity%0A%7C%20extend%20entity_%20%3D%20tostring(parse_json(Properties).entity)%0A%7C%20where%20isnotempty(entity_)%0A%7C%20project%20SubscriptionId%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello, we want to parse out the "subscription ID" as a field from the following string. Please let me know how can I do that?

 

Column Namestring

privateLinkServiceId_

/subscriptions/"SubsriptionID"/resourceGroups/"RG-Name"/providers/Microsoft.Storage/storageAccounts/"Name"

 

Existing query  ( please suggest what needs to be appended) :

 

AzureActivity | search "entity"
| extend privateLinkServiceId_ = tostring(parse_json(tostring(parse_json(tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).privateLinkServiceConnections))[0].properties)).privateLinkServiceId)

1 Reply

@SiddharthRajD 

 

AzureActivity 
| extend entity_ = tostring(parse_json(Properties).entity)
| where isnotempty(entity_)
| parse entity_ with * '/subscriptions/' subscription_ '/' *
| project subscription_

However SubscriptionId is already a column in that Table, so you can just do 

AzureActivity
| extend entity_ = tostring(parse_json(Properties).entity)
| where isnotempty(entity_)
| project SubscriptionId