Logic Apps: Parsing JSON and Filtering User Data Based on Last Sign-in Activity

Question

I've defined the following logic in Logic Apps, but Parse JSON isn't capturing the signInActivity details, so I can't filter the object based on lastSignInDateTime. Basically, I'm looking for users who have an office 365 e1 license and whose lastSignInDateTime and lastNonInteractiveSignInDateTime are old (Inactive).

{
    "properties": {
        "value": {
            "items": {
                "properties": {
                    "accountEnabled": {
                        "type": "boolean"
                    },
                    "city": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "companyName": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "country": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "department": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "displayName": {
                        "type": "string"
                    },
                    "employeeId": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "jobTitle": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "mail": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "onPremisesDomainName": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "onPremisesExtensionAttributes": {
                        "properties": {
                            "extensionAttribute1": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute10": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute11": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute12": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute13": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute14": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute15": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute2": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute3": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute4": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute5": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute6": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute7": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute8": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "extensionAttribute9": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            }
                        },
                        "type": "object"
                    },
                    "onPremisesSamAccountName": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "onPremisesUserPrincipalName": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "signInActivity": {
                        "properties": {
                            "lastNonInteractiveSignInDateTime": {
                                "type": [
                                    "String",
                                    "null"
                                ]
                            },
                            "lastNonInteractiveSignInRequestId": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            },
                            "lastSignInDateTime": {
                                "type": [
                                    "String",
                                    "null"
                                ]
                            },
                            "lastSignInRequestId": {
                                "type": [
                                    "string",
                                    "null"
                                ]
                            }
                        },
                        "type": "object"
                    },
                    "state": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "userPrincipalName": {
                        "type": [
                            "string",
                            "null"
                        ]
                    }
                },
                "required": [
                    "accountEnabled",
                    "city",
                    "companyName",
                    "country",
                    "department",
                    "displayName",
                    "employeeId",
                    "jobTitle",
                    "onPremisesDomainName",
                    "mail",
                    "onPremisesSamAccountName",
                    "onPremisesUserPrincipalName",
                    "state",
                    "userPrincipalName",
                    "onPremisesExtensionAttributes"
                ],
                "type": "object"
            },
            "type": "array"
        }
    },
    "type": "object"
}

Answer 1

Hello 67868880,

Thanks for posting!

As per my testing, Multiple filter query parameter is not supporting the "assignedLicenses" and "signInActivity/lastSignInDateTime" properties in same Graph API call.

Please refer the below Graph Endpoint to get old users lastSignInDateTime and lastNonInteractiveSignInDateTime.

https://graph.microsoft.com/beta/users?select=displayName,signInActivity&filter=signInActivity/lastSignInDateTime le 2019-06-01T00:00:00Z

By adjusting the filter query within a single $filter operator, you can build more complex filter queries.

Please refer below:

https://graph.microsoft.com/beta/users?$count=true&$filter=signInActivity/lastSignInDateTime le 2022-09-01T00:00:00Z&$filter=endsWith(mail,'@alumni.xxx.xxx')

However, as per my testing adjusting multiple filters in above scenario is not supported.

Hope this helps.

If the answer is helpful, please click Accept Answer and kindly upvote. If you have any further questions about this answer, please click Comment.