[docs] Analyse gaps and impact

[Ioana37]

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]

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

[github-actions]

This issue has been marked stale because it has been open for 60 days with no activity.