Snippet Name: AD_User_Change_Query
Tags:
Created Date: 2025-06-16 13:12:41
Last Modified Date: 2025-05-31 10:32:08
Dependencies:
DBA Notes:
Sample Output:
Title: AD User Change Query
Author: user
I am using PowerShell, to try and find and Active Directory Uses that changed in roughly 24 hours. the query i am using is below, but I think i may be using the wrong column, $_.whenChanged to find "changes": Get-ADUser -Filter {(sAMAccountName -like '*')} -Properties * | Where-Object {$_.whenChanged -ge (Get-Date).AddDays(-1)}| Out-GridView
Author: ChatGPT
You're on the right track, but `whenChanged` might not be the best attribute for detecting user modifications in Active Directory. `whenChanged` only updates when certain attributes change and might not capture all relevant changes.
### Alternative Attributes to Consider:
- **`pwdLastSet`** ??? Password last changed
- **`lastLogonTimestamp`** ??? Approximate last login time (replicated across DCs)
- **`accountExpires`** ??? Account expiration date
- **`userAccountControl`** ??? Flags for account status (disabled, locked, etc.)
- **`msDS-UserPasswordExpiryTimeComputed`** ??? Computed password expiration time
- **`badPwdCount`** ??? Number of failed logins
### Suggested Approach:
Instead of `whenChanged`, you can check multiple attributes that indicate actual changes:
```powershell
$Users = Get-ADUser -Filter * -Properties whenChanged, pwdLastSet, lastLogonTimestamp, accountExpires, userAccountControl |
Where-Object {
$_.whenChanged -ge (Get-Date).AddDays(-1) -or
$_.pwdLastSet -ge (Get-Date).AddDays(-1) -or
$_.lastLogonTimestamp -ge (Get-Date).AddDays(-1) -or
$_.accountExpires -ge (Get-Date).AddDays(-1)
}
$Users | Select-Object Name, SamAccountName, whenChanged, pwdLastSet, lastLogonTimestamp, accountExpires, userAccountControl |
Out-GridView
```
### Notes:
1. `lastLogonTimestamp` updates only every 9-14 days, so it may not be useful for real-time tracking.
2. If you need to track **specific attribute changes**, consider using **Active Directory Change Auditing** or logs from the **Security Event Viewer**.
Would you like a more advanced solution using **PowerShell AD Change Tracking Logs**?
Author: user
i get some errors Could not compare "133770517468440481" to "02/12/2025 07:39:40". Error: "Cannot convert value "2/12/2025 7:39:40 AM" to type "System.Int64". Error: "Invalid cast from 'DateTime' to
'Int64'.""
Author: ChatGPT
The error occurs because some Active Directory attributes store timestamps as **Windows FileTime (Int64)** values rather than PowerShell `DateTime` objects. Specifically:
- `lastLogonTimestamp`
- `pwdLastSet`
- `accountExpires`
These attributes must be **converted** to a readable date format before comparison.
---
### **Updated PowerShell Script**
```powershell
$Users = Get-ADUser -Filter * -Properties whenChanged, pwdLastSet, lastLogonTimestamp, accountExpires, userAccountControl |
Where-Object {
$_.whenChanged -ge (Get-Date).AddDays(-1) -or
($_.pwdLastSet -ne 0 -and [datetime]::FromFileTime($_.pwdLastSet) -ge (Get-Date).AddDays(-1)) -or
($_.lastLogonTimestamp -ne 0 -and [datetime]::FromFileTime($_.lastLogonTimestamp) -ge (Get-Date).AddDays(-1)) -or
($_.accountExpires -ne 0 -and [datetime]::FromFileTime($_.accountExpires) -ge (Get-Date).AddDays(-1))
}
$Users | Select-Object Name, SamAccountName,
whenChanged,
@{Name='pwdLastSet'; Expression={if ($_.pwdLastSet -ne 0) {[datetime]::FromFileTime($_.pwdLastSet)} else {$null}}},
@{Name='lastLogonTimestamp'; Expression={if ($_.lastLogonTimestamp -ne 0) {[datetime]::FromFileTime($_.lastLogonTimestamp)} else {$null}}},
@{Name='accountExpires'; Expression={if ($_.accountExpires -ne 0) {[datetime]::FromFileTime($_.accountExpires)} else {'Never'}}},
userAccountControl |
Out-GridView
```
---
### **Fixes & Explanation**
1. **Fixed Type Conversion**
- Used `[datetime]::FromFileTime($_.AttributeName)` to convert **Windows FileTime (Int64)** to `DateTime`.
2. **Handled `0` Values**
- Some attributes (e.g., `pwdLastSet`, `lastLogonTimestamp`, `accountExpires`) may have a **value of 0**, meaning they were **never set**.
- If `0`, they are **ignored** in filtering and display.
3. **Formatted `accountExpires`**
- If `accountExpires = 0`, it displays `"Never"` instead of a date.
---
### **What This Does**
- Finds users modified **in the last 24 hours** (`whenChanged`).
- Finds users who:
- Changed their **password** (`pwdLastSet`).
- Logged in (`lastLogonTimestamp`).
- Had their **account expiration updated** (`accountExpires`).
- Outputs a **filtered list** with properly formatted timestamps in `Out-GridView`.
---
#### ??? **Try This and Let Me Know If You Need More Adjustments!** ????
Author: user
i have an azure sql database and an SSRS virtual machine , bith in azure, and both on the same subnet of 10.24.248.x. i need to create an SSRS data source that points to the SLq Server. how can i find the private IP of the Azure SQL Database server
Author: ChatGPT
Azure SQL Database is a **PaaS service**, which means it **does not have a fixed private IP address** like a Virtual Machine. Instead, Azure SQL uses a public endpoint with a fully qualified domain name (FQDN), such as:
```
yourserver.database.windows.net
```
However, since your **SSRS VM and Azure SQL are on the same subnet**, you might want to use **Private Link** to get a private IP address for your SQL Database.
---
### **Options to Connect SSRS to Azure SQL Database**
#### **1?????? Find the Public Endpoint**
You can use the default public endpoint (unless a private link is configured):
1. Go to the **Azure Portal**.
2. Navigate to **Azure SQL Database** > Select your **SQL Server**.
3. Under **"Overview"**, find the **Server name** (e.g., `yourserver.database.windows.net`).
4. Use this in SSRS as the data source.
---
#### **2?????? Use Private Link for a Private IP (Recommended)**
If your **Azure SQL Database is configured with Private Link**, you can find its **private IP address**:
1. Go to the **Azure Portal**.
2. Navigate to **Azure SQL Server** > **Private endpoint connections**.
3. Click on your **Private Endpoint**.
4. Look for the **Private IP address** assigned.
- If Private Link is enabled, this is the **internal IP** of your Azure SQL Server.
- If no private IP exists, you will need to **set up Private Link**.
---
#### **3?????? Set Up Private Link (If Not Already Configured)**
If you need to **force Azure SQL to use a private IP** within your subnet:
1. **Go to Azure Portal** > **Private Link Center** > **Private Endpoints**.
2. Click **+ Private Endpoint**.
3. **Resource Type:** Select **Microsoft.Sql/servers**.
4. **Target Resource:** Choose your **Azure SQL Database** server.
5. **Subnet:** Select your **existing subnet (10.24.248.x)**.
6. **DNS Configuration:** Enable **Private DNS Zone** (so your VM resolves the private IP).
7. Click **Create**.
This will assign a **private IP address** to your Azure SQL Database that your SSRS VM can use.
---
#### **4?????? Configure SSRS Data Source**
Once you have the **FQDN** (or the **private IP from Private Link**), configure SSRS:
1. Open **Report Manager** (or Report Server Configuration Manager).
2. Go to **Data Sources**.
3. Create a **new data source**.
4. Set:
- **Connection Type:** SQL Server
- **Server Name:**
- Use **private IP** (if using Private Link).
- Use **FQDN (`yourserver.database.windows.net`)** if not using Private Link.
- **Authentication:**
- **Managed Identity** (if enabled)
- **SQL Authentication** (if using SQL login)
- **Active Directory Authentication** (if using AAD)
---
### **TL;DR:**
- **No private IP by default** for Azure SQL Database.
- Use the **public FQDN (`yourserver.database.windows.net`)** for connection.
- If **Private Link** is set up, find the **private IP** in **Azure Portal ??? Private Endpoint Connections**.
- If Private Link is **not enabled**, consider **creating a Private Endpoint** to assign a private IP.
Let me know if you need help setting up **Private Link**! ????