Why does my K1000 SQL report not create links to machines?
I have created a SQL report to bring back some results. the report appears to be working. however, unlike when you create a report from the wizard, or use one of the built in reports, when a machine name is in the list, it doesnt link to the machine's page on the k1000 like its suppose to. is there something else i have to put in the code? (by the way, i'm new to sql, so the code is probably ugly) any suggestions would be great!
SELECT
S.DISPLAY_NAME, M.NAME AS Machine
FROM MACHINE M
INNER JOIN
ORG1.KBOT_LOG KL ON (KL.MACHINE_ID = M.ID)
INNER JOIN
MACHINE_SOFTWARE_JT MSJT ON (MSJT.MACHINE_ID = M.ID)
INNER JOIN
SOFTWARE S ON (S.ID = MSJT.SOFTWARE_ID)
where
((KL.KBOT_ID RLIKE '144|143')
AND (S.DISPLAY_NAME RLIKE 'JAVA')
AND (S.DISPLAY_NAME NOT RLIKE ('7 UPDATE 13|AUTO UPDATER|FX')))
ORDER BY DISPLAY_NAME
Answers (4)
Thats as close as you are going to get becasue of the PHP use of htmlspecialcharachters() in the code.
http://php.net/manual/en/function.htmlspecialchars.php
Even escaping them wont work becasue anything you can do occurs before the PHP strips it all away
I just saw that on 5.4. I created a test report. The way they're doing that has to be in the XML which you used to be able to edit with "classic reports" in older versions. It's not in the SQL query. You could expirement around some with the query below. It wont be exactly what you want. I can't get the syntax exactly right, but it's a start.
SELECT DISTINCT S.DISPLAY_NAME, CASE WHEN M.NAME LIKE '%%' THEN 'https://yourkbox.domain.org/adminui/machine.php?ID=' + M.ID END AS "MACHINE" FROM MACHINE M INNER JOIN ORG1.KBOT_LOG KL ON (KL.MACHINE_ID = M.ID) INNER JOIN MACHINE_SOFTWARE_JT MSJT ON (MSJT.MACHINE_ID = M.ID) INNER JOIN SOFTWARE S ON (S.ID = MSJT.SOFTWARE_ID) where (S.DISPLAY_NAME RLIKE 'JAVA') ORDER BY DISPLAY_NAME LIMIT 5
Comments:
-
Now that I'm testing this further it still will not create the link. I changed the THEN to match the exact URL without the +M.ID. It created it correctly, but it is not clickable. I still think it's in the XML. - dugullett 11 years ago
i tried the concat to add all of it together with html link code(
<a href...
), but it just displayed all of the text for the link instead of translating it. does SQL have a link command built in? i'v been searching the web and so far have only found ways to link to a sql query, not just display as a link.
this is the best i have been able to come up with so far:
SELECT DISTINCT
M.NAME AS SYSTEM_NAME, S.DISPLAY_NAME,
CONCAT ('https://yourk1000.com/adminui/machine.php?ID=',M.ID) AS MACHINE_LINK
using firefox, just double (or triple) click on this link area in the results page to select the whole thing, then right click and open it (or in a new tab). not exactly what i was looking for but beats copying, going to inventory, pasting, then clicking on the computer. chrome may do the same thing, or IE, but i use firefox mainly.