Kusto Query Language Count Assets

Pedro Ivo 0

Good night, everyone. Can you help me? I am a beginner in the use of Kusto Query Language (KQL) in Microsoft Sentinel and I am Very confused. How can I count alerts by the aflicted asset type (personal computer, servers ...)? I am not finding this column name on SecurityAlert Table.

JamesTran-MSFT 36,371 Reputation points Microsoft Employee

2024-02-28T21:56:21.18+00:00
@Pedro Ivo
I just wanted to check in and see if you had any other questions or if you were able to resolve this issue?

If you're still having issues, can you share a screenshot of what you're seeing when running your KQL query.

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.

If the information helped address your question, please Accept the answer. This will help us and also improve searchability for others in the community who might be researching similar information.
Clive Watson 5,716 Reputation points MVP

2024-02-29T10:38:10.5966667+00:00

What is the source product? I also dont see 'AffectedAssetType' but it maybe that its only available from a specific Alert source (that I dont have).

Are you looking for this data from Defender for Endpoint as an example, when Alerted in Sentinel?
JamesTran-MSFT 36,371 Reputation points Microsoft Employee

2024-03-05T17:24:10.41+00:00

@Pedro Ivo

I just wanted to check in and see if you had any other questions or if you were able to resolve this issue?

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.

If the information helped address your question, please Accept the answer. This will help us and also improve searchability for others in the community who might be researching similar information.

1 answer

Adharsh Santhanam 1,205 Reputation points

2024-02-22T03:31:17.66+00:00

The AffectedAssetType is available as extended properties. For your query, you can try something like this.

SecurityAlert | where TimeGenerated >= ago(1d) // Adjust the time range as needed | extend AffectedAssetType = tostring(parse_json(ExtendedProperties)['AffectedAssetType']) | summarize AlertCount = count() by AffectedAssetType

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.
Pedro Ivo 0 Reputation points

2024-02-22T19:58:22.03+00:00

Thanks! But I only got an empty string and the total number of alerts within the specified period as a result. I was unable to view the type of asset affected. Is there any prior configuration I need to do in the environment?

Adharsh Santhanam 1,205 Reputation points

2024-02-23T04:51:28.4433333+00:00

No, did you change the data range in the above filter because the one that I gave is only for 1 day. You would want to change that to reflect a longer duration than that.

Pedro Ivo 0 Reputation points

2024-02-23T09:26:20.5833333+00:00

I've tried one year and I got the correct alerts quantity, but without asset indication.
Sign in to comment