/build/static/layout/Breadcrumb_cap_w.png

K1000 Reports - Reports to List K1000 SQL Queries and LDAP Filters

Typical disclaimer: I *really* need to stress that everything below is based on my own research and findings - the information below does not indicate official documentation or support, however some of this may be accurate.

Hopefully these K1000 SQL Reports are self-explanatory (they list the SQL Queries or LDAP Filters for their respective targets), but the intention is two-fold:

1) To provide a backup. Rather than having to save these as individual files (or K1000 Resources), they can all be exported in concise reports.

2) To provide a one-stop reference when looking to scavenge code from existing SQL queries and LDAP filters. This can be particularly useful when looking to build JOIN statements or determining how a Wizard-built filter has been constructed.

Another use for the K1000 Queries (Reports) report is for accessing report queries when the Report Wizard has been used to build a report, and subtopics have been used. In this situation, the Edit SQL button will not be available on the Edit Report screen (which precludes accessing the query using the K1000 UI). Using the K1000 Queries (Reports) report will allow you to access the query (and the corresponding JOIN statements that are created when using subtopics).

As usual, I hope these help! ^_^

John

 

K1000 Queries (Reports)

Lists SQL query code for all Reports in the K1000 database.

SELECT S.TITLE, S.CATEGORY, S.QUERY
FROM SMARTY_REPORT S
ORDER BY S.CATEGORY, S.TITLE

 

K1000 Queries (Smart Labels)

Lists SQL query code for all Smart Labels in the K1000 database.

SELECT L.NAME AS LABEL,
(CASE WHEN C.NAME IS NULL THEN '!No Label Group!'
ELSE C.NAME END) AS LABEL_GROUP,
L.NOTES,
F.QUERY
FROM LABEL L
LEFT JOIN LABEL_LABEL_JT LJT ON LJT.CHILD_LABEL_ID = L.ID
LEFT JOIN LABEL C ON C.ID = LJT.LABEL_ID
JOIN FILTER F ON F.LABEL_ID = L.ID
ORDER BY LABEL_GROUP, L.NAME

 

K1000 Queries (LDAP Labels)

Lists LDAP filters for all LDAP labels in the K1000 database.

SELECT L.NAME AS LABEL,
(CASE WHEN C.NAME IS NULL THEN '!No Label Group!'
ELSE C.NAME END) AS LABEL_GROUP,
L.NOTES,
LD.SEARCH_FILTER
FROM LABEL L
LEFT JOIN LABEL_LABEL_JT LJT ON LJT.CHILD_LABEL_ID = L.ID
LEFT JOIN LABEL C ON C.ID = LJT.LABEL_ID
JOIN LDAP_FILTER LD ON LD.LABEL_ID = L.ID
ORDER BY LABEL_GROUP, L.NAME

 

K1000 Queries (Email Alerts)

Lists SQL query code for all Email Alerts in the K1000 database.

SELECT N.TITLE, N.QUERY
FROM NOTIFICATION N
ORDER BY N.TITLE

Comments

  • This is great! I tried the Report and Smart Label queries, and everything is there.

    I was thinking about how I could check my 500+ smart labels based on this article:
    K1000: Lean Mean Machine Labels
    https://www.kace.com/support/resources/kb/solutiondetail?sol=SOL114277

    This will save me tons of time with these consolidated SQL query reports! - flip1001 9 years ago
  • Cool, thanks for the feedback! ^_^

    John - jverbosk 9 years ago
  • Perfect! Exactly what I was looking for thanks! - timothy.n.white 6 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ