Fabric Workspace Activity Location Based On IP Address Using KQL

This is a second blog in a row inspired by Edgar Cotte (Sr PM, Fabric CAT). At a recent RTI workshop, he shared a handy KQL function geo_info_from_ip_address which retrieves geolocation based on IP address. You can read more about it here.

A few months ago I wrote a blog on getting workspace activities using Semantic Link Labs. I have been using it on one of my workspaces in personal tenant to generate activity data. I retrieve the logs for each day and save it to a lakehouse. The activity event logs have Client IP address and I wanted to try above function on this field. So I shortcutted the delta table to a KQL table in an Eventhouse and it worked like a charm. Super helpful for auditing & monitoring the workspace activities.

## GET ACTIVITIES FOR THE LAST 7 DAYS AND SAVE IT TO A LAKEHOUSE USING POLARS
## USING PYTHON NOTEBOOK BELOW

from datetime import datetime, timedelta
import pandas as pd
import sempy_labs as labs
import polars as pl

#last 7 days
N=7

activities = []
for n in range(N):
    day = datetime.now() - timedelta(days=n)
    start_of_day = day.replace(hour=0, minute=0, second=0, microsecond=0).strftime('%Y-%m-%dT%H:%M:%S')
    end_of_day = day.replace(hour=23, minute=59, second=59, microsecond=999999).strftime('%Y-%m-%dT%H:%M:%S')

    df = labs.admin.list_activity_events(
        start_time=start_of_day, 
        end_time=end_of_day
    ) 
    activities.append(df)

final_df = pd.concat(activities)
pl_df = pl.from_pandas(final_df)
## change to your abfss
pl_df.write_delta("abfss://Sales@onelake.dfs.fabric.microsoft.com/MyLakehouse.Lakehouse/Tables/dbo/ws_activities", mode="overwrite")

In the Eventhouse, I added the above table as a shortcut and queried to aggregate by location using geo_info_from_ip_address:

//table name "ws_activities"

external_table("ws_activities")
| extend Location = geo_info_from_ip_address(['Client IP'])
| extend LocationString = strcat_delim(", ", 
    tostring(Location.city), 
    tostring(Location.state), 
    tostring(Location.country))
| extend Latitude = todouble(Location.latitude)
| extend Longitude = todouble(Location.longitude)
| where isnotempty(LocationString) and LocationString != ", , "
| where isnotempty(Latitude) and isnotempty(Longitude)
| summarize EventCount = count() by LocationString, Latitude, Longitude

I live in Portland, OR and recently travelled to Seattle, WA so this checks out:

Rendered the result as a map which shows Portland and Seattle.

The same map is available in Real Time Dashboard so you can track the location of activities in real time and generate alerts if needed!