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?
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)
Please log in to answer
Posted by:
chucksteel
9 years ago
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.
