More Custom SQL K1000 Reporting
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. :)
0 Comments
[ + ] Show comments
Answers (2)
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
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
Please log in to answer
Posted by:
dugullett
11 years ago
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
Comments:
-
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? - joe.pyrczak 11 years ago
-
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? - dugullett 11 years ago
-
You can take off the M.NAME under GROUP BY section, and just leave S.DISPLAY_NAME.
GROUP BY S.DISPLAY_NAME - dugullett 11 years ago
-
that did it! Thanks. - joe.pyrczak 11 years ago
-
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. - joe.pyrczak 11 years ago
-
It looks like you found your answer. You can take out the JOIN label section since it's now unnecessary. - dugullett 11 years ago
