Join Sentinel Entity Json in Logicapp

Question

Join Sentinel Entity Json in Logicapp

Karthick G 76

Sentinel alert Entity output in Logicapp is in below format

[
{
"$id": "3",
"Name": “randamuser1”,
"Type": "account"
},
{
"$id": "4",
"Name": "randamuser2”,
"Type": "account"
},

{
"$id": "6",
"HostName": “hostofuser1”,
"Type": "host"
},
{
"$id": "7",
"HostName": “hostofuser2,
"Type": "host"
},
]

i wanted to map Name & Hostname in same json block, how to achieve it.

Kamlesh Kumar 3,771 Reputation points

2022-08-14T06:16:43.567+00:00

Hi @Karthick G ,

Welcome to Microsoft Q&A Platform. Thank you for the question.

Could you please share the correct input and output (expected) json file, because seems above sample is not correct. It has object 'Name' and 'HostName'.

Regards,
Kamlesh Kumar

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is How

Want a reminder to come back and check responses? Here is how to subscribe to a Notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Karthick G 76 Reputation points

2022-08-14T06:41:03.94+00:00

In logicapp when i invoke Microsoft Sentinel Alert the Entites are displayed in below format in am trying get username and devicename

[
{
"$id": "3",
"Name": “randamuser1”,
"Type": "account"
},
{
"$id": "4",
"Name": "randamuser2”,
"Type": "account"
},

{
"$id": "6",
"HostName": “hostofuser1”,
"Type": "host"
},
{
"$id": "7",
"HostName": “hostofuser2,
"Type": "host"
},
]

my expected output is

{
"Name": “randamuser1”,
"HostName": “hostofuser1”
}

{
"Name": "randamuser2”,
"HostName": “hostofuser2,
}

so that i can build a html table in below format

Name hostname
randamuser1 hostofuser1
randamuser2 hostofuser2

Kamlesh Kumar 3,771

If you see the source array data, 1st & 2nd list has Name object but 3rd and 4th doesn't have the Name though have HostName. Could you please confirm if source data is correct?

[  
	{  
		"$id": "3",  
		"Name": "randamuser1",  
		"Type": "account"  
	},  
	{  
		"$id": "4",  
		"Name": "randamuser2",  
		"Type": "account"  
	},  
	{  
		"$id": "6",  
		"HostName": "hostofuser1",  
		"Type": "host"  
	},  
	{  
		"$id": "7",  
		"HostName": "hostofuser2",  
		"Type": "host"  
	}  
]

Karthick G 76 Reputation points

2022-08-14T12:06:52.197+00:00

Yes the values are correct thats how it is being retrieved from Sentinel alert. Image for you reference.

Kamlesh Kumar 3,771 Reputation points

2022-08-14T06:16:43.567+00:00

Hi @Karthick G ,

Welcome to Microsoft Q&A Platform. Thank you for the question.

Could you please share the correct input and output (expected) json file, because seems above sample is not correct. It has object 'Name' and 'HostName'.

Regards,
Kamlesh Kumar

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is How

Want a reminder to come back and check responses? Here is how to subscribe to a Notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Karthick G 76 Reputation points

2022-08-14T06:41:03.94+00:00

In logicapp when i invoke Microsoft Sentinel Alert the Entites are displayed in below format in am trying get username and devicename

[
{
"$id": "3",
"Name": “randamuser1”,
"Type": "account"
},
{
"$id": "4",
"Name": "randamuser2”,
"Type": "account"
},

{
"$id": "6",
"HostName": “hostofuser1”,
"Type": "host"
},
{
"$id": "7",
"HostName": “hostofuser2,
"Type": "host"
},
]

my expected output is

{
"Name": “randamuser1”,
"HostName": “hostofuser1”
}

{
"Name": "randamuser2”,
"HostName": “hostofuser2,
}

so that i can build a html table in below format

Name hostname
randamuser1 hostofuser1
randamuser2 hostofuser2
Kamlesh Kumar 3,771 Reputation points

2022-08-14T10:28:05.123+00:00

If you see the source array data, 1st & 2nd list has Name object but 3rd and 4th doesn't have the Name though have HostName. Could you please confirm if source data is correct?

[ { "$id": "3", "Name": "randamuser1", "Type": "account" }, { "$id": "4", "Name": "randamuser2", "Type": "account" }, { "$id": "6", "HostName": "hostofuser1", "Type": "host" }, { "$id": "7", "HostName": "hostofuser2", "Type": "host" } ]
Karthick G 76 Reputation points

2022-08-14T12:06:52.197+00:00

Yes the values are correct thats how it is being retrieved from Sentinel alert. Image for you reference.

Answer 1

Hi Karthick,

Those entities likely won't map the way you'd like given your analytic rule.

However if you play with your kql you could create a concatenated value and map that to an identifier. (I use strcat frequently)

The other option is to add a log analytics query to your logic app and pull out the 2 fields that way, but I'd do it as in the first suggestion.

Hope that helps.

reference:
strcatfunction

Answer 2

@Karthick G Thanks for reaching out. Unfortunately, you cannot format the data at the logic app end as there is no relation or way to distinguish which user account will be mapped to which user host in case if you want to format it at the logic app end either using inline code or using different action such as foreach, conditions etc. as I don't see any relationship that would help in processing it further at the logic app end.

Alternative as David has mentioned I will suggest you to only pull the required fields as per your business needs.

Join Sentinel Entity Json in Logicapp

2 answers

Activity