• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Sco Openserver
    • Sco Unix Support
    • SCO Openserver Installation ISOs
    • SCO Openserver Binaries
    • Add a Hard Drive to an Openserver 5 Virtual Machine
  • Scripting
    • PowerShell
      • Add leading zeros
      • Check if a File Exists
      • Grep with Powershell
      • Create Environment Variables
      • Test for open Ports
      • Append to a Text File
    • VBScript
      • Check if a File Exists
      • lpad and rpad functions
      • Windows Update E-Mail Notification
  • Office 365
    • Connect to Office 365 with PowerShell
    • Add or remove an email alias using Powershell
    • Change Primary email address of Active Directory user
    • How to hide an AD user from the Global Address List
    • How to hide mail contacts from the Global Address List
    • Change the primary email address for an account with PowerShell
    • Change Primary email address of an AD User
    • Grant a single user access to access to all calendars
    • Forward email to an external address using Powershell
    • Convert shared mailbox to user mailbox with Powershell
  • Get in touch
  • About Me
    • Privacy Policy

Log RDS Sessions with a Power BI Streaming Dataset

November 14, 2019 by Paulie 1 Comment

After a conversation with a customer today, I needed to create an easy way to monitor RDP authentications. This turned out to be more difficult than I expected. We suspected that some accounts had been compromised.

I decided to combine a few things to enrich the data from the event log and make analysis simple:

  • A custom event log trigger to execute a PowerShell Script.
  • IPStack to enrich the client IP Information.
  • A streaming PowerBI Dataset to record and visualise the data.
    This video on Streaming datasets from Patrick Leblanc was the inspiration.

It worked out pretty well, here is a screenshot of the Power BI Report:

Power BI report showing details of Successful Remote Desktop Logons
The customer does not have any users outside of the UK!

Step one: Triggering an event when a user successfully authenticates an RDS Session

When a user logs on to a terminal server a number of events are recorded in the event log. I found event 4624 with a logon type of 10 is the easiest to attach to and provides a good source of data.

Image showing Event 4624, Logon Type 10 in the Windows Event Viewer, which indicates a successful user logon via terminal services.
The event data includes the username, client IP address, date & time etc.

There is a good post on Technet which describes how to trigger a PowerShell script when a Windows Event is logged. Follow those instructions but make the following changes to the XML export routine.

Trigger the event to fire on Event ID 4624 and Logon Type 10

Because multiple 4624 events occur whenever an RDS session is logged on, you need to create a custom XML Filter in the event log to further narrow down the results to show only those that have Logon Type set to 10, as per the following:

<QueryList>
  <Query Id="0">
    <Select Path="Security">
		*[System[(EventID=4624)]]
		and
		*[EventData[Data[@Name='LogonType'] and (Data='10')]]
	</Select>
  </Query>
</QueryList>
Custom XPath Filter to narrow down the 4624 Events to Logon Type 10

Although this filter works fine in the event viewer, if you create a trigger from it, it will only filter on Event ID 4624, which causes the PowerShell script to execute too many times.

We also need to extract the Event Record ID from the trigger to be sent to the script. I’ve put the example XML on to pastebin:

Click here to see the XML Code changes required

Once you have modified the XML Scheduled Task file, recreate the task as per the technet article.

Step Two: Tweak the scheduled task

After you have created the trigger on event ID 4624 you need to modify it slightly to allow parallel execution. If multiple people logon at the same time, you still want the script to execute.

Here is my scheduled task setup:

Step Three: Powershell Script to Post Successful Terminal Server Authentications to PowerBI

Next up is the actual PowerShell Script that posts event log data to Power BI. The basic flow of the script is like this:

  1. Receive the Event Record ID from the Scheduled Task and query the event log for full details of the event.
  2. Check the Logon Type was “10”. This is the logon type associated with a Terminal Server session.
  3. Extract the Information from the XML of the Event Log
  4. Connect to IP Stack and query it for information regarding the IP Address
  5. Create a JSON payload and send it to PowerBI for further analysis.

Here is the script:

param([string]$eventRecordID = "none")

$DebugPreference = "Continue"
#The PowerBI Streaming Data Set Endpoint and IP Stack API Key
$PBIendpoint = "Replace_With_PBI_Endpoint"
$IPStackAPIKey="Replace_With_IP_Stack_API_Key"

Write-Debug "Event Record ID is $eventRecordID"

if ($eventRecordID -ne "none") {
    #Get the event detail for event ID 4624 (Successful Logon)
    $evtPath = @"
        <QueryList>
          <Query Id='0' Path='Security'>
            <Select Path='Security'>
              *[System[(EventRecordID=$eventRecordID)]]
            </Select>
          </Query>
        </QueryList>
"@

    $event = get-winevent -LogName 'Security' -FilterXPath "$evtPath"
    $eventXML = [xml]$event.ToXML()
    $logonType = $eventXML.Event.EventData.Data[8].'#text'

    #Check the logon type is 10 (Remote Desktop)
    if ($logonType -eq "10")
    {
        Write-Debug "Logon Type is 10, sending data to Power BI"

	    $eventTime = $event.TimeCreated.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss.fffZ")
	    $eventUser = $eventXML.Event.EventData.Data[5].'#text'.ToString() 
	    $eventClientIP = $eventXML.Event.EventData.Data[18].'#text'.ToString()

	    
	    $ipInfo = Invoke-WebRequest `
            "http://api.ipstack.com/$eventClientIP`?access_key=$IPStackAPIKey" -Method POST |
            ConvertFrom-JSON

	    $payload = @{
	        "Time" ="$eventTime"
	        "User" ="$eventUser"
	        "Client IP" = "$($eventClientIP)"
	        "Continent" = "$($ipInfo.continent_code)"
	        "Country" = "$($ipInfo.country_code)"
	        "Region" = "$($ipInfo.region_name)"
	        "Zip" = "$($ipInfo.zip)"
	        "latitude" ="$($ipInfo.latitude)"
	        "longitude" ="$($ipInfo.longitude)"
	    }
	    Invoke-RestMethod -Method Post -Uri "$PBIendpoint" -Body (ConvertTo-Json @($payload))
    }
    else
    {
        Write-Debug "Logon Type is $logonType, not logging"
    }
}
else
{
    Write-Debug "No event information passed to script"
}

Once the data is in PowerBI, it is simple to create a report to see the following information about the users of your terminal server environment:

  • Who is logging on
  • When they are logging on
  • The country they are in
  • The location within that country

IPStack also provide a threat level based on the reputation of the IP, but I was using a free account, so that information was not available to me.

Create your PowerBI Streaming Dataset

Follow the video Patrick Leblanc from Guy in a Cube did on creating a streaming dataset in Power BI:

This is how to configure the Streaming dataset for use with the PowerShell script above:

Step Four: Build the Power BI Report

Building the report is pretty simple, and once it was running I could immediately identify three separate accounts that had been compromised:

Image showing Power BI Report
This customer does not have any users based outside of the UK!

Out of interest I shadowed the compromised accounts on the terminal server before securing them, just to see what they were getting up to. It was mainly a lot of mass emailing via gmail and activity on “tagged.com” communicating with guys looking to buy a “pet”:

I’m pretty pleased with how it turned out, now it is dead easy to monitor terminal server sessions via PowerBI and so much more usable than the event log. The data comes through to PowerBI virtually instantly, so it’s a really good use case.

Related

Filed Under: How To Tagged With: Power BI, Remote Desktop

Reader Interactions

Comments

  1. John Lilleystone says

    November 14, 2019 at 3:17 pm

    Nice post Paul! I find the event log a pretty unfriendly tool so dumping it out to PowerBI sounds like a great idea.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar