Analysis of storage analytics logs

 https://learn.microsoft.com/en-us/azure/storage/common/storage-analytics

1. Setup Azure Log analytics, see above

2. Download blobs from $log container, use storage explorer.

3. Build a single csv using this file. 

 .\XLog2CSV.ps1 -inputFolder "C:\temp\orthanc\blob\2023\11\" -outputFile c:\temp\november.csv

4. Import CSV into sql.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[nov2023]') AND type in (N'U'))

DROP TABLE [dbo].[nov2023]

GO

CREATE TABLE [dbo].[nov2023](

    [Log_Version] [float] NOT NULL,

    [Transaction_Start_Time] [varchar](50) NOT NULL,

    [REST_Operation_Type] [varchar](50) NOT NULL,

    [Request_Status] [varchar](50) NOT NULL,

    [HTTP_Status_Code] [smallint] NOT NULL,

    [E2E_Latency] [smallint] NOT NULL,

    [Server_Latency] [smallint] NOT NULL,

    [Authentication_type] [varchar](50) NOT NULL,

    [Requestor_Account_Name] [varchar](50) NULL,

    [Owner_Account_Name] [varchar](50) NULL,

    [Service_Type] [varchar](50) NULL,

    [Request_URL] [varchar](1000) NULL,

    [Object_Key] [varchar](150) NULL,

    [Request_ID] [varchar](50) NULL,

    [Operation_Number] [tinyint] NULL,

    [Client_IP] [varchar](50) NULL,

    [Request_Version] [date] NULL,

    [Request_Header_Size] [smallint] NULL,

    [Request_Packet_Size] [int] NULL,

    [Response_Header_Size] [smallint] NULL,

    [Response_Packet_Size] [int] NULL,

    [Request_Content_Length] [int] NULL,

    [Request_MD5] [varchar](50) NULL,

    [Server_MD5] [varchar](50) NULL,

    [ETag] [varchar](50) NULL,

    [Last_Modified_Time] [varchar](50) NULL,

    [ConditionsUsed] [varchar](50) NULL,

    [User_Agent] [varchar](150) NULL,

    [Referrer] [varchar](1) NULL,

    [Client_Request_ID] [varchar](200) NULL,

    [User_Object_ID] [varchar](50) NULL,

    [Tenant_ID] [varchar](50) NULL,

    [Application_ID] [varchar](50) NULL,

    [Audience] [varchar](50) NULL,

    [Issuer] [varchar](100) NULL,

    [User_Principal_Name] [varchar](100) NULL,

    [Unused_Field] [varchar](1) NULL,

    [Authorization_Detail] [varchar](1200) NULL

) ON [PRIMARY]

GO

bulk insert [dbo].[nov2023] from "z:\november.csv" with ( FORMAT = 'CSV');

5.Analysis

--SELECT TOP (1000) * FROM [orthanc].[dbo].[output1]

--  select COUNT(*) from output1

--  select

-- Get IP address count

--select  CASE charindex(':',Client_IP)

--     WHEN 0 THEN Client_IP

--    else left(Client_ip,charindex(':',Client_IP)-1)

--END as Client_IP, count(*) as cnt

--from output1

--group by CASE charindex(':',Client_IP)

--     WHEN 0 THEN Client_IP

--    else left(Client_ip,charindex(':',Client_IP)-1)

--END

--order by 2 desc

select  CASE charindex(':',Client_IP)

     WHEN 0 THEN Client_IP

    else left(Client_ip,charindex(':',Client_IP)-1)

END as Client_IP

from output1