There may be occasion where you obtain a query, written in SQL that you would like to run as a report within your K1000 appliance. This document demonstrates how to take that query and get a report out of it.
Some places where you might obtain a query:
- Technical Support gives you a query written in SQL based on your request for some type of data
- SQL from somewhere else in your K1000 appliance was obtained by looking at the details behind some other objects in your K1000 appliance. e.g. filters, alerts, helpdesk ticket rules, other reports, etc
- A colleague has written a query and asked you to make it into a report
Step 1: Obtain the SQL
For this discussion I am going to use the SQL that comes from a report called "For Each Patch, what machines have it installed". The SQL is
select P.TITLE AS DISPLAY_NAME, MACHINE.NAME as ComputerName,
SYSTEM_DESCRIPTION, IP, MAC,
REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED,
from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P
MACHINE.ID = S.MACHINE_ID and
S.PATCHUID = P.UID and
order by P.TITLE
Step 2: Create a new "dummy" report
To create a new dummy report you would:
- click on the "reporting" tab.
- Then from the drop-down on the left-hand side of that page you would choose "Add New SQL Report"
- As in this screenshot you would fill in the following:
- a name for the report
- a description
- paste in the query *Check the box for "Auto-generate layout"
- Click "Save"
Step 3: Review your results so far
You should find your report in the list and review your results. In this case they would look something like this screenshot:
Step 4: Edit the report query
For a report that starts with a SQL statement you cannot enter into the GUI-Wizard-based-mode. This only works for reports that are created in the GUI-based-wizard.
However, you can still change some things. If you wanted to change something you can go back into a report editor by simply finding the report in the list of reports and click on the title.
If you ever change an existing report in any way you will want to re-check the box for "re-generate the layout". This will automatically adust the report for additional columns and formatting related to that.
Something that you might want to change is that certain columns only have a couple of different values and most results will show up with one value or another. In these cases it can be great to move the row to show-up as a section in the report instead of as a column.
For our patching SQL example, since we are listing each patch and given a report of the machines that have it there are going to be up to hundreds of machines in a given patch name. So we could have a section that lumps all those related records together like this screenshot:
Step 4: Edit / Re-generate the layout
If you were familiar with the JasperSoft engine then you might want to try your hand at editing the XML layout directly to produce different results. We do not recommend that. Keep in mind that if you ever use the "auto-generate" layout feature again that your custom changes in the XML layout would be gone.
However, this step is so important that I am going to mention it again... If you ever change an existing report in any way you will want to re-check the box for "re-generate the layout". This will automatically adust the report for additional columns and formatting related to that.
Step 5: View your results
Find the report in the list view and click on one of the formats to view the results (ie HTML, CSV, Excel, PDF, etc)