How to create line breaks to separate criteria in Kace reports
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.
Answers (2)
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
Comments:
-
I saw this and it will not work for how I want the look to be. - shawnvic71 12 years ago
-
Sorry I meant to create a row with a title. - shawnvic71 12 years ago
-
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? - dugullett 12 years ago
-
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 '%') - shawnvic71 12 years ago -
Do you know how to remove kb articles and or sevice packs from be displayed in the report output.. - shawnvic71 12 years ago
-
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 - jverbosk 12 years ago -
WHERE (NOT S.IS_PATCH) should also work. - dugullett 12 years ago
-
Okay where would the WHERE (NOT S.IS_PATCH) go in the query? - shawnvic71 12 years ago
-
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 '%') - dugullett 12 years ago
-
Okay that worked.. Thanks again "Dugullett" - shawnvic71 12 years ago
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
