I have a SQL script in place but need to tweak it a little bit more. I'd like some direction on how to insert a line break to separate criteria in a software report. 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Is something like this what you are needing? 

http://www.itninja.com/blog/view/workaround-for-line-break-character-bug-in-reports-generated-on-inventory-custom-fields

Answered 09/20/2012 by: dugullett
Red Belt

  • I saw this and it will not work for how I want the look to be.
  • Sorry I meant to create a row with a title.
    • Maybe it would help some more if we knew what kind of output you want. Can you post your current query, and what you want the outcome to be?
  • Okay so I think I figured it out last night (6 hours of research) Here's my query
    So what it does is create a row and the top of the report.

    select '%' as system_name,null as ip,null as display_version,null as user union

    SELECT M.NAME AS SYSTEM_NAME, IP, S.DISPLAY_VERSION, USER_LOGGED USER

    FROM MACHINE M

    LEFT JOIN MACHINE_SOFTWARE_JT ON M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID

    LEFT JOIN SOFTWARE S ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID


    WHERE (S.DISPLAY_NAME like '%')


    OR (S.DISPLAY_NAME like '%')
  • Do you know how to remove kb articles and or sevice packs from be displayed in the report output..
  • You could use REGEX (rlike) instead of like in your WHERE statements to get a little more granular on what is matched (included). Or, you could filter things out using REGEX statements (i.e. WHERE S.DISPLAY_NAME not rlike 'kb|articles|service|packs') - of course, you'll need to provide your own filtering criteria.

    John
  • WHERE (NOT S.IS_PATCH) should also work.
  • Okay where would the WHERE (NOT S.IS_PATCH) go in the query?
    • SELECT DISTINCT M.NAME AS SYSTEM_NAME, IP, S.DISPLAY_VERSION, USER_LOGGED USER

      FROM MACHINE M

      LEFT JOIN MACHINE_SOFTWARE_JT ON M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID

      LEFT JOIN SOFTWARE S ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID


      WHERE (NOT S.IS_PATCH) and (S.DISPLAY_NAME like '%'


      OR S.DISPLAY_NAME like '%')
  • Okay that worked.. Thanks again "Dugullett"
Please log in to comment
0

Unfortunately, the (new) Reports module doesn't process line break characters correctly (either <br/> or \r\n).  The Knowledge Base module also has the same issue (at times, at least on my K1100 - v5.3.53053).

If you have the Classic Reports module, the \r\n line break *is* processed correctly.  Short of that (i.e. if you don't have Classic Reports or prefer to stick with the new Reports), you'll need to parse substrings out like I did in these blogs (which is a solution to my workaround that dgullett referenced): 

K1000 Reports - Querying and Reporting on Network Printer Error and Status Codes

http://www.itninja.com/blog/view/k1000-reports-querying-and-reporting-on-network-printer-error-and-status-codes


K1000 Reports - Print Server Printer List with Name, IP, Location & Comments Columns

http://www.itninja.com/blog/view/k1000-reports-print-server-printer-list-with-name-ip-location-comments-columns

Hope that helps!

John

Answered 09/20/2012 by: jverbosk
Red Belt

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

Share