Need help to create KQL to get the count in Azure

I have created below KQL for above mentioned scenario but it's giving below error: 'extend' operator: Failed to resolve scalar expression named 'Timestamp' Request id: 110870af-8a15-4eb1-8673-68cd59148d91

AppEvents

| where tostring(Properties.Policy) == "B2C_1A_RPBT_AccountActivation"

| where Name == "TAPI_AUTOPROVISION_JWT_CREATE_SUCCESS"

| extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId), clientId = tostring(Properties.ClientId), eventTime = Timestamp

| summarize LastAutoProvisionTime = max(eventTime) by obj, session, clientId

| join kind=anti (

AppEvents

| where Name has "Password" or Name has "LocalAccountWrite"

| extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId)

| distinct obj, session

) on obj, session

| summarize TotalAutoProvisionedWithoutPassword = dcount(obj) by clientId

| sort by TotalAutoProvisionedWithoutPassword desc

Hello Sonika Sahu,

To create a KQL query that counts the number of customers created via auto-provisioning who have not yet set up their password, you can modify your existing query to ensure that the Timestamp field is properly referenced. The error you encountered indicates that the Timestamp field may not be available in the context you're using it.

Here’s a revised version of your KQL query that should work:

AppEvents
| where tostring(Properties.Policy) == "B2C_1A_RPBT_AccountActivation"
| where Name == "TAPI_AUTOPROVISION_JWT_CREATE_SUCCESS"
| extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId), clientId = tostring(Properties.ClientId)
| summarize LastAutoProvisionTime = max(Timestamp) by obj, session, clientId
| join kind=anti (
    AppEvents
    | where Name has "Password" or Name has "LocalAccountWrite"
    | extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId)
    | distinct obj, session
) on obj, session
| summarize TotalAutoProvisionedWithoutPassword = dcount(obj) by clientId
| sort by TotalAutoProvisionedWithoutPassword desc

In this query:

• Ensure that Timestamp is available in the AppEvents table. If it is not, you may need to replace it with the correct field that represents the event time.
• The join kind=anti is used to exclude sessions where the password was set.

Make sure to replace clientId with the appropriate application client ID as needed.

If the provided solution works for your query, please do not forget to Accept the answer and Upvote it. this can be beneficial to other community members.it would be greatly appreciated and helpful to others.

Thanks

Hello Sonika Sahu,

We haven't heard back from you regarding the advice I provided earlier. Could you please let us know if it was helpful? If you have any questions or encounter any issues, please don't hesitate to reach out. We are committed to resolving your concerns as a priority.

Thanks

Hello Sonika Sahu,

Just want to check if the above answer worked for you or else please let us know if any help needed, we are always here to help whenever you need us.

Please do not forget to “upvote it” wherever the information provided helps you, this can be beneficial to other community members.it would be greatly appreciated and helpful to others.

Thanks