locked
LINQ to display count of records not in top x RRS feed

  • Question

  • User-2012457684 posted

    I am writing a logfile reader for our clients to use on their websites.   

    To that end, I create a single list composed of the various files that they want to view. and then use LINQ to query the list and build the pages.   I will use LINQ to get the top x number of browsers  and then want to have all of the others included as others.   This is what I need help with.   I do not know how to get the count of the records that are not in the top x.    

     

    Tuesday, September 1, 2020 9:11 PM

Answers

  • User-1330468790 posted

    Hi mj1223,

     

    You could select all records and count before take a certain number from the result.

    It is a math problem as @mgebhard said. You could get count for all, "Total",  and you already know the number of records for displaying, "known browsers". The only thing you need to do is substraction.

     

    More details, you could refer to below codes (Console App for .NET Core):

    class Program
        {
            static void Main(string[] args)
            {
                // Simplified Example Data
                List<Logfile> ListData = new List<Logfile>
                {
                    new Logfile { Browser= "Browser1",FileName= "FileName1", UserAgent= "UserAgent1"},
                    new Logfile { Browser= "Browser1",FileName= "FileName2", UserAgent= "UserAgent2"},
                    new Logfile { Browser= "Browser1",FileName= "FileName3", UserAgent= "UserAgent3"},
                    new Logfile { Browser= "Browser1",FileName= "FileName4", UserAgent= "UserAgent4"},
                    new Logfile { Browser= "Browser2",FileName= "FileName5", UserAgent= "UserAgent5"},
                    new Logfile { Browser= "Browser3",FileName= "FileName6", UserAgent= "UserAgent6"},
                    new Logfile { Browser= "Browser4",FileName= "FileName7", UserAgent= "UserAgent7"},
                    new Logfile { Browser= "Browser5",FileName= "FileName8", UserAgent= "UserAgent8"},
                    new Logfile { Browser= "Browser5",FileName= "FileName9", UserAgent= "UserAgent9"},
                    new Logfile { Browser= "Browser5",FileName= "FileName10", UserAgent= "UserAgent10"},
                    new Logfile { Browser= "Browser6",FileName= "FileName11", UserAgent= "UserAgent11"},
                    new Logfile { Browser= "Browser7",FileName= "FileName12", UserAgent= "UserAgent12"},
                    new Logfile { Browser= "Browser7",FileName= "FileName13", UserAgent= "UserAgent13"},
                    new Logfile { Browser= "Browser8",FileName= "FileName14", UserAgent= "UserAgent14"},
                    new Logfile { Browser= "Browser9",FileName= "FileName15", UserAgent= "UserAgent15"},
                    new Logfile { Browser= "Browser10",FileName= "FileName16", UserAgent= "UserAgent16"},
                    new Logfile { Browser= "Browser11",FileName= "FileName17", UserAgent= "UserAgent17"},
                    new Logfile { Browser= "Browser12",FileName= "FileName18", UserAgent= "UserAgent18"},
                    new Logfile { Browser= "Browser13",FileName= "FileName19", UserAgent= "UserAgent19"},
                    new Logfile { Browser= "Browser13",FileName= "FileName20", UserAgent= "UserAgent20"}
                };
    
                // Take number
                int takeNum = 10;
    
                // Total records for browsers
                var TotalBrowsers = (from browser in ListData
                                group browser by browser.Browser into Agents
                                select new Browsers
                                {
                                    Browser = Agents.Key,
                                    Count = Agents.Count()
                                })
                       .OrderByDescending(x => x.Count).ToList();
    
                // Take a certain number of records
                var browsers = TotalBrowsers.Take(takeNum);
    
                // Others Count
                var othersCount = TotalBrowsers.Count() - takeNum;
    
               
                // Display known browsers
                foreach (var item in browsers)
                {
                    Console.WriteLine(item.ToString());
                }
    
                // Display the count for others
                Console.WriteLine(String.Format("The count for others is: {0}", othersCount));
    
                Console.ReadLine();
            }
        }
    
        public class Browsers
        {
            public string Browser { get; set; }
            public int Count { get; set; }
    
            public override string ToString()
            {
                return String.Format("Browser: {0}, Count: {1}  \n",Browser, Count); 
            }
        }
    
        public class Logfile
        {
            public string FileName { get; set; }
            public string UserAgent { get; set; }
            public string Browser { get; set; }
        }

    Result:

     

    Hope this can help you.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 2, 2020 5:43 AM

All replies

  • User475983607 posted

    It's very difficult to answer data questions without the actual data.   Basically the other records are those not in the TOP X.  Use a NOT IN or a JOIN to exclude the records NOT IN the TOP X.  See the standard TSQL docs.

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver15

    https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15

    Tuesday, September 1, 2020 9:43 PM
  • User-2012457684 posted

    This does not use a database.   If it did I would not need to use LINQ, and would know how to write in SQL.    In this case it is reading the log files using a streamreader and creating a list.  I then use LINQ to read the list.   here is my class

    public class Logfile
    {
        public string FileName { get; set; }
        public string date { get; set; }
        public string time { get; set; }
        public string ServerIP { get; set; }
        public string Method { get; set; }
        public string URI { get; set; }
        public string Query { get; set; }
        public string Port { get; set; }
        public string Username { get; set; }
        public string IP { get; set; }
        public string UserAgent { get; set; }
        public string Referrer { get; set; }
        public string Status { get; set; }
        public string SubStatus { get; set; }
        public string Win32Status { get; set; }
        public string TimeOnSite { get; set; }
        public string Browser { get; set; }
    
    
    
        public List<String> ListLogs(DateTime StartDate, DateTime EndDate)
        {
            List<String> logList = new List<String>();
            foreach (DateTime day in EachDay(StartDate, EndDate))
            {
                string d = day.Day.ToString("00");
                string m = day.Month.ToString("00");
                string yr = day.Year.ToString().Substring(2);
                string logfile = "u_ex" + yr + m + d + ".log";
                logList.Add(logfile);
            }
            return logList;
        }
    
        public List<Logfile> GetLogs(List<String> logList, string path)
        {
            List<Logfile> Files = null;
            var uaParser = Parser.GetDefault();
            Files = new List<Logfile>();
            foreach (string log in logList)
            {
                string newFile = path + @"\" + log;
                if (File.Exists(newFile))
                {
                    //Logfile lf = new Logfile();
                    using (StreamReader sr = new StreamReader(newFile))
                    {
                        //the top 4 lines do not contain any log information so skip reading them
                        for (var i = 0; i < 4; i++)
                        {
                            sr.ReadLine();
                        }
                        while(sr.Peek() >= 0)
                        {
                            Logfile lf = new Logfile();
                            string line = sr.ReadLine();
                            var info = Regex.Split(line, @"\s+");
                            if (!info[0].StartsWith("#"))
                            {
                                lf.date = info[0];
                                lf.time = info[1];
                                lf.ServerIP = info[2];
                                lf.Method = info[3];
                                lf.URI = info[4];
                                lf.Query = info[5];
                                lf.Port = info[6];
                                lf.Username = info[7];
                                lf.IP = info[8];
                                lf.UserAgent = info[9];
                                lf.Referrer = info[10];
                                lf.Status = info[11];
                                lf.SubStatus = info[12];
                                lf.Win32Status = info[13];
                                lf.TimeOnSite = info[14];
                                ClientInfo c = uaParser.Parse(info[9]);
                                lf.Browser = c.UA.Family;
                                Files.Add(lf);
                            }
                        }
                    }
                }
            }
            return Files;
    
        }
    
        private IEnumerable<DateTime> EachDay(DateTime from, DateTime thru)
        {
            for (var day = from.Date; day.Date <= thru.Date; day = day.AddDays(1))
            {
                yield return day;
            }
        }
    
    }

    public class Browsers
    {
    public string Browser { get; set; }
    public int Count { get; set; }
    }

    then in my controller i simply do this where in this case x = 10 

    string processID = Request.ServerVariables["INSTANCE_ID"].ToString();

    public ActionResult Browsers(DateTime StartDate, DateTime EndDate) { Logfile log = new Logfile(); string path = @"c:\inetpub\logs\logfiles\W3SVC" + processID; if (!Directory.Exists(path)) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } List<String> logs = log.ListLogs(StartDate, EndDate); List<Logfile> ListData = log.GetLogs(logs, path); var browsers = (from browser in ListData group browser by browser.Browser into Agents select new Browsers { Browser = Agents.Key, Count = Agents.Count() }) .OrderByDescending(x => x.Count) .Take(10).ToList(); return View(browsers); }

    What I am stuck on is getting the other browsers so when i build the report i can display all of the other browsers as a single nuumber.

    Tuesday, September 1, 2020 10:41 PM
  • User475983607 posted

    What I am stuck on is getting the other browsers so when i build the report i can display all of the other browsers as a single nuumber.

    Correct me if I'm wrong but the math is pretty simple.

    Total - known browsers = Other browsers

    As far as I can tell, you already have these values otherwise you could not get the count of known browsers.  

    Tuesday, September 1, 2020 10:53 PM
  • User-1330468790 posted

    Hi mj1223,

     

    You could select all records and count before take a certain number from the result.

    It is a math problem as @mgebhard said. You could get count for all, "Total",  and you already know the number of records for displaying, "known browsers". The only thing you need to do is substraction.

     

    More details, you could refer to below codes (Console App for .NET Core):

    class Program
        {
            static void Main(string[] args)
            {
                // Simplified Example Data
                List<Logfile> ListData = new List<Logfile>
                {
                    new Logfile { Browser= "Browser1",FileName= "FileName1", UserAgent= "UserAgent1"},
                    new Logfile { Browser= "Browser1",FileName= "FileName2", UserAgent= "UserAgent2"},
                    new Logfile { Browser= "Browser1",FileName= "FileName3", UserAgent= "UserAgent3"},
                    new Logfile { Browser= "Browser1",FileName= "FileName4", UserAgent= "UserAgent4"},
                    new Logfile { Browser= "Browser2",FileName= "FileName5", UserAgent= "UserAgent5"},
                    new Logfile { Browser= "Browser3",FileName= "FileName6", UserAgent= "UserAgent6"},
                    new Logfile { Browser= "Browser4",FileName= "FileName7", UserAgent= "UserAgent7"},
                    new Logfile { Browser= "Browser5",FileName= "FileName8", UserAgent= "UserAgent8"},
                    new Logfile { Browser= "Browser5",FileName= "FileName9", UserAgent= "UserAgent9"},
                    new Logfile { Browser= "Browser5",FileName= "FileName10", UserAgent= "UserAgent10"},
                    new Logfile { Browser= "Browser6",FileName= "FileName11", UserAgent= "UserAgent11"},
                    new Logfile { Browser= "Browser7",FileName= "FileName12", UserAgent= "UserAgent12"},
                    new Logfile { Browser= "Browser7",FileName= "FileName13", UserAgent= "UserAgent13"},
                    new Logfile { Browser= "Browser8",FileName= "FileName14", UserAgent= "UserAgent14"},
                    new Logfile { Browser= "Browser9",FileName= "FileName15", UserAgent= "UserAgent15"},
                    new Logfile { Browser= "Browser10",FileName= "FileName16", UserAgent= "UserAgent16"},
                    new Logfile { Browser= "Browser11",FileName= "FileName17", UserAgent= "UserAgent17"},
                    new Logfile { Browser= "Browser12",FileName= "FileName18", UserAgent= "UserAgent18"},
                    new Logfile { Browser= "Browser13",FileName= "FileName19", UserAgent= "UserAgent19"},
                    new Logfile { Browser= "Browser13",FileName= "FileName20", UserAgent= "UserAgent20"}
                };
    
                // Take number
                int takeNum = 10;
    
                // Total records for browsers
                var TotalBrowsers = (from browser in ListData
                                group browser by browser.Browser into Agents
                                select new Browsers
                                {
                                    Browser = Agents.Key,
                                    Count = Agents.Count()
                                })
                       .OrderByDescending(x => x.Count).ToList();
    
                // Take a certain number of records
                var browsers = TotalBrowsers.Take(takeNum);
    
                // Others Count
                var othersCount = TotalBrowsers.Count() - takeNum;
    
               
                // Display known browsers
                foreach (var item in browsers)
                {
                    Console.WriteLine(item.ToString());
                }
    
                // Display the count for others
                Console.WriteLine(String.Format("The count for others is: {0}", othersCount));
    
                Console.ReadLine();
            }
        }
    
        public class Browsers
        {
            public string Browser { get; set; }
            public int Count { get; set; }
    
            public override string ToString()
            {
                return String.Format("Browser: {0}, Count: {1}  \n",Browser, Count); 
            }
        }
    
        public class Logfile
        {
            public string FileName { get; set; }
            public string UserAgent { get; set; }
            public string Browser { get; set; }
        }

    Result:

     

    Hope this can help you.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 2, 2020 5:43 AM
  • User-2012457684 posted

    Thank you for showing me that.   

    Thursday, September 3, 2020 6:51 PM