Extracting Azure VM Logs on User Logon & VM Startup & Shutdown.

[Alan2022]

Sharing Mode. 
Getting Logs on Analytic Workspace.

# List of VM with Startup/Shutdown/UserLogin/UserLogoff Logs for 1day

# Create Report Array
$report = @()

# Get Log Analytic Workspace
$ws = Get-AzOperationalInsightsWorkspace -Name "<Name>" -ResourceGroupName "<ResourceGroupName>"

# Create Query for Data Extraction KQL - Kusto Query Language For 7days
$query = @"
Event 
| where EventID == 7001 or EventID == 7002 or EventID == 12 or EventID == 13 
| where Source has "Winlogon" or Source has "Kernel-General"
| where TimeGenerated > ago(1d)
| order by TimeGenerated asc
"@

# EventID 12 - OS Startup
# EventID 13 - OS Shutdown
# EventID 7001 - User Login
# EventID 7002 - User Logoff

# Execute KQL Query
$resultQueries = (Invoke-AzOperationalInsightsQuery -WorkspaceId $ws.CustomerId.Guid -Query $query).Results | Select-Object -Property @{n="Computer"; e={ $_.Computer -replace '<domain name>','' }}, @{n="UserName"; e={ $_.ParameterXML -replace '.*</Param><Param>','' -replace '</Param>','' }}, @{n="TimeGenerated"; e={ $_.TimeGenerated }}, @{n="EventID"; e={ $_.EventID }}, @{n="Remarks"; e={ $_.RenderedDescription }}  
 
# Get All Virtual Machines based on ResourceName
$resultVMs = Get-AzVM -ResourceGroupName "<ResourceGroupName>" | Select-Object -Property @{n="VMName"; e={ $_.Name }}, @{n="ResourceGroupName"; e={ $_.ResourceGroupName }} | Sort-Object -Property VMName

# Loop on the List of VM
foreach($resultVM in $resultVMs){

    # Filter for Computer Name in resultQueries
    @($resultQueries | Where-Object { $_.Computer -eq $resultVM.VMName }) | Sort-Object -Property TimeGenerated | ForEach-Object {
        
        # Creating Report Header
        $reportdetails = "" | Select VMName, ResourceGroup, Username, TimeGenerated, EventID, Remarks

        # For Winlogon username
        if($_.EventID -eq 12 -Or $_.EventID -eq 13){
            $temp = ""
        }
        else{
            $temp = $_.Username
        }

        $reportdetails.VMName = $resultVM.VMName
        $reportdetails.ResourceGroup = $resultVM.ResourceGroupName
        $reportdetails.Username = $temp
        $reportdetails.TimeGenerated = (Get-Date $_.TimeGenerated -f "MM.dd.yyyy hh.mm tt")
        $reportdetails.EventID = $_.EventID
        $reportdetails.Remarks = $_.Remarks

        $report+=$reportdetails
    }
}

# View Reports
$report | Export-Excel "$($env:USERPROFILE)\Desktop\VMList_$(get-date -f "MM.dd.yyyy hh.mm tt").xlsx" -Autosize
Write-Host "Finished..."