/build/static/layout/Breadcrumb_cap_w.png

K1000- Service Desk Report by LDAP User Group

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

Answers (1)

Posted by: chucksteel 8 years ago
Red Belt
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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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