search

balteravishay / scorecard

Security Scorecards - Security health metrics for Open Source
Apache License 2.0
1 stars 0 forks source link

[docs] Analyse gaps and impact #6

Open Ioana37 opened 1 month ago

Ioana37 commented 1 month ago

The goal here is to analyse the gaps that exist in the .Net ecosystem in the security metrics that scorecard checks for, so that when we publish the blog post we can make a case that is backed by real data as to where the community should put its efforts.

In high level if we could get GitHub repositories that are nuget/.Net based and examine the scorecard checks metrics for them we could know what are the trending lows and trending highs for the nuget/.Net ecosystem.

We can start with the data that exists in CloudMine using the query in the comments to extract nuget repos that are Microsoft owned, and leverage PowerBI over the BigQuery open dataset that has the socrecard data for those repos.

Later we can explore how to retrieve more non-MS repos from GitHub and feed them into the dashboard.

balteravishay commented 1 month ago
cluster("ospo").database("corporateprod").clearlyDefinedDefinitions project id,type,provider,namespace,name,revision,sourceLocation,projectWebsite extend homeLink = strcat("https://github.com/",split(sourceLocation,"/")[3],"/",split(sourceLocation,"/")[4]) extend sourceLocationOrgName = strcat(split(sourceLocation,"/")[3],"/",split(sourceLocation,"/")[4]) join kind =inner(cluster("1es").database("GitHub").Repository project IsPrivate,Archived,FullName) on $left.sourceLocationOrgName==$right.FullName where Archived == false where IsPrivate == false
extend typeCapitalized=strcat(toupper(substring(type,0,1)),tolower(substring(type,1)))
extend typeCapitalized=iif(typeCapitalized=="Nuget","NuGet",typeCapitalized)
extend typeCapitalized=iif(typeCapitalized=="Pypi","Pip",typeCapitalized)
extend typeCapitalized=iif(typeCapitalized=="Gem","RubyGems",typeCapitalized)
extend typeCapitalized=iif(typeCapitalized=="Crate","Cargos",typeCapitalized)
extend comparisonKeyCalculated=strcat(tolower(namespace),"/",tolower(name)," ",revision," -",typeCapitalized)
extend comparisonKeyCalculated=iif(substring(comparisonKeyCalculated,0,1)=="/",substring(comparisonKeyCalculated,1),comparisonKeyCalculated)
extend comparisonKeyCalculated=iif(typeCapitalized=="Maven",strcat(tolower(namespace),":",tolower(name)," ",revision," -",typeCapitalized),comparisonKeyCalculated)
extend comparisonKeyCalculated= iif(substring(comparisonKeyCalculated,0,1)==":",substring(comparisonKeyCalculated,1),comparisonKeyCalculated)
where typeCapitalized=="NuGet"

distinct id,type,provider,namespace,name,sourceLocation,projectWebsite,homeLink,FullName,typeCapitalized