Hi Everyone,

I have found a lot of help here and now I am trying to do something different:

I am trying to write a SQL query with Software and labels for different locations.

I am trying to list systems in each label location with what software is installed on each, for eg.

Software Name with list of tags it is installed on below, grouped by Label name

Any ideas? I am very new to SQL reporting and have just started with help from you all and a Blog post from John V. :)

Answer Summary:
Ok, I changed the query like this for my final reports: SELECT S.DISPLAY_NAME, S.DISPLAY_VERSION, GROUP_CONCAT(DISTINCT M.NAME SEPARATOR '\n') AS MACHINE_NAME, GROUP_CONCAT(DISTINCT M.USER_LOGGED SEPARATOR '\n') AS User_Logged FROM SOFTWARE S LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON (MSJT.SOFTWARE_ID = S.ID) LEFT JOIN MACHINE M ON (M.ID = MSJT.MACHINE_ID) LEFT JOIN MACHINE_LABEL_JT MLJT ON M.ID=MLJT.MACHINE_ID LEFT JOIN LABEL L ON MLJT.LABEL_ID=L.ID WHERE (NOT S.IS_PATCH) AND /* M.IP allows searching by IP. .% at the end can be used for searching the subnets */ /* use OR "M.IP LIKE '10.252.44.%'" without quotes and edit for additional subnets */ M.IP LIKE '10.1.1.%' OR M.IP LIKE '10.1.2.%' GROUP BY S.DISPLAY_NAME ORDER BY S.DISPLAY_NAME I added the comment there "/* */" for reference, so the location smart labels I made can be put into the query dugullett helped me with above. Why you ask? I found out from here: http://www.itninja.com/question/smart-labels-slow-to-populate that the labels are slow to populate. I have a new location with a couple subnets that I need to report against. With several mobile users not checking in but once a week, they would not show up for the report until after the report is due :) Hope this helps someone else too
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

Try this.

SELECT DISPLAY_NAME, DISPLAY_VERSION,

GROUP_CONCAT(DISTINCT M.NAME SEPARATOR '\n') AS MACHINE_NAME,

GROUP_CONCAT(DISTINCT M.USER_LOGGED SEPARATOR '\n') AS User_Logged

FROM SOFTWARE S

LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON (MSJT.SOFTWARE_ID = S.ID)

LEFT JOIN MACHINE M ON (M.ID = MSJT.MACHINE_ID)

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

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

WHERE (NOT S.IS_PATCH) AND

L.NAME = '<LABEL NAME>'

GROUP BY S.DISPLAY_NAME, M.NAME

ORDER BY S.DISPLAY_NAME

 
Answered 04/04/2013 by: dugullett
Red Belt

  • That is almost exactly what I am trying to do, I keep getting lists and lists with way too much data until I did what you suggested. One followup question... I am trying to get it to display broken up by M.NAME I think it is?
    • So you want software name, and all the machines that have it in one cell? I actually tried doing this in the beginning, and it seemed messy. Is there any way you can post a sample .csv to show what you want to make sure I know?
      • You can take off the M.NAME under GROUP BY section, and just leave S.DISPLAY_NAME.

        GROUP BY S.DISPLAY_NAME
  • that did it! Thanks.
  • Now I have a NEW wrinkle. I have a new smart label that has not populated as several mobile users have not been in the office yet, so I need to query against the machine ip for eg. 10.1.1.x and 10.1.2.x subnet and all the computers on them. Gotta love impromptu audits.
    • It looks like you found your answer. You can take out the JOIN label section since it's now unnecessary.
Please log in to comment

Answers

1

Thank you, I can see this being very helpful!

Answered 04/04/2013 by: GeekSoldier
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share