# Fabric Workspace Activity Location Based On IP Address Using KQL

This is a second blog in a row inspired by [Edgar Cotte](https://www.linkedin.com/in/edgarcotte/) (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](https://learn.microsoft.com/en-us/kusto/query/geo-info-from-ip-address-function?view=microsoft-fabric).

A few months ago I wrote a blog on [getting workspace activities](https://fabric.guru/whats-your-most-active-fabric-workspace) 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.

```python
## 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`:

```python
//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:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1749508228502/596fb9bc-3f6b-4454-b42f-5f4e1a966931.png align="center")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1749508612727/9b97867c-6a56-4c47-a8c0-aaca06121fdb.png align="center")

The same map is available in [Real Time Dashboard](https://learn.microsoft.com/en-us/fabric/real-time-intelligence/dashboard-real-time-create) so you can track the location of activities in real time and generate alerts if needed!