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
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

0

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

Answered 02/21/2013 by: jdornan
Red Belt

Please log in to comment
0

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
Answered 02/13/2013 by: dugullett
Red Belt

  • 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.
Please log in to comment
0

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.

Answered 02/14/2013 by: timbot18
Senior White Belt

Please log in to comment
0

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.

Answered 02/14/2013 by: timbot18
Senior White Belt

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