admin/get_bu_data has incorrect count (`cnt`) values

[n8felton]

This is going to require some digging, but right now, I'm brain dumping some of my findings to attempt to look at later, or see if someone else wants to investigate.

Some of my older Business Units have a value for "cnt" (snip of admin/get_bu_data)

{
"users": [
"bob"
],
"managers": [
"richard",
"cindy"
],
"machine_groups": [
101,
132,
103,
122,
123,
108,
107,
133,
106,
150,
126,
134,
100,
124,
102,
151
],
"name": "CLA",
"unitid": "3",
"address": "",
"link": "",
"groupid": "1",
"cnt": "688",
"key": "5E48FBAA-B863-480B-A4E9-4830B8E6E242"
}

Regardless of this value existing, it's not correct.

While some of my newer Business Units don't have anything set

{
"users": [],
"managers": [
"harry"
],
"machine_groups": [
152,
153,
154,
155,
156
],
"name": "GIS",
"unitid": "19",
"address": "",
"link": ""
}

Quick digging shows that cnt is a property in the business_unit table of the database, but I haven't yet found what put it there, when/what version of MR put it there, or if there is anything attempting to update the value.

It appears that admin/get_mg_data is generating the cnt value on the fly, instead of gathering it from the database. Perhaps admin/get_bu_data should mimic this.

[chelming]

if it's currently broken, can we take this time to also rename it to count?

[bochoven]

That value is not used anymore, I can't remember which version of MR did use it. Is this a feature request to add total amount of machines in a BU to that JSON?

[chelming]

Sounds like it. We were looking to get full counts of the machines in a business unit and noticed some inconsistencies.

[chelming]

I think it'd also be nice to print the total number next to the business unit name.

[n8felton]

I'm going to leave this here because I spent way too long trying to get it to work and maybe future me will need it again. This is 💯 a terrible SQL query, but it got me the data I needed to make pretty pie charts for now.

SELECT name.name AS 'Business Unit',
       sum(qty) AS total
FROM
  (SELECT bu.name,
          bu.unitid,
          rd.machine_group,
          count(*) AS qty
   FROM munkireport.reportdata AS rd
   JOIN
     (SELECT unitid,
             value,
             (CASE
                  WHEN property = 'name' THEN value
              END) AS name
      FROM munkireport.business_unit
      WHERE property = 'machine_group') AS bu ON rd.machine_group = bu.value
   GROUP BY bu.unitid,
            rd.machine_group) AS totals
JOIN
  (SELECT DISTINCT unitid,
                   max(CASE
                           WHEN property = 'name' THEN value
                       END) AS name
   FROM munkireport.business_unit
   GROUP BY unitid) AS name ON name.unitid = totals.unitid
GROUP BY name.name;

[ofirgalcon]

I'm going to leave this here because I spent way too long trying to get it to work and maybe future me will need it again. This is 💯 a terrible SQL query, but it got me the data I needed to make pretty pie charts for now.

SELECT name.name AS 'Business Unit',
       sum(qty) AS total
FROM
  (SELECT bu.name,
          bu.unitid,
          rd.machine_group,
          count(*) AS qty
   FROM munkireport.reportdata AS rd
   JOIN
     (SELECT unitid,
             value,
             (CASE
                  WHEN property = 'name' THEN value
              END) AS name
      FROM munkireport.business_unit
      WHERE property = 'machine_group') AS bu ON rd.machine_group = bu.value
   GROUP BY bu.unitid,
            rd.machine_group) AS totals
JOIN
  (SELECT DISTINCT unitid,
                   max(CASE
                           WHEN property = 'name' THEN value
                       END) AS name
   FROM munkireport.business_unit
   GROUP BY unitid) AS name ON name.unitid = totals.unitid
GROUP BY name.name;

Very useful, but for some reason it doesn't show all my Business Units, ones that have devices in them

[ofirgalcon]

OK ignore me, I was being stupid