Forum Discussion
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...
- 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.
Hi Clive,
Sorry for the slight delay in response; I have had some family commitments.
I am ideally looking for a check on blob storage egress over a timeframe (say 24 hours; it could be anything) that has exceeded 'x' GB in 'y' minutes DURING that timeframe, and possibly users who have performed the read/write actions to cause this.
So, for example, check on a blob storage egress over 24 hours and see if it has exceeded '4' GB in '60' minutes DURING that 24-hour timeframe and by which users who have performed the read/write actions to cause it.
I understand why you have suggested the 'serialize' and 'prev' functions and not sure if that meets the criteria above.
I have slightly adjusted my 'summarize' command line to refer to the Account Name now, but I still require some assistance on the time-series element and possibly identifying users performing the actions I mentioned.
//Time range added to look last 24 hours (in 6-hour time intervals) from the previous day and read size on the number of bytes that is over 10000000000 on each account
StorageBlobLogs
| where TimeGenerated between ( startofday(ago(48hrs)) .. endofday(ago(1d)) )
| where OperationName == "GetBlob"
| summarize ReadSize = sum(ResponseBodySize) by AccountName, bin(TimeGenerated, 6hr)
| where ReadSize > 10000000000
| render timechart
| order by ReadSize desc
Sorry for the slight delay in response; I have had some family commitments.
I am ideally looking for a check on blob storage egress over a timeframe (say 24 hours; it could be anything) that has exceeded 'x' GB in 'y' minutes DURING that timeframe, and possibly users who have performed the read/write actions to cause this.
So, for example, check on a blob storage egress over 24 hours and see if it has exceeded '4' GB in '60' minutes DURING that 24-hour timeframe and by which users who have performed the read/write actions to cause it.
I understand why you have suggested the 'serialize' and 'prev' functions and not sure if that meets the criteria above.
I have slightly adjusted my 'summarize' command line to refer to the Account Name now, but I still require some assistance on the time-series element and possibly identifying users performing the actions I mentioned.
//Time range added to look last 24 hours (in 6-hour time intervals) from the previous day and read size on the number of bytes that is over 10000000000 on each account
StorageBlobLogs
| where TimeGenerated between ( startofday(ago(48hrs)) .. endofday(ago(1d)) )
| where OperationName == "GetBlob"
| summarize ReadSize = sum(ResponseBodySize) by AccountName, bin(TimeGenerated, 6hr)
| where ReadSize > 10000000000
| render timechart
| order by ReadSize desc
How about?
StorageBlobLogs
| where OperationName == "GetBlob"
| make-series ReadSize = sum(ResponseBodySize) default=0 on TimeGenerated from startofday(ago(2d)) to endofday(ago(1d)) step 6h by AccountName
| project series_stats(ReadSize), AccountName
| where series_stats_ReadSize_max > 10000000000
// use can use min, avg instaed of max
StorageBlobLogs
| where OperationName == "GetBlob"
| make-series ReadSize = sum(ResponseBodySize) default=0 on TimeGenerated from startofday(ago(2d)) to endofday(ago(1d)) step 6h by AccountName
| project series_stats(ReadSize), AccountName
| where series_stats_ReadSize_max > 10000000000
// use can use min, avg instaed of max
- Hi Clive.
Oh, I like that with the series_stats, mate.
What I can still cannot extract are the users performing the read/write 'actions'. The nearest property I can see to a user is the CallerIpAddress. This returns the IP address of the requester, including the port number, assuming the requester is a user!
Have not tested this yet but a query like something like:
let users=
StorageBlobLogs
| where TimeGenerated between ( startofday(ago(48hrs)) .. endofday(ago(1d)) )
| where OperationName == "GetBlob"
| summarize ReadSize = sum(ResponseBodySize) by AccountName, bin(TimeGenerated, 6hr)
| where ReadSize > 10000000000
| distinct CallerIpAddress;
StorageBlobLogs
| where TimeGenerated between ( startofday(ago(48hrs)) .. endofday(ago(1d)) )
| where OperationName == "GetBlob" and CallerIpAddress in~ (users)
Jason- 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- 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.