scan read - preranasingha/API-Post-read GitHub Wiki

let

headers = [#"Content-Type"="application/x-www-form-urlencoded"],
postData="grant_type=refresh_token&client_id=ast-app&refresh_token="&#"RefreshToken",


response = Web.Contents("https://iam.checkmarx.net",
    
    [RelativePath = "/auth/realms/cxone-humana/protocol/openid-connect/token",
        Headers = headers,
        Content = Text.ToBinary(postData)
    ]
),
jsonResponse = Json.Document(response),
AccessToken = jsonResponse[access_token],
UnixStartDate = Text.From(Date.Year(Date.StartOfMonth(Date.From(DateTime.LocalNow()))))&"-" & Text.PadStart(Text.From(Date.Month(Date.StartOfMonth(Date.From(DateTime.LocalNow())))),2,"0") &"-" & Text.PadStart(Text.From(Date.Day(Date.StartOfMonth(Date.From(DateTime.LocalNow())))),2,"0")&"T00:00:00.010000Z",
UnixEndDate = Text.From(Date.Year(Date.StartOfMonth(Date.From(DateTime.LocalNow()))))&"-" & Text.PadStart(Text.From(Date.Month(Date.StartOfMonth(Date.From(DateTime.LocalNow())))),2,"0") &"-" & Text.PadStart(Text.From(Date.Day(Date.EndOfMonth(Date.From(DateTime.LocalNow())))),2,"0")&"T00:00:00.010000Z", 
Source = Json.Document(Web.Contents("https://ast.checkmarx.net",[RelativePath = "/api/scans?limit=0&from-date="&#"UnixStartDate"&"&to-date="&#"UnixEndDate"&"&statuses=Completed", 
     Headers = [#"Authorization"=AccessToken]
 ])),
scans = Source[scans],
#"Converted to Table" = Table.FromList(scans, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"statusDetails", "createdAt", "updatedAt"}, {"statusDetails", "createdAt", "updatedAt"}),
#"Expanded statusDetails" = Table.ExpandListColumn(#"Expanded Column1", "statusDetails"),
#"Expanded statusDetails1" = Table.ExpandRecordColumn(#"Expanded statusDetails", "statusDetails", {"name"}, {"name"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded statusDetails1", each ([name] = "sast")),
#"Duplicated Column1" = Table.DuplicateColumn(#"Filtered Rows", "createdAt", "createdAt - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column1", "createdAt - Copy", Splitter.SplitTextByEachDelimiter({"T"}, QuoteStyle.Csv, false), {"createdAt - Copy.1"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"createdAt - Copy.1", type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type3",{{"createdAt", type datetime}, {"updatedAt", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [updatedAt]-[createdAt]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}}),
#"Calculated Start of Month" = Table.TransformColumns(#"Changed Type",{{"createdAt - Copy.1", Date.StartOfMonth, type datetime}}),
#"Grouped Rows" = Table.Group(#"Calculated Start of Month", {"createdAt - Copy.1"}, {{"SAST Count", each Table.RowCount(_), Int64.Type}, {"Min Date", each List.Min([createdAt]), type nullable datetime}, {"Max Date", each List.Max([updatedAt]), type nullable datetime}, {"Avg Scan Time", each List.Average([Custom]), type nullable duration}}),
#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Avg Scan Time", "Average Scan Time"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"createdAt - Copy.1", type text}, {"Average Scan Time", type text}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type2", {{"Average Scan Time", each Text.BeforeDelimiter(_, "."), type text}})

in #"Extracted Text Before Delimiter"