Basically what I am wanting to do is create a report that will be emailed to certain users everyday that says "There are XX number of machines in XX smart label"

I'm needing to do this for about six different smart labels and would very much like to combine them into a single email.

I'm sure there is some SQL wizardry that can accomplish this but that is over my head.

 

Any help is very much appreciated.

Thanks.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

Are all of the labels in question in a group? We have labels assigned for departments and they are all in a "Departments" group. The following report shows the number of computers in each department:

 SELECT DISTINCT(LABEL.NAME) AS DEPARTMENT,
COUNT(MACHINE.NAME) AS COUNT
FROM MACHINE  
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)  
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') 
LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 258)
WHERE LABEL_LABEL_JT.LABEL_ID = 258
GROUP BY DEPARTMENT
ORDER BY DEPARTMENT

To make this work for your label group you'll need to find the ID of the label group and replace the 258 with your ID.

If the labels aren't in a group then it is still possible but the SQL will be a little different. You could do something like this:

 SELECT DISTINCT(LABEL.NAME) AS DEPARTMENT,
COUNT(MACHINE.NAME) AS COUNT
FROM MACHINE  
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)  
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') 
WHERE LABEL.NAME in ("English", "Academic Advising", "Advancement Services", "Athletics")
GROUP BY DEPARTMENT
ORDER BY DEPARTMENT

Enter the label names you want included in the report in the WHERE LABEL.NAME in ("label1", "label2")

 

Answered 08/06/2013 by: chucksteel
Red Belt

  • Your second option works perfectly! Thank you very much!
  • Chucksteel,
    I am trying to do something very similar to AFCUjstrick, so I created the label group like your top suggestion. In my report I want to see the count of machines in 3 machine labels and I want to see the name of the 3 labels. In my report it is spitting out one of the 3 label names and summing the count for all 3 in the one label. Can you help me figure out what I'm doing wrong? I suspect in the first line where yours says "AS DEPARTMENT " and mine says "AS LABEL_NAME" I'm wrong. Do I specify the name of the label group there?

    Here's my SQL:

    SELECT DISTINCT(LABEL.NAME) AS LABEL_NAME,
    COUNT(MACHINE.NAME) AS COUNT
    FROM MACHINE
    LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)
    LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')
    LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 1364)
    WHERE LABEL_LABEL_JT.LABEL_ID = 1364
    • You're missing the GROUP BY statement. In order to perform a count of a column in MySQL you normally have to group by another column. In this case you want to GROUP BY LABEL_NAME
      • SWEET! That worked. Thanks very much! :-)
Please log in to comment

Answers

0

Try this. Edit to match your label names.

SELECT COUNT(M.NAME) 'Machine Count', L.NAME as 'Label Name'

FROM MACHINE M

LEFT JOIN MACHINE_LABEL_JT MLJT ON M.ID=MLJT.MACHINE_ID

LEFT JOIN LABEL L ON L.ID = MLJT.LABEL_ID

WHERE L.NAME LIKE '<LABEL_1>%'

OR L.NAME LIKE '<LABEL_2>%'

OR L.NAME LIKE '<LABEL_3>%'

GROUP BY L.NAME

ORDER BY L.NAME
 
Answered 08/06/2013 by: dugullett
Red Belt

  • Well I didn't see Chuck's answer. I guess I should refresh before I answer next time. LOL.
  • The more info I can get the better off I'll be. Thank you as well.
  • One more add on question. Is there a way to send the report so it is in-line in the email rather than an attachment?
    • Instead of doing it as a report try using an alert.

      http://www.kace.com/support/resources/kb/article/How-to-Make-an-Alert-for-Recently-Installed-Software?action=artikel&cat=9&id=654&artlang=en
Please log in to comment
Answer this question or Comment on this question for clarity