Open Source Usage by Project This reports shows open source licenses identified during the indexing phase. Each row contains the repository, project, license and the number of files that license was associated with. Click on any license name for more details. ATTACH '%INDEX_DATABASE%' AS indexDB; SELECT s.ServerName as [Repository], p.ProjectName as [Project], '<a href=''http://www.koders.com/corp/about/developers/license-info/'' style=''border-bottom:0px; color:#336699; display: inline''>' || f.FileLicense || '</a>' as [License], count(ProjectFileFolder) as [Files] FROM indexDB.Servers s INNER JOIN indexDB.Projects p ON s.ServerID = p.ServerID INNER JOIN indexDB.FileInfo f ON p.ProjectID = f.ProjectID WHERE f.FileLicense <> '' GROUP BY [Project], [License] ORDER BY [Project], [License]; DETACH indexDB TableOnly None Basic Stats A basic usage overview of the system select 'Number of searches issued' as 'Statistics', sum(searches) as 'Value' from searchlog union select 'Number of file downloads', sum(downloads) from reuselog union select 'Number of distinct file downloads', count(distinct filehash) from reuselog union select 'Number of unique users', count(distinct cookie) from users where cookie is not null union select 'Number of unique users in the past week', count(distinct cookie) from users where lastvisit > (date('now', '-7 day')) order by 2 desc TableOnly None Repository Breakdown A breakdown of source code by repository ATTACH '%INDEX_DATABASE%' AS indexDB; Select Object as Repository, Value as LOCs from indexDB.LOCByServer; DETACH indexDB ChartOnly Pie Language Breakdown A breakdown of source code by language ATTACH '%INDEX_DATABASE%' AS indexDB; Select Object as Language, Value as LOCs from indexDB.LOCByLanguage where Value > 0; DETACH indexDB ChartOnly Pie Language Filters Most frequently used programming language filters. SELECT Language as [Language Filter], COUNT(ID) AS [Searches] FROM SearchLog where Language != '*' GROUP BY Language union SELECT 'None', COUNT(ID) AS [Searches] FROM SearchLog where Language == '*' GROUP BY Language ORDER BY 2 DESC; ChartOnly Pie License Breakdown A breakdown of source code by license ATTACH '%INDEX_DATABASE%' AS indexDB; SELECT FileLicense AS License, SUM(FileCode_LOCS + FileMixed_LOCS) AS LOCs FROM indexDB.FileInfo f where f.FileLicense != '' GROUP BY f.FileLicense union select 'No License Detected', COALESCE( SUM(FileCode_LOCS + FileMixed_LOCS), 0) FROM indexDB.FileInfo f where f.FileLicense == '' ORDER BY 2 DESC; DETACH indexDB ChartOnly Pie License Filters Most frequently used licensing filters. SELECT License as [License Filter], COUNT(ID) AS [Searches] FROM SearchLog where License != '*' GROUP BY License union SELECT 'None', COUNT(ID) AS [Searches] FROM SearchLog where License == '*' GROUP BY License ORDER BY 2 DESC ChartOnly Pie Project Status The current breakdown of projects by Status Codes, grouped by their repository. ATTACH '%INDEX_DATABASE%' AS indexDB; select s.serverName as Repository, count(ProjectID) as Projects, lc.description as Status from indexDB.Projects p inner join indexDB.Servers s on p.ServerID = s.ServerID inner join indexDB.LookupCodes lc on p.Status = lc.Code group by s.serverName, lc.description; DETACH indexDB TableOnly None Repository Status Summary of the current searchable index broken down by repository. ATTACH '%INDEX_DATABASE%' AS indexDB; SELECT s.ServerName as Repository, COUNT(DISTINCT p.ProjectID) AS Projects, COUNT(DISTINCT f.FileID) AS Files, SUM(f.FileCode_LOCS + f.FileMixed_LOCS) AS LOCs, SUM((f.FileCode_LOCS + f.FileMixed_LOCS) * %COST_PER_LOC%) AS 'Value ($)' FROM indexDB.Servers s INNER JOIN indexDB.Projects p ON s.ServerID = p.ServerID INNER JOIN indexDB.FileInfo f ON p.ProjectID = f.ProjectID WHERE (p.Status = 100) GROUP BY s.ServerName ORDER BY SUM(f.FileCode_LOCS + f.FileMixed_LOCS) DESC; DETACH indexDB TableOnly None Reuse Value By Project The projects that have saved the most money for your team. ATTACH '%INDEX_DATABASE%' AS indexDB; SELECT s.ServerName as Repository, p.ProjectName as [Project Name], SUM((f.FileCode_LOCS + f.FileMixed_LOCS) * r.Downloads) AS [Total LOCs], SUM((f.FileCode_LOCS + f.FileMixed_LOCS) * r.Downloads * %COST_PER_LOC%) AS [Savings ($)] FROM ReuseLog r INNER JOIN indexDB.FileInfo f ON r.FileHash = f.FileHash INNER JOIN indexDB.Projects p ON r.ProjectID = p.ProjectID INNER JOIN indexDB.Servers s ON p.ServerID = s.ServerID GROUP BY p.projectname, s.ServerName ORDER BY 4 desc Limit {Top}; DETACH indexDB TableOnly None Team Usage A breakdown of who is searching your index and how often. select CASE WHEN u.Username is not null THEN u.Username WHEN u.Email is not null THEN u.Email END as Username, strftime("%m/%d/%Y", u.Registered) AS [Activated], strftime("%m/%d/%Y", u.LastVisit) AS [Last Login], count(a.ID) AS [Searches] from users u left join UserActivityLog a on (a.UserID = u.ID and a.Type = 1000) where u.Username is not null or u.Email is not null group by u.Username, u.Email, u.Registered, u.LastVisit union select 'Anonymous(' || cast(count(distinct u.ID) as nvarchar(10)) || ')', 'N/A', 'N/A', count(a.ID) from users u left join UserActivityLog a on (a.UserID = u.ID and a.Type = 1000) where u.Username is null and u.Email is null order by 4 desc, 1 asc TableOnly None Top Null Searches The top search terms where the number of results is zero. SELECT Terms, SUM(Searches) AS Count FROM SearchLog WHERE Hits = 0 GROUP BY Terms ORDER BY SUM(Searches) DESC Limit {Top} TableOnly None Top Reused Files The most frequently downloaded (or copied) files. ATTACH '%INDEX_DATABASE%' AS indexDB; SELECT ProjectFileName as [File Name], ProjectName as [Project], SUM(downloads) as [Times Reused] FROM ReuseLog inner join indexDB.FileInfo f on ReuseLog.FileHash = f.FileHash inner join indexDB.Projects p on f.ProjectId = p.ProjectID group by ProjectFileName, ProjectName order by downloads desc Limit {Top}; DETACH indexDB TableOnly None Top Reused Projects The most frequently downloaded (or copied) projects. ATTACH '%INDEX_DATABASE%' AS indexDB; SELECT p.ProjectName as [Project], SUM(r.downloads) AS [Times Reused] FROM ReuseLog r, indexDB.Projects p WHERE r.ProjectId = p.ProjectID GROUP BY p.ProjectName ORDER BY 2 DESC Limit {Top}; DETACH indexDB TableOnly None Top Searches The most frequently searched terms. SELECT Terms, SUM(Searches) AS Count FROM SearchLog GROUP BY Terms ORDER BY SUM(Searches) DESC Limit 100 TableOnly None Top Solutions The most commonly reused source files, based upon the search term that was used. This shows which files are being most frequently used to helped developers solve problems. ATTACH '%INDEX_DATABASE%' AS indexDB; SELECT s.[Language], s.Terms, f.ProjectFileName AS FileName, p.ProjectName, r.Downloads, s.Searches, Round(CAST(r.Downloads AS FLOAT) / CAST(s.Searches AS FLOAT), 2) AS ReuseFactor FROM ReuseLog r INNER JOIN SearchLog s ON r.SearchLogID = s.ID INNER JOIN indexDB.FileInfo f ON r.FileHash = f.FileHash INNER JOIN indexDB.Projects p ON r.ProjectID = p.ProjectID WHERE (s.Searches > 2) AND (r.Downloads > 1) ORDER BY s.[Language], ReuseFactor DESC, r.Downloads DESC Limit {Top}; DETACH indexDB TableOnly None