/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1000- Service Desk Report by LDAP User Group

05/06/2015 2471 views
I'm looking for help in coming up with a report that shows closed Service Desk tickets sorted by LDAP user labels. In other words, if users in a particular organizational unit in Active Directory, that are in an LDAP label, submit tickets then there should be a way to have a report for all the users that have submitted tickets in a particular time frame for that label.


I have a report that sorts OVAL results by LDAP label and wonder if this can be tweaked in some way?


select LABEL.NAME,
count(*) as TESTED_COUNT,
sum(RESULT = 'SAFE' OR (CLASS='inventory' AND RESULT='VULNERABLE')) as SAFE_COUNT, sum(CLASS='vulnerability' AND RESULT = 'VULNERABLE') AS VULNERABLE_COUNT, sum(RESULT NOT IN ('SAFE','VULNERABLE')) AS OTHER_COUNT from OVAL_STATUS INNER JOIN MACHINE_LABEL_JT ON MACHINE_LABEL_JT.MACHINE_ID = OVAL_STATUS.MACHINE_ID INNER JOIN KBSYS.OVAL_DEFINITION  ON OVAL_STATUS.OVAL_DEFINITION_ID = OVAL_DEFINITION.ID INNER JOIN LABEL ON LABEL.ID = MACHINE_LABEL_JT.LABEL_ID INNER JOIN LDAP_FILTER ON LDAP_FILTER.LABEL_ID = LABEL.ID group by LABEL.NAME;

0 Comments   [ + ] Show comments

Comments


All Answers

1
This query will show tickets closed in the past 7 days and includes the labels for the submitter. It only includes tickets where the submitter's labels include "User Services"

SELECT T.TITLE, T.CREATED, T.TIME_CLOSED, T.SUBMITTER_ID, GROUP_CONCAT(LABEL.NAME) AS SUBMITTER_LABELS
FROM ORG1.HD_TICKET T
JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = T.SUBMITTER_ID
JOIN LABEL on LABEL.ID = USER_LABEL_JT.LABEL_ID
WHERE TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY T.ID
HAVING SUBMITTER_LABELS like "%User Services%"
Since users can have multiple labels it is difficult to group by one particular label, but it should be possible.
Answered 05/08/2015 by: chucksteel
Red Belt

 
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