Forum Discussion

Iron Contributor

Oct 07, 2023

Solved

How to measure egress for Storage Account and whether it has exceeded x GiB in y minutes?

Hello, I am trying to find a KQL query that can scan any Storage Account and verify, through an alert metric, whether it has exceeded x GiB in y minutes. I know it is possible to set up an al...

data collection

KQL

Log Data

JMSHW0420
Oct 10, 2023
Hi Clive,

The below query actually provides the solution I require.

StorageBlobLogs
| where TimeGenerated between ( startofday(ago(2d)) .. endofday(ago(1d)) )
| where OperationName == "GetBlob"
| extend IPAddress = tostring(split(CallerIpAddress,':')[0])
| join
(
SigninLogs
| where isnotempty(IPAddress)
)
on IPAddress
| summarize ReadSize = sum(ResponseBodySize) by AccountName, UserPrincipalName, bin(TimeGenerated, 6hr)
| where ReadSize > 10000

Thanks for your help.

Clive_Watson

Bronze Contributor

Oct 09, 2023

You might get lucky and find logon details in one of the AAD tables - like SigninLogs, but if not the IP maybe your only clue

Add this to the end of the query

...
| where OperationName == "GetBlob" and CallerIpAddress in~ (users)
| extend IPAddress = tostring(split(CallerIpAddress,':')[0])
|join
(
SigninLogs // try other Tables as well
| where isnotempty(IPAddress)
)
on IPAddress

JMSHW0420

Iron Contributor

Oct 10, 2023

Hi Clive,

The below query actually provides the solution I require.

StorageBlobLogs
| where TimeGenerated between ( startofday(ago(2d)) .. endofday(ago(1d)) )
| where OperationName == "GetBlob"
| extend IPAddress = tostring(split(CallerIpAddress,':')[0])
| join
(
SigninLogs
| where isnotempty(IPAddress)
)
on IPAddress
| summarize ReadSize = sum(ResponseBodySize) by AccountName, UserPrincipalName, bin(TimeGenerated, 6hr)
| where ReadSize > 10000

Thanks for your help.