Get-MgBetaAuditLogSignIn with filter signInEventTypes does not return all results

Question

Get-MgBetaAuditLogSignIn with filter signInEventTypes does not return all results

David Trevor 321

In Azure Active Directory, one can check sign-in logs

I want to query the interactive user sign-ins via Powershell / via Graph.

I retrieve all events I can get and filter on a specific IP. I get 92308 results and verify that they are all of type "interactiveUser". The next query, I additionally include a filter on "interactiveUser". I should receive the same amount of events, however I receive only 3023, which is not even close to what I expected.

$IpFilter = '1.1.1.1'

$WithoutSignInType = Get-MgBetaAuditLogSignIn -All -Filter "(CreatedDateTime ge 2020-01-01T00:00Z) and (IPAddress eq $IpFilter)"
$WithoutSignInType | Group-Object SignInEventTypes
	Count Name
	----- ----
	92308 {interactiveUser}

$WithSignInType = Get-MgBetaAuditLogSignIn -All -Filter "signInEventTypes/any(t:t eq 'interactiveUser') and (CreatedDateTime ge 2020-01-01T00:00Z) and (IPAddress eq $IpFilter)"
$WithSignInType | Group-Object SignInEventTypes
	Count Name
	----- ----
	3023 {interactiveUser}

Peter Kayode 506 Reputation points

2023-08-02T14:19:55.9933333+00:00

There might be a few reasons why you are seeing different results when you filter on the "interactiveUser" sign in type. Here are a few possibilities:

Filter Expression Complexity: Microsoft Graph API and its PowerShell SDK have some limitations on the complexity of filter expressions. It might not be handling the combination of filters correctly, especially if there's a complex type involved like an array (which is what signInEventTypes appears to be).

Filter Syntax or Semantics: There might be a problem with how you're specifying the filter. Unfortunately, the exact semantics of how filter expressions work, especially with complex types like arrays, isn't well documented.

Data Skew: It might be that the vast majority of sign-in events from that IP are not interactive user sign-ins, so when you filter on that type, you're eliminating a large number of events.

To troubleshoot, I would recommend the following steps:

Run the query without the IP filter and the time filter. This will give you an idea of the total number of interactive user sign-ins and if there's a significant difference with the total number of events.

Try running the query with only the sign-in type filter. If you still see a significant discrepancy in the numbers, it could indicate that there's a problem with how the filter is being applied.

Verify that there aren't any other factors that could be skewing the data, such as sign-in events from bots or other non-interactive sources that might be inflating the number of events.

Remember that Azure Active Directory logs can be voluminous, so running queries without filters can return a lot of data. Be sure to test during off-peak hours or in a test environment if possible to avoid impacting the performance of your directory.
Sandeep G-MSFT 20,921 Reputation points Microsoft Employee Moderator

2023-08-16T04:30:02.92+00:00

@David Trevor

Just checking in to see if the below answer helped. If this answers your query, please don’t forget to click "Accept the answer" and Up-Vote for the same, which might be beneficial to other community members reading this thread.
David Trevor 321 Reputation points

2023-08-17T13:57:21.6+00:00

If I wanted ChatGPT to answer my question, I would have done it myself. So no, the answer certainly did not help.
Givary-MSFT 35,626 Reputation points Microsoft Employee Moderator

2023-08-18T08:57:38.06+00:00
@Peter Kayode Thank you for contributing to Microsoft Q&A. In an effort to maintain the integrity of answers that are posted, the use of AI tools are allowed as long as the following requirements have been met:

The answer must clearly indicate that it was fully or partially generated by an AI service, and list which AI service was used.

AI generated answers must be validated as providing a useful answer to the question being asked before posting.

If the AI service provides attribution links, those should be included with the AI response.

Failure to meet these requirements may result in suspension. For more information, please review the Artificial Intelligence (AI) usage policy for Microsoft Q&A:
https://learn.microsoft.com/en-us/answers/support/ai-usage-policy

Thanks,

Girish
Givary-MSFT 35,626 Reputation points Microsoft Employee Moderator

2023-08-21T06:43:03.58+00:00

@David Trevor Apologies for the delayed response and thank you for your feedback on this post.

Please send me an email to 'AzCommunity@microsoft.com' with Sub - Attn: Givary and following details in the email body:

Link to this thread/post

Azure Subscription ID, so that we can provide free support option to investigate on this issue.

1 answer

Your answer

Peter Kayode 506 Reputation points

2023-08-02T14:19:55.9933333+00:00

There might be a few reasons why you are seeing different results when you filter on the "interactiveUser" sign in type. Here are a few possibilities:

Filter Expression Complexity: Microsoft Graph API and its PowerShell SDK have some limitations on the complexity of filter expressions. It might not be handling the combination of filters correctly, especially if there's a complex type involved like an array (which is what signInEventTypes appears to be).

Filter Syntax or Semantics: There might be a problem with how you're specifying the filter. Unfortunately, the exact semantics of how filter expressions work, especially with complex types like arrays, isn't well documented.

Data Skew: It might be that the vast majority of sign-in events from that IP are not interactive user sign-ins, so when you filter on that type, you're eliminating a large number of events.

To troubleshoot, I would recommend the following steps:

Run the query without the IP filter and the time filter. This will give you an idea of the total number of interactive user sign-ins and if there's a significant difference with the total number of events.

Try running the query with only the sign-in type filter. If you still see a significant discrepancy in the numbers, it could indicate that there's a problem with how the filter is being applied.

Verify that there aren't any other factors that could be skewing the data, such as sign-in events from bots or other non-interactive sources that might be inflating the number of events.

Remember that Azure Active Directory logs can be voluminous, so running queries without filters can return a lot of data. Be sure to test during off-peak hours or in a test environment if possible to avoid impacting the performance of your directory.
Sandeep G-MSFT 20,921 Reputation points Microsoft Employee Moderator

2023-08-16T04:30:02.92+00:00

@David Trevor

Just checking in to see if the below answer helped. If this answers your query, please don’t forget to click "Accept the answer" and Up-Vote for the same, which might be beneficial to other community members reading this thread.
David Trevor 321 Reputation points

2023-08-17T13:57:21.6+00:00

If I wanted ChatGPT to answer my question, I would have done it myself. So no, the answer certainly did not help.
Givary-MSFT 35,626 Reputation points Microsoft Employee Moderator

2023-08-18T08:57:38.06+00:00

@Peter Kayode Thank you for contributing to Microsoft Q&A. In an effort to maintain the integrity of answers that are posted, the use of AI tools are allowed as long as the following requirements have been met:

The answer must clearly indicate that it was fully or partially generated by an AI service, and list which AI service was used.

AI generated answers must be validated as providing a useful answer to the question being asked before posting.

If the AI service provides attribution links, those should be included with the AI response.

Failure to meet these requirements may result in suspension. For more information, please review the Artificial Intelligence (AI) usage policy for Microsoft Q&A:
https://learn.microsoft.com/en-us/answers/support/ai-usage-policy

Thanks,

Girish
Givary-MSFT 35,626 Reputation points Microsoft Employee Moderator

2023-08-21T06:43:03.58+00:00

@David Trevor Apologies for the delayed response and thank you for your feedback on this post.

Please send me an email to 'AzCommunity@microsoft.com' with Sub - Attn: Givary and following details in the email body:

Link to this thread/post

Azure Subscription ID, so that we can provide free support option to investigate on this issue.

Answer 1

David Trevor 321

Resolved the issue by connecting to Graph with the following scopes:

Connect-Graph -Scopes AuditLog.Read.All,Directory.Read.All

Share via

Get-MgBetaAuditLogSignIn with filter signInEventTypes does not return all results

1 answer

Your answer