cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
madmvx
Frequent Visitor

How to parse a dynamic Json

Im getting a http response from Azure LogAnalytics, the response is a Json like this

 

 

{
 "tables": [
{
  "name": "PrimaryResult",
  "columns": [
    {
      "name": "TimeGenerated",
      "type": "datetime"
    },
    {
      "name": "DestinationIP",
      "type": "string"
    },
    {
      "name": "DestinationUserName",
      "type": "string"
    },
    {
      "name": "country_name",
      "type": "string"
    },
    {
      "name": "country_iso_code",
      "type": "string"
    },
    {
      "name": "AccountCustomEntity",
      "type": "string"
    }
  ],
  "rows": [
    [
      "2021-05-17T14:07:01.878Z",
      "158.000.000.33",
      "luis",
      "United States",
      "US",
      "luis"
       ]
     ] 
   } 
 ]
}

 

 

 

I will never get the same colums or sometimes i will get more rows with data like this

 

 

 

  {
 "tables": [
{
  "name": "PrimaryResult",
  "columns": [
    {
      "name": "Account",
      "type": "string"
    },
    {
      "name": "Computer",
      "type": "string"
    },
    {
      "name": "IpAddress",
      "type": "string"
    },
    {
      "name": "AccountType",
      "type": "string"
    },
    {
      "name": "Activity",
      "type": "string"
    },
    {
      "name": "LogonTypeName",
      "type": "string"
    },
    {
      "name": "ProcessName",
      "type": "string"
    },
    {
      "name": "StartTimeUtc",
      "type": "datetime"
    },
    {
      "name": "EndTimeUtc",
      "type": "datetime"
    },
    {
      "name": "ConnectinCount",
      "type": "long"
    },
    {
      "name": "timestamp",
      "type": "datetime"
    },
    {
      "name": "AccountCustomEntity",
      "type": "string"
    },
    {
      "name": "HostCustomEntity",
      "type": "string"
    },
    {
      "name": "IPCustomEntity",
      "type": "string"
    }
  ],
  "rows": [
    [
      "abc\\abc",
      "EQ-DC02.abc.LOCAL",
      "0.0.0.0",
      "User",
      "4624 - An account was successfully logged on.",
      "10 - RemoteInteractive",
      "C:\\Windows\\System32\\svchost.exe",
      "2021-05-17T15:02:25.457Z",
      "2021-05-17T15:02:25.457Z",
      2,
      "2021-05-17T15:02:25.457Z",
      "abc\\abc",
      "EQ-DC02.abc.LOCAL",
      "0.0.0.0"
    ],
    [
      "abc\\eona",
      "EQPD-SW01.abc.LOCAL",
      "0.0.0.0",
      "User",
      "4624 - An account was successfully logged on.",
      "10 - RemoteInteractive",
      "C:\\Windows\\System32\\svchost.exe",
      "2021-05-17T15:21:45.993Z",
      "2021-05-17T15:21:45.993Z",
      1,
      "2021-05-17T15:21:45.993Z",
      "abc\\abc",
      "EQPD-SW01.abc.LOCAL",
      "0.0.0.0"
    ]
    ]
  }
]
}

 

 

the question is, how can i parse this "Columns" and "Rows" to a object? to send it to powerapps

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @madmvx 

 

You've got a formatting issue with the expression and have placed a ) in the wrong place as follows:


range(0,length(first(body('ParseTable')?['tables'])?['columns']))

 

First() will return the first object of an array and the cloud flow expects more than one object for tables and columns.  Your alternative is using an integer to express this.

 
range(0,length(body('ParseTable')?['tables']?[0]?['columns']))

 

Did you copy and paste my example to see it first hand?

 

I also see the changes you've made to your final output - changes the requirement slightly 🤣 but I believe I now have what you are looking for below:

 

{"id":"ab0e2e6a-216d-4965-b973-c5f2-4791e734","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"DamoBird365_Repurpose_Key_Value","operationDefinition":{"type":"Scope","actions":{"Compose":{"type":"Compose","inputs":{"tables":[{"name":"PrimaryResult","columns":[{"name":"Account","type":"string"},{"name":"Computer","type":"string"},{"name":"IpAddress","type":"string"},{"name":"AccountType","type":"string"},{"name":"Activity","type":"string"},{"name":"LogonTypeName","type":"string"},{"name":"ProcessName","type":"string"},{"name":"StartTimeUtc","type":"datetime"},{"name":"EndTimeUtc","type":"datetime"},{"name":"ConnectinCount","type":"long"},{"name":"timestamp","type":"datetime"},{"name":"AccountCustomEntity","type":"string"},{"name":"HostCustomEntity","type":"string"},{"name":"IPCustomEntity","type":"string"}],"rows":[["abc\\abc","EQ-DC02.abc.LOCAL","0.0.0.0","User","4624 - An account was successfully logged on.","10 - RemoteInteractive","C:\\Windows\\System32\\svchost.exe","2021-05-17T15:02:25.457Z","2021-05-17T15:02:25.457Z",2,"2021-05-17T15:02:25.457Z","abc\\abc","EQ-DC02.abc.LOCAL","0.0.0.0"],["abc\\eona","EQPD-SW01.abc.LOCAL","0.0.0.0","User","4624 - An account was successfully logged on.","10 - RemoteInteractive","C:\\Windows\\System32\\svchost.exe","2021-05-17T15:21:45.993Z","2021-05-17T15:21:45.993Z",1,"2021-05-17T15:21:45.993Z","abc\\abc","EQPD-SW01.abc.LOCAL","0.0.0.0"]]}]},"runAfter":{}},"RowHeader":{"type":"Select","inputs":{"from":"@range(0,sub(length(first(outputs('Compose')?['tables'])?['columns']),1))","select":"@first(outputs('Compose')?['tables'])?['columns']?[item()]?['name']"},"runAfter":{"Compose":["Succeeded"]}},"Apply_to_each":{"type":"Foreach","foreach":"@first(outputs('Compose')?['tables'])?['rows']","actions":{"EachObject":{"type":"Select","inputs":{"from":"@range(0,sub(length(body('RowHeader')),1))","select":{"@{body('RowHeader')?[item()]}":"@items('Apply_to_each')?[item()]"}},"runAfter":{}},"replace":{"type":"Compose","inputs":"@replace(replace(replace(replace(string(body('EachObject')),'{',''),'}',''),'[','{'),']','}')","runAfter":{"EachObject":["Succeeded"]}},"json":{"type":"Compose","inputs":"@json(outputs('replace'))","runAfter":{"replace":["Succeeded"]}}},"runAfter":{"RowHeader":["Succeeded"]}},"Re_Purposed_Array":{"type":"Compose","inputs":"@outputs('json')","runAfter":{"Apply_to_each":["Succeeded"]}}},"runAfter":{}}}

 

There is now a need to remove the curly and square brackets from each loop and then once the array has been brought back together, remove any escaped chars with the json() expression.  

 

I also spotted a wee mistake with my ranges.  They should run from 0 to lenght-1.  You'll see that in the above copy paste.

 

Here's the flow in full:

 

DamoBird365_0-1621320272397.png

And your sample output:

 

DamoBird365_1-1621320311124.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

View solution in original post

5 REPLIES 5
DamoBird365
Super User
Super User

Hi @madmvx 

 

Have you got a sample required output?  

 

You can use select to repurpose the data.

DamoBird365_0-1621279021332.png

 

would give you this:

DamoBird365_1-1621279063032.png

 

You could obviously do the same for rows.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Yes i got a sample required, i need something like this:

Using the First example, but this is with one object 

 

 

[
    {
      "TimeGenerated": "2021-05-17T14:07:01.878Z"
    ,
    
      "DestinationIP": "158.000.000.33"
    ,
    
      "DestinationUserName": "luis"
    
  }
]

 

 

 and this is what i need if i have 2

 

 

[
    {
      "TimeGenerated": "2021-05-17T14:07:01.878Z"
    ,
    
      "DestinationIP": "158.000.000.33"
    ,
    
      "DestinationUserName": "luis"
    
  },
{
      "TimeGenerated": "2021-05-17T14:07:01.878Z"
    ,
    
      "DestinationIP": "158.000.000.33"
    ,
    
      "DestinationUserName": "luis"
    
  }
]

 

 

Obviously the key and the value never gonna be the same

The Key is the value of "name" in columns And the Value is the value of rows

@DamoBird365 

Hi @madmvx 

 

This is ALMOST what you are looking for.

 

DamoBird365_0-1621281967217.png

 Output is as follows:

 

DamoBird365_1-1621282088514.png

 

I've done you a copy /paste for your cloud flow so that you can get the various expressions.  Just copy the below code, open a new flow with manual trigger, select "new step", go to my clipboard (last tab) and ctrl+v on your keyboard and select the scope that is pasted.  Go ahead and save and test.

 

{"id":"e3700810-d364-4f13-9736-d408-8b3eac7c","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"DamoBird365_Repurpose_Key_Value","operationDefinition":{"type":"Scope","actions":{"Compose":{"type":"Compose","inputs":{"tables":[{"name":"PrimaryResult","columns":[{"name":"Account","type":"string"},{"name":"Computer","type":"string"},{"name":"IpAddress","type":"string"},{"name":"AccountType","type":"string"},{"name":"Activity","type":"string"},{"name":"LogonTypeName","type":"string"},{"name":"ProcessName","type":"string"},{"name":"StartTimeUtc","type":"datetime"},{"name":"EndTimeUtc","type":"datetime"},{"name":"ConnectinCount","type":"long"},{"name":"timestamp","type":"datetime"},{"name":"AccountCustomEntity","type":"string"},{"name":"HostCustomEntity","type":"string"},{"name":"IPCustomEntity","type":"string"}],"rows":[["abc\\abc","EQ-DC02.abc.LOCAL","0.0.0.0","User","4624 - An account was successfully logged on.","10 - RemoteInteractive","C:\\Windows\\System32\\svchost.exe","2021-05-17T15:02:25.457Z","2021-05-17T15:02:25.457Z",2,"2021-05-17T15:02:25.457Z","abc\\abc","EQ-DC02.abc.LOCAL","0.0.0.0"],["abc\\eona","EQPD-SW01.abc.LOCAL","0.0.0.0","User","4624 - An account was successfully logged on.","10 - RemoteInteractive","C:\\Windows\\System32\\svchost.exe","2021-05-17T15:21:45.993Z","2021-05-17T15:21:45.993Z",1,"2021-05-17T15:21:45.993Z","abc\\abc","EQPD-SW01.abc.LOCAL","0.0.0.0"]]}]},"runAfter":{}},"RowHeader":{"type":"Select","inputs":{"from":"@range(0,length(first(outputs('Compose')?['tables'])?['columns']))","select":"@first(outputs('Compose')?['tables'])?['columns']?[item()]?['name']"},"runAfter":{"Compose":["Succeeded"]}},"Apply_to_each":{"type":"Foreach","foreach":"@first(outputs('Compose')?['tables'])?['rows']","actions":{"EachObject":{"type":"Select","inputs":{"from":"@range(1,length(body('RowHeader')))","select":{"@{body('RowHeader')?[item()]}":"@items('Apply_to_each')?[item()]"}},"runAfter":{}}},"runAfter":{"RowHeader":["Succeeded"]}},"Compose_2":{"type":"Compose","inputs":"@outputs('eachobject')","runAfter":{"Apply_to_each":["Succeeded"]}}},"runAfter":{}}}

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

I tried running the first RowHeader but i got this error

 

Unable to process template language expressions in action 'SelectRelevantItems' inputs at line '1' and column '3038': 'The template language expression 'range(0,length(first(body('ParseTable')?['tables']?['columns'])))' cannot be evaluated because property 'columns' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

 

i put this 

From: range(0,length(first(body('ParseTable')?['tables']?['columns'])))
map: Key: first(outputs('ParseTable')?['tables']?['columns']?[item()]?['name']) Value: 

madmvx_0-1621284169977.png

@DamoBird365 

And sorry i edited the question and the sample is like the edited now, but i appreciate a lot the help, i need to solve this, is really hard.

Hi @madmvx 

 

You've got a formatting issue with the expression and have placed a ) in the wrong place as follows:


range(0,length(first(body('ParseTable')?['tables'])?['columns']))

 

First() will return the first object of an array and the cloud flow expects more than one object for tables and columns.  Your alternative is using an integer to express this.

 
range(0,length(body('ParseTable')?['tables']?[0]?['columns']))

 

Did you copy and paste my example to see it first hand?

 

I also see the changes you've made to your final output - changes the requirement slightly 🤣 but I believe I now have what you are looking for below:

 

{"id":"ab0e2e6a-216d-4965-b973-c5f2-4791e734","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"DamoBird365_Repurpose_Key_Value","operationDefinition":{"type":"Scope","actions":{"Compose":{"type":"Compose","inputs":{"tables":[{"name":"PrimaryResult","columns":[{"name":"Account","type":"string"},{"name":"Computer","type":"string"},{"name":"IpAddress","type":"string"},{"name":"AccountType","type":"string"},{"name":"Activity","type":"string"},{"name":"LogonTypeName","type":"string"},{"name":"ProcessName","type":"string"},{"name":"StartTimeUtc","type":"datetime"},{"name":"EndTimeUtc","type":"datetime"},{"name":"ConnectinCount","type":"long"},{"name":"timestamp","type":"datetime"},{"name":"AccountCustomEntity","type":"string"},{"name":"HostCustomEntity","type":"string"},{"name":"IPCustomEntity","type":"string"}],"rows":[["abc\\abc","EQ-DC02.abc.LOCAL","0.0.0.0","User","4624 - An account was successfully logged on.","10 - RemoteInteractive","C:\\Windows\\System32\\svchost.exe","2021-05-17T15:02:25.457Z","2021-05-17T15:02:25.457Z",2,"2021-05-17T15:02:25.457Z","abc\\abc","EQ-DC02.abc.LOCAL","0.0.0.0"],["abc\\eona","EQPD-SW01.abc.LOCAL","0.0.0.0","User","4624 - An account was successfully logged on.","10 - RemoteInteractive","C:\\Windows\\System32\\svchost.exe","2021-05-17T15:21:45.993Z","2021-05-17T15:21:45.993Z",1,"2021-05-17T15:21:45.993Z","abc\\abc","EQPD-SW01.abc.LOCAL","0.0.0.0"]]}]},"runAfter":{}},"RowHeader":{"type":"Select","inputs":{"from":"@range(0,sub(length(first(outputs('Compose')?['tables'])?['columns']),1))","select":"@first(outputs('Compose')?['tables'])?['columns']?[item()]?['name']"},"runAfter":{"Compose":["Succeeded"]}},"Apply_to_each":{"type":"Foreach","foreach":"@first(outputs('Compose')?['tables'])?['rows']","actions":{"EachObject":{"type":"Select","inputs":{"from":"@range(0,sub(length(body('RowHeader')),1))","select":{"@{body('RowHeader')?[item()]}":"@items('Apply_to_each')?[item()]"}},"runAfter":{}},"replace":{"type":"Compose","inputs":"@replace(replace(replace(replace(string(body('EachObject')),'{',''),'}',''),'[','{'),']','}')","runAfter":{"EachObject":["Succeeded"]}},"json":{"type":"Compose","inputs":"@json(outputs('replace'))","runAfter":{"replace":["Succeeded"]}}},"runAfter":{"RowHeader":["Succeeded"]}},"Re_Purposed_Array":{"type":"Compose","inputs":"@outputs('json')","runAfter":{"Apply_to_each":["Succeeded"]}}},"runAfter":{}}}

 

There is now a need to remove the curly and square brackets from each loop and then once the array has been brought back together, remove any escaped chars with the json() expression.  

 

I also spotted a wee mistake with my ranges.  They should run from 0 to lenght-1.  You'll see that in the above copy paste.

 

Here's the flow in full:

 

DamoBird365_0-1621320272397.png

And your sample output:

 

DamoBird365_1-1621320311124.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,423)