Get-MGAuditLogSignin - How to use a variable in a filter

[barabetto]

Hi,

I am new to working with Graph and I am trying to create a script that gets the last logon for certain users
If I run the following command for a specific account I get last logon information back:
Get-MgAuditLogSignIn -All -Filter "userPrincipalName eq 'username@domain.com'" -Top 1 | select userprincipalname, createddatetime

I have a variable that contains the UPN of an account $UPN
If I use the following, nothing is returned:
Get-MgAuditLogSignIn -All -Filter "userPrincipalName eq '$UPN'" -Top 1 | select userprincipalname, createddatetime

I am guessing it is something to do with how the variable is entered in the filter but I can't seem to find much documentation.

In my script:
I get the UPN in this way:
$User = Get-MgUser -userid username@domain.com
[string]$upn = $user.userprincipalname

however if I manually enter an account as follows, the script works:
$upn = "username@domain.com"

I have run $upn.gettype() and for both instances it returns a type of string.

I am running in PowerShell 7

Any help would be greatly appreciated.

Thank You

[SeniorConsulting]

Normally when you use single quotes in PowerShell, it treats things as a literal, so your $UPN variable is no longer your object, but instead is literally the characters $UPN.

As you've correctly established, the filter command needs those single quotes around them. To pass them nicely to the Graph API, you will probably need to escape the single quotes using the backtick, so that instead of the $UPN being treated as literal characters, the quotes are:

In your example, this looks like:
Get-MgAuditLogSignIn -All -Filter "userPrincipalName eq `'$UPN`'" -Top 1 | select userprincipalname, createddatetime

[barabetto]

Thank you for your comments and I think you are on the right track but I still doesn't work.
Note I am running PowerShell V7.3 and I'm not sure if the escaping of strings etc has changed and it is added to the issue:
https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_parsing?view=powershell-7.3#passing-arguments-that-contain-quote-characters

[alexandair]

@barabetto, your code works for me without any changes:

PS> Get-MgAuditLogSignIn -All -Filter "userPrincipalName eq 'aleksandar@mydomain.onmicrosoft.com'" -Top 1 | select userprincipalname, createddatetime

UserPrincipalName                CreatedDateTime
-----------------                ---------------
aleksandar@mydomain.onmicrosoft.com 7/10/2023 2:27:42 PM

PS> $upn = 'aleksandar@mydomain.onmicrosoft.com'
PS> Get-MgAuditLogSignIn -All -Filter "userPrincipalName eq '$upn'" -Top 1 | select userprincipalname, createddatetime

UserPrincipalName                CreatedDateTime
-----------------                ---------------
aleksandar@mydomain.onmicrosoft.com 7/10/2023 2:27:42 PM

[SeniorConsulting]

Thanks Alexandair, I'm currently unable to test on 7.3 at the moment. If you're running 7.3 in your lab, could you please also test what barabetto is doing (re: getting the UPN from a variable after using Get-MgUser)?
Their exact code is:

$User = Get-MgUser -userid username@domain.com
[string]$upn = $user.userprincipalname

and then move onto the
Get-MgAuditLogSignIn -All -Filter "userPrincipalName eq '$UPN'" -Top 1 | select userprincipalname, createddatetime

[alexandair]

@SeniorConsulting That works too.

 PS> $user = get-mguser -UserId 'aleksandar@mydomain.onmicrosoft.com'
 PS> [string]$upn = $user.UserPrincipalName
 PS> Get-MgAuditLogSignIn -All -Filter "userPrincipalName eq '$UPN'" -Top 1 | select userprincipalname, createddatetime

UserPrincipalName                CreatedDateTime
-----------------                ---------------
aleksandar@mydomain.onmicrosoft.com 7/10/2023 8:55:24 PM

[SeniorConsulting]

Thanks Alexandair.

Barabetto, it might be worth running the commands with the -debug flag, and/or including some screenshots so that we've got an idea of what you're working with. It's hard to tell when most things come back OK. If you do include screenshots, feel free to sanitise any data you would prefer us not to see (e.g. full UPN names).

My only theory at the moment is whether the following is case sensitive:
[string]$upn = $user.userprincipalname

If it is, writing the output of $UPN would return a blank value. I tested this theory in 5.1, and had no problems, but it might be a little different for you.

[alexandair]

It's not case sensitive. I've tested that too.

…

________________________________ From: SeniorConsulting ***@***.***> Sent: Monday, July 10, 2023 11:35:02 PM To: microsoftgraph/msgraph-sdk-powershell ***@***.***> Cc: Aleksandar Nikolić ***@***.***>; Comment ***@***.***> Subject: Re: [microsoftgraph/msgraph-sdk-powershell] Get-MGAuditLogSignin - How to use a variable in a filter (Issue #2128) Thanks Alexandair. Barabetto, it might be worth running the commands with the -debug flag, and/or including some screenshots so that we've got an idea of what you're working with. It's hard to tell when most things come back OK. If you do include screenshots, feel free to sanitise any data you would prefer us not to see (e.g. full UPN names). My only theory at the moment is whether the following is case sensitive: [string]$upn = $user.userprincipalname If it is, writing the output of $UPN would return a blank value. I tested this theory in 5.1, and had no problems, but it might be a little different for you. — Reply to this email directly, view it on GitHub<#2128 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAX5RXFCE5V6FFWPWJUILMTXPRYQNANCNFSM6AAAAAA2AWG6MY>. You are receiving this because you commented.Message ID: ***@***.***>

[barabetto]

Thanks for all your help.

So I have entered the commands on the command line the same as alexandair, however it does not find anything :

PS C:> connect-mggraph -Scopes auditlog.read.all

$user = get-mguser -UserId 'username@domain.com'
PS C:> [string]$upn = $user.UserPrincipalName
PS C:> Get-MgAuditLogSignIn -debug -All -Filter "userPrincipalName eq '$UPN'" -Top 1 | select userprincipalname, createddatetime
DEBUG: [CmdletBeginProcessing]: - Get-MgAuditLogSignIn begin processing with parameterSet 'List'.
DEBUG: [Authentication]: - AuthType: 'Delegated', AuthProviderType: 'InteractiveAuthenticationProvider', ContextScope: 'CurrentUser', AppName: 'Microsoft Graph Command Line Tools'.
DEBUG: [Authentication]: - Scopes: [Application.Read.All, AppRoleAssignment.ReadWrite.All, AuditLog.Read.All, Chat.Create, Chat.Read, Chat.ReadBasic, Chat.ReadWrite, ChatMessage.Read, ChatMessage.Send, Domain.Read.All, email, Group.ReadWrite.All, openid, profile, User.Read, User.Read.All].
DEBUG: ============================ HTTP REQUEST ============================

HTTP Method:
GET

Absolute Uri:
https://graph.microsoft.com/v1.0/auditLogs/signIns?$top=1&$filter=userPrincipalName eq %27username%40domain.com%27

Headers:
FeatureFlag : 00000047
Cache-Control : no-store, no-cache
SdkVersion : graph-powershell/1.22.0,Graph-dotnet-1.25.1
User-Agent : Mozilla/5.0,(Windows NT 10.0; Microsoft Windows 10.0.19045; en-GB),PowerShell/7.3.5
Accept-Encoding : gzip

Body:

DEBUG: ============================ HTTP RESPONSE ============================

Status Code:
OK

Headers:
Transfer-Encoding : chunked
Vary : Accept-Encoding
Strict-Transport-Security : max-age=31536000
request-id : 861c571b-dc7b-4d1d-b152-1e410611750e
client-request-id : 861c571b-dc7b-4d1d-b152-1e410611750e
x-ms-ags-diagnostic : {"ServerInfo":{"DataCenter":"UK South","Slice":"E","Ring":"3","ScaleUnit":"003","RoleInstance":"LO2PEPF000004F4"}}
OData-Version : 4.0
Date : Wed, 12 Jul 2023 12:39:30 GMT

Body:
{
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#auditLogs/signIns",
"value": []
}

If I enter the name of the user manually then it works:

PS C:> Get-MgAuditLogSignIn -All -Filter "userPrincipalName eq 'username@domain.com'" -Top 1 | select userprincipalname, createddatetime

UserPrincipalName CreatedDateTime

---

username@domain.com 12/07/2023 12:18:39

Just in case its my specific version of Powershell:
PS C:> $PSVersionTable

Name Value

---

PSVersion 7.3.5
PSEdition Core
GitCommitId 7.3.5
OS Microsoft Windows 10.0.19045
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

Version of mggraph:
PS C:> Get-Command -Module microsoft.graph.users

CommandType Name Version Source

---

Function Get-MgUser 1.22.0 Microsoft.Graph.Users
Function Get-MgUserCreatedObject 1.22.0 Microsoft.Graph.Users
Function Get-MgUserDirectReport 1.22.0 Microsoft.Graph.Users

in fact the list goes on and all entries are the same version

[barabetto]

I have also updated to Graph V2.0.0 and still have the same issue.

[barabetto]

I have the same issue on another computer as well.

[peombwa]

I'm also not able to repro the issue on my end using the latest version of the SDK, v2.6.1.

Per the -debug log, the service responded with empty value, which means it couldn't find a sign in logs for the provided UPN. Please troubleshoot on your end as the issue is not with the SDK, confirmed by 3 people. Alternatively, you can open a ticket at https://developer.microsoft.com/graph/support to have the service team investigate why the response is empty for the provided UPN.

[microsoft-github-policy-service]

This issue has been automatically marked as stale because it has been marked as requiring author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment.