Please use this topic to ask the community for KBOX reports that you would like to see.
14 Comments   [ + ] Show Comments

Comments

  • Good afternoon everyone

    Our director is looking for a Help Desk report that shows tickets closed in the past 7 days, that were open for longer than 3 days

    Are there any recommendations for this report?

    Thank you
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • Hello,

    I'm new in K1000. I need you help / advise on how to grab the following reports for server patching.

    - List of services before patching
    - List of services after patching
    - List of services that did not start after patching with comparison from before and after patching.

    Thanks in advance for your help.
  • Would like to see a report showing utilization of tech's time. It could be used to show how busy or not so busy our staff is on tickets.
  • Hello, I'm looking for a patch age report. I want to a report of machines that are missing security patches that are older than 60 days. I also need the report to then show the patch details, such as patch title, vendor,
  • I am looking for a report that will allow me to present the average time spent within each available status for each ticket over a period of time. I have written SQL for a few reports but even using the ticket history report as a base I can't get this one working. Thanks for any help you can lend.
  • I have been asked to take the current Service Desk Last 31 Days Tickets Closed report and add the following fields: Request date, Close date, system, issue, comments and result
    Current sql is found in the standard report in KACE
  • Hi folks, I'm trying to create a report that will show me all open tickets for a Support Rep AND all tickets closed by that Rep over the previous week/month/year. I can use the Wizard to create two separate reports that will give me this information but I'd really love to have it all contained within one report. Is this possible?
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • I need a report that shows all open tickets >=21 days. I found one for 7,14, & 21 days, but I can't seem to edit out the 7 & 14 ones. Any ideas?
  • I have a canned report that shows me all tickets from the last 30 days - the only field I am missing is LOCATION, which we track as a user property. This field isn't available in the report wizard. My current data pull retrieves:
    Ticket ID Created Time Closed Submitter Status Owner Category Impact
    Would love to figure out how to get a "LOCATION" column. KACE support says this has to be a custom SQL script which I have no experience with.
  • Here's one, I'm trying to figure out the best way to create a report that shows all of the outdated BIOS versions. We are entirely DELL computers so this should be relatively native, right?
  • Am I too late to the party? Hell I'd pay for the reports I'm looking for.
    First a listing of all PCs grouped by location labels that includes the warranty info and user last logged in as well as the basic info like IP, MAC, Make and model.
  • I'm also hoping I''m not too late to the party. What we need is a report which shows dell updates available and their impact only on servers. I'm trying to get it done but I'm not the best with SQL yet. As someone said above, the K1000 is the only reason I need to start learning SQL hah.
  • I am trying to find a report that lists the last patch date for a server. Our customer wants a report that just lists servername, ip, os, and the last date it was patched. Currently we have this query but it does not get info from about 30% of the servers.
    SELECT DISTINCT A.NAME,A.OS_NAME,A.IP,
    MAX(B.DEPLOY_STATUS_DT) as Patch_Status
    FROM ORG1.MACHINE A,
    ORG1.PATCHLINK_MACHINE_STATUS B
    WHERE A.ID=B.MACHINE_ID
    GROUP BY NAME
  • Hello. I'm looking for a custom report that will query the provide a list of running services on systems and the 'log on as' username attached to those services. Thank you for any help with this.
Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Here are reports the CIO has requested to get a feel for the volume of tickets our main help desk queue gets within a month or week time frame. That might mean the last 30 days or a designated month like August (our busiest ticket month). It could be a graph rather than a spreadsheet showing the number of tickets created and the number of tickets in each category and/or status.

This is the specific request from the CIO:
- the number of tickets over a week time and month time period;
- and the status of each ticket, open, closed, pending, all categories.
Answered 01/22/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
Here are reports the CIO has requested to get a feel for the volume of tickets our main help desk queue gets within a month or week time frame. That might mean the last 30 days or a designated month like August (our busiest ticket month). It could be a graph rather than a spreadsheet showing the number of tickets created and the number of tickets in each category and/or status.

This is the specific request from the CIO:
- the number of tickets over a week time and month time period;
- and the status of each ticket, open, closed, pending, all categories.


Number of tickets created within the past 7 days:

SELECT COUNT(*) AS '# of Tickets Created' FROM HD_TICKET T
WHERE DATEDIFF(NOW(), CREATED) < 7


Number of tickets created within the past 30 days:

SELECT COUNT(*) AS '# of Tickets Created' FROM HD_TICKET T
WHERE DATEDIFF(NOW(), CREATED) < 30


All tickets opened within the past 7 days and their ID, Issue, and Status:

SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', S.NAME AS 'Status' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
WHERE DATEDIFF(NOW(), CREATED) < 7


All tickets opened within the past 30 days and their ID, Issue, and Status:

SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', S.NAME AS 'Status' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
WHERE DATEDIFF(NOW(), CREATED) < 30
Answered 01/22/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
1
Thanks Andy! They give me a great start. These are all handy for the first help desk queue (our main one) but what about other queues?

How about creating a graph of the number of tickets per category (we have 42 possibilities) over the last 30 days?

Using the Report Wizard do you know what would be the Regex expression to make a rule against the Ticket Info.Created field to check for less than 30 days (if possible)?
Answered 01/22/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
Rich, the reports I created show tickets from all queues. If you want to look at a specific queue, you'll have to add "AND T.QUEUE_ID = #" to the WHERE clause - replacing # with the queue number you want to display tickets from.

If you want to create a graph, you'll need to use ODBC and Excel or something similar to query the database and generate a graph. The reporting features in the KBOX cannot display graphs.

Using the Report Wizard do you know what would be the Regex expression to make a rule against the Ticket Info.Created field to check for less than 30 days (if possible)?

Unless I'm misunderstanding the question, one of the reports I created does this:

SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', S.NAME AS 'Status' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
WHERE DATEDIFF(NOW(), CREATED) < 30


EDIT: Rich, I just noticed that you were asking about Regex in the Report Wizard - that flew right over my head when I read this earlier... I'm not sure if this would be possible using Regex since you are trying to find the past 30 days... it's not really a pattern you're looking for. Besides, why bother with the reporting wizard when writing the SQL is much more robust?
Answered 01/23/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
1
ORIGINAL: airwolf
... Besides, why bother with the reporting wizard when writing the SQL is much more robust?


I agree there's much more power in SQL, however, I've only started learning SQL because of KBOX. Also, it's possible to create bar, line or pie charts instead of Tables already in the Report Wizard GUI interface.

Improvements to the GUI interface to allow selection of date ranges in the report wizard was requested at the last Konference. Kind of like how Quicken offers reports on Month to date, Year to date, Previous year, etc.

It would be great to be able to create charts over different time spans, for example:
  • a bar graph of tickets closed by technician
  • a bar graph of the number of tickets created per day
  • a pie chart of the categories of total tickets closed over X time period
  • a pie chart of the categories of tickets closed by technician over X time period


In the GUI wizard I can figure out everything except date criteria since all that's offered is to use Regex.

P.S. Andy, I just noticed your edited comment because I happened to be looking at previous posts. This forum only emails the original comment post and not edited comments. I also like to edit my posts on occasion for typos but by adding another comment instead of editing ensures people subscribed get the added content.
Answered 02/13/2010 by: RichB
Fourth Degree Green Belt

  • Yes, charts and graphs please!
Please log in to comment
0
Hi,

I'm hoping to find a report that will show me all "ASSET" items added to asset within the last "X" number of days. I'm not really talking about Inventory items, but Asset module stuff. What I'm trying to do is generate reports for manually entered Asset Items that can then be sent off to our Property Technicians so they can know what new items have come in the door and their property decal numbers, cost, etc.

Thanks!
Answered 03/12/2010 by: errinf
Senior Yellow Belt

Please log in to comment
0
ORIGINAL: errinf

Hi,

I'm hoping to find a report that will show me all "ASSET" items added to asset within the last "X" number of days. I'm not really talking about Inventory items, but Asset module stuff. What I'm trying to do is generate reports for manually entered Asset Items that can then be sent off to our Property Technicians so they can know what new items have come in the door and their property decal numbers, cost, etc.

Thanks!


Here is a simple report that will show all assets created within the past 7 days.

SELECT * FROM ASSET A
WHERE DATEDIFF(NOW(),A.CREATED) < 7
Answered 03/12/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Andy,

This is exactly what I was looking for! Thanks so much.
Answered 03/15/2010 by: errinf
Senior Yellow Belt

Please log in to comment
0
I'm hoping someone can help me figure out how to setup a report that shows ONLY servers and their patching status. I've got ones for all systems, but I need to additionally filter by "S_Windows_Servers" and not report on any desktops.

Thanks!
Answered 03/17/2010 by: errinf
Senior Yellow Belt

Please log in to comment
0
I'm hoping someone can help me figure out how to setup a report that shows ONLY servers and their patching status. I've got ones for all systems, but I need to additionally filter by "S_Windows_Servers" and not report on any desktops.
This should work, but I can't test it because my company doesn't use security patching in the KBOX.

SELECT * FROM MACHINE M
JOIN PATCHLINK_MACHINE_STATUS P ON (P.MACHINE_ID = M.ID)
WHERE M.OS_NAME like '%Server%'
Answered 03/18/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Andy,

Thanks for the assistance. I tried the query you offered and it errored out using the "select *", so I tried using this:

select NAME as MACHINE from MACHINE M
JOIN PATCHLINK_MACHINE_STATUS P ON (P.MACHINE_ID = M.ID)
WHERE M.OS_NAME like '%Server%'

It doesn't error out, but it generates a huge report that repeatedly shows nothing but a list of each of my servers by name (a few hundred times for each server), but no patch info. There is no column heading for anything patch related either, it only has a column heading for "MACHINE".

Any ideas?

Thanks again
Answered 03/22/2010 by: errinf
Senior Yellow Belt

Please log in to comment
0
Errinf, if the tables list the status of each patch, you'll have a row for each server for each patch - this would mean hundreds of rows per server. You can use 'GROUP BY NAME' at the end of the query, but this will only give you accurate data for the names of all of your servers, it will not concatenate the patch statuses into a single field. I wish I could give you more help, but I haven't used security patching yet, so I have no sample data to build a query from.
Answered 03/22/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Andy,

Thanks for the help. I'll keep plugging away at it in my spare time.

-E
Answered 03/22/2010 by: errinf
Senior Yellow Belt

Please log in to comment
0
I would be ever so grateful if someone could help me with this Helpdesk report. I am not very good at all with SQL, and I have wasted hours so far trying to take existing reports and modify them to work.

We have 10 people on the Helpdesk. Each Monday morning, I would like to run a report which shows what tickets each person still has open (ID, title, Timeopen, submitter, status, priority), and what tickets they have closed during the past 7 days. For the tickets they still have open, I would like to have this show every ticket they have open, not just the ones that have been open the past 7 days.

This is a report I will circulate each week showing what we have done the past week, and what we still have in the works.

Thank you for your help.
Answered 03/24/2010 by: btrotter
Senior Yellow Belt

Please log in to comment
2
Btrotter, you should be able to tweak the examples I posted earlier in this thread to suit your needs.

Create a custom SQL report and set Owner as the break column, then paste this into the SQL field:


SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', T.TIME_OPENED AS 'Time Opened', O.FULL_NAME AS 'Owner', U.FULL_NAME AS 'Submitter', S.NAME AS 'Status', P.NAME AS 'Priority' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
JOIN USER O ON (T.OWNER_ID = O.ID)
JOIN HD_PRIORITY P ON (T.HD_PRIORITY_ID = P.ID)
WHERE (S.STATE = 'opened') OR (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 7)
ORDER BY O.FULL_NAME, T.TIME_OPENED
Answered 03/24/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Fantastic!! That was exactly what I needed! Thank you very much!
Answered 03/25/2010 by: btrotter
Senior Yellow Belt

Please log in to comment
0
Btrotter, you can change the ORDER BY statement if you'd like. For example, the following would sort by status first, then time opened:

ORDER BY O.FULL_NAME, S.NAME, T.TIME_OPENED
Answered 03/25/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thanks airwolf. I was able to look at one of your examples above and figure that part out.
One more question if I may. I just noticed the report does not show the unassigned tickets. How do I get those results to display?
Answered 03/25/2010 by: btrotter
Senior Yellow Belt

Please log in to comment
0
Unassigned tickets have an Owner ID of 0, so you can change the join to the Owner (USER) table to a LEFT JOIN. This will display Null results for Owner (i.e. Unassigned). You can also add the IFNULL function to the Owner field to display "Unassigned" as the owner for Null values.

SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', T.TIME_OPENED AS 'Time Opened', IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', U.FULL_NAME AS 'Submitter', S.NAME AS 'Status', P.NAME AS 'Priority' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
JOIN HD_PRIORITY P ON (T.HD_PRIORITY_ID = P.ID)
WHERE (S.STATE = 'opened') OR (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 7)
ORDER BY O.FULL_NAME, S.NAME, P.NAME, T.TIME_OPENED
Answered 03/25/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
You're good!
Thanks again for your help.
Answered 03/25/2010 by: btrotter
Senior Yellow Belt

Please log in to comment
0

I am new to SQL as well and I am looking for a report to list a few simple things for me. Hoping someone can help......
I need a report to show tickets left open for a set period of time like 5 days. and be able to run for a single user like Bob....
the columns I would need are Problem Description, the time it has been in an opened state (like 6D 1H) and Ticket priority.

I would not need to see status and owner as those would be set in advanced.[/align]
Thanks
Answered 04/01/2010 by: lucken
Yellow Belt

Please log in to comment
0
Lucken, what you're looking for is very similar to what I've created in http://itninja.com/question/faulttree-109953.

Try this on for size. Make sure you change 'Queue Name' appropriately. When you setup the custom SQL report, set the break column to Owner and the report will automatically break out the tickets by Owner.

SELECT T.ID AS 'TICK', T.TITLE AS 'Problem Description', U.FULL_NAME AS 'Owner', T.CREATED AS 'Time Created', CONCAT(DATEDIFF(NOW(), T.CREATED),'D') AS 'Time Opened', P.NAME AS 'Priority' FROM HD_TICKET T
JOIN USER U ON (U.ID = T.OWNER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN HD_PRIORITY P ON (P.ID = T.HD_PRIORITY_ID)
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='Queue Name')
WHERE NOW() > DATE_ADD(T.CREATED, INTERVAL 5 DAY) AND T.OWNER_ID <> 0 AND S.STATE = 'opened'
ORDER BY T.CREATED
Answered 04/01/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thanks man Now I just need to get my butt in gear and learn how to do these query's
Answered 04/02/2010 by: lucken
Yellow Belt

Please log in to comment
0
My manager wants to see how ticket creation is distributed throughout the day, so if possible I'd like to be able to have a report which shows the total number of tickets created by hour, for the past 60 or 90 days. I don't need information per day, week or anything like that, just a report that shows the busy times of the day.

Thanks!

Alistair
Answered 04/20/2010 by: alogie
Senior Yellow Belt

Please log in to comment
0
I'm trying to build a report that will show me a list of all systems and a count of their unapplied patches. This is as close as I got:

Select
CONCAT(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) as MACHINE_NAME,
COUNT(*) AS TOTAL,
where
MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID and
PATCHLINK_MACHINE_STATUS.PATCHUID = KBSYS.PATCHLINK_PATCH.UID and
PATCHLINK_MACHINE_STATUS.STATUS = 'NOTPATCHED'
order by TOTAL, MACHINE_NAME

But I've got something wrong and my SQL skills are insufficient to find the error. Any suggestions?
Answered 08/03/2010 by: jkknorr
Senior Yellow Belt

Please log in to comment
2
ORIGINAL: jkknorr

I'm trying to build a report that will show me a list of all systems and a count of their unapplied patches. This is as close as I got:

Select
CONCAT(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) as MACHINE_NAME,
COUNT(*) AS TOTAL,
where
MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID and
PATCHLINK_MACHINE_STATUS.PATCHUID = KBSYS.PATCHLINK_PATCH.UID and
PATCHLINK_MACHINE_STATUS.STATUS = 'NOTPATCHED'
order by TOTAL, MACHINE_NAME

But I've got something wrong and my SQL skills are insufficient to find the error. Any suggestions?


You are missing a couple pieces in your query. You forgot the FROM statement and you need to use joins instead of the WHERE clause to get the results you want. Finally, when you use COUNT(), you have to use a GROUP BY statement. Try this:

SELECT
CONCAT(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) as MACHINE_NAME,
COUNT(KBSYS.PATCHLINK_PATCH.UID) AS TOTAL
FROM MACHINE
LEFT JOIN PATCHLINK_MACHINE_STATUS ON MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID
LEFT JOIN KBSYS.PATCHLINK_PATCH ON PATCHLINK_MACHINE_STATUS.PATCHUID = KBSYS.PATCHLINK_PATCH.UID
WHERE PATCHLINK_MACHINE_STATUS.STATUS = 'NOTPATCHED'
GROUP BY MACHINE_NAME
ORDER BY TOTAL desc, MACHINE.NAME asc


Dan
Answered 08/10/2010 by: DContreras
Orange Belt

Please log in to comment
0
I am trying to develop an "Escalated/Open Tickets by Owner" report for each director in our technology team. I created team label and assigned the appropriate label to each of the ticket owners. An example would be a Networking label for those who work Network-related tickets. I will schedule this report to run each week and email to the director.

What I'm having trouble with is the correct way to do the joins once I duplicate the standard escalation report. I'm not very advanced at joining and SQL. Any advice on how to modify the standard query for the label criteria?
Answered 08/24/2010 by: plualum
Yellow Belt

Please log in to comment
0
I was able to figure out the query with the help of our internal programmers. For reference, here is the slightly modified Escalation Report query:

select HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.ESCALATED,
HD_TICKET.DUE_DATE,
CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
left join USER_LABEL_JT on HD_TICKET.OWNER_ID = USER_LABEL_JT.USER_ID
where HD_STATUS.STATE = 'opened' and ESCALATED != '0000-00-00 00:00:00'
AND USER_LABEL_JT.LABEL_ID = 261
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL


It does not exactly behave as I'd expect in that the ESCALATED field does not reset once a comment or status change is made to a ticket. In other words, the ticket remains on the report even if it is not actively in escalation. If you have any idea on how to get a report with tickets truly escalating, I'd love to know!
Answered 08/24/2010 by: plualum
Yellow Belt

Please log in to comment
0
Background:
HP serial numbers reflect the date of manufacture using year and week . We have the need to extract these digits from the bios serial number in a report.

example: MXL8350BLV the fourth digit “8” reflects the year 2008 and the sixth and seventh digits “35” reflect the week of the year. In this example the PC was manufactured between the 24th and 30th of August 2008.

The info I need is the 4th-6th digits of the s/n broken out (835 in the example). Ultimately the digits would need to be converted to a date (Year and Month) if possible.



Example of a report I am using that would need the s/n date conversion info added:

SELECT LABEL.NAME AS LABEL_NAME, SYSTEM_DESCRIPTION, MACHINE.NAME AS SYSTEM_NAME, USER_FULLNAME, BIOS_SERIAL_NUMBER, CS_MODEL FROM MACHINE LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden') WHERE (1 in (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'SS-BM')) GROUP BY MACHINE.ID ORDER BY LABEL.NAME asc




Thanks
Answered 08/25/2010 by: Spyder2114
Senior Yellow Belt

Please log in to comment
0
Hello,

We are looking for a specific report that will reflect the status of the deployed patches on date “X” in label “Y”. The report would be similar to the view available in
Computers: Detail Item “PC_XX”
Deployment Status - Patched

Thank you
Answered 09/03/2010 by: vsd
Yellow Belt

Please log in to comment
0
Hi guys,

Can anyone help me whip out the SQL for these reports?
And is it even possible to extract these types of data in one report?
Thanks a bunch!

1. Number of computers by office

- Breakdown by type (desktop and laptop)
- Breakdown by type position classification (managers, sup, R&F)
- Breakdown by year of purchase
- Breakdown by warranty status
- Breakdown by Windows version
- Breakdown by Processor type
- Breakdown by disk utilization (<50 GB, 50-100, 100-150,>150)
- Breakdown by Windows Patch update status

(some of these info shall be added via linked asset entries)

Example: Breakdown by type and so on from all the list:

name of branch | no. of laptops | no. of branded desktops | no. of cloned desktops |
-------------------------------------------------------------------------------------------------
branch a | 10 | 12 | 14 |
branch b | 13 | 16 | 15 |
-------------------------------------------------------------------------------------------------
total | 23 | 28 | 29 |


No Pressure! [:D]
Answered 09/08/2010 by: shtamsec
Senior Yellow Belt

Please log in to comment
0
Spyder

I think this should get you going.
SELECT LABEL.NAME AS LABEL_NAME,
SYSTEM_DESCRIPTION,
MACHINE.NAME AS SYSTEM_NAME,
USER_FULLNAME,
BIOS_SERIAL_NUMBER,
CS_MODEL,
Substring(BIOS_SERIAL_NUMBER, 4, 1) AS YEAR,
Substring(BIOS_SERIAL_NUMBER, 5, 2) AS WEEK_OF_YEAR
FROM MACHINE
LEFT JOIN MACHINE_LABEL_JT
ON ( MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID )
LEFT JOIN LABEL
ON ( LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
AND LABEL.TYPE <> 'hidden' )
WHERE ( 1 IN (SELECT 1
FROM LABEL,
MACHINE_LABEL_JT
WHERE MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
AND LABEL.TYPE <> 'hidden'
AND LABEL.NAME = 'SS-BM') )
GROUP BY MACHINE.ID
ORDER BY LABEL.NAME ASC
Answered 11/05/2010 by: dchristian
Red Belt

Please log in to comment
0
VSD,

What view are you talking about?

Is this what your looking for?
SELECT Date_format(P.STATUS_DT, '%m/%d/%Y') STATUS_DATE,
M.NAME,
PP.IDENTIFIER,
PP.TITLE,
P.STATUS,
L.NAME
FROM PATCHLINK_MACHINE_STATUS P,
KBSYS.PATCHLINK_PATCH PP,
PATCHLINK_PATCH_LABEL_JT PL,
LABEL L,
MACHINE M
WHERE P.PATCHUID = PP.UID
AND P.PATCHUID = PL.PATCHUID
AND PL.LABEL_ID = L.ID
AND M.ID = P.MACHINE_ID
AND L.NAME = ' TEST LABEL' /* LABEL NAME HERE */
AND Date_format(P.STATUS_DT, '%m/%d/%Y') = '11/03/2010'
/* DATE CAN GO HERE */
ORDER BY STATUS_DATE,
L.NAME
Answered 11/05/2010 by: dchristian
Red Belt

Please log in to comment
0
I'm looking for a report that would provide me a list of computer and the last date/time a user logged into it. In other words I want the following detail; Computer Name, IP Address, Last Logged on User, Most Recent logon date/time.
Answered 11/30/2010 by: jwilcox746
Senior Yellow Belt

Please log in to comment
0
i'm just a newbie when it comes to SQL and i really need some help... i'm trying to create a report that will show machine count per itemized chassis type and this is as far as i got and i don't know what's wrong with it ...

SELECT LABEL.NAME AS LOCATION,
(SELECT COUNT(*)FROM MACHINE WHERE MACHINE.CHASSIS_TYPE = 'laptop' AND MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.USAGE_MACHINE = 1) AS LAPTOP,
(SELECT COUNT(*)FROM MACHINE WHERE MACHINE.CHASSIS_TYPE = 'desktop' AND MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.USAGE_MACHINE = 1) AS DESKTOP,
(SELECT COUNT(*)FROM MACHINE WHERE MACHINE.CHASSIS_TYPE = 'server' AND MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.USAGE_MACHINE = 1) AS SERVER,
(SELECT COUNT(*)FROM MACHINE WHERE MACHINE.CHASSIS_TYPE = 'other' AND MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.USAGE_MACHINE = 1) AS OTHERS,
COUNT(MACHINE.NAME) AS PC_COUNT
FROM LABEL, MACHINE_LABEL_JT, MACHINE
WHERE MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
AND LABEL.USAGE_MACHINE = 1
GROUP BY LOCATION ORDER BY PC_COUNT DESC

the problem is with the wrong count per chassis type and not with the LOCATION as i'm testing this as if i only used my machine label as my machines' location and that's why i selected the LABEL.NAME and tag it as LOCATION. Hope someone would help me with this..thanks in advance..
Answered 12/05/2010 by: qu@dr0@l@s
Yellow Belt

Please log in to comment
0
Has anyone got a report for listing the number of tickets that are closed by submitter? i.e. not closed by the submitter, but are closed and assigned to a submitter.
Answered 01/25/2011 by: stubox
Blue Belt

Please log in to comment
0
stubox,

Something like this?

SELECT U.FULL_NAME AS SUBMITTER,
COUNT(*) AS CLOSED_TICKETS
FROM HD_TICKET H,
HD_STATUS S,
USER U
WHERE H.HD_STATUS_ID = S.ID
AND U.ID = H.SUBMITTER_ID
AND S.STATE = 'CLOSED'
GROUP BY U.FULL_NAME
ORDER BY U.FULL_NAME
Answered 01/25/2011 by: dchristian
Red Belt

Please log in to comment
0
Thanks dchristian that will do the trick!
Answered 01/26/2011 by: stubox
Blue Belt

Please log in to comment
0
I need 3 reports...or 1 that shows 3 different areas. My boss wants to see a report for hardware changes on a monthly basis. Basically he just wants a report of new assets added to the domain. The 2nd report is for Patches applied for the previous month, and the 3 is for new software installed/detected for the previous month. Is this possible?
Answered 02/09/2011 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
0
I am looking for a report that will give the average "first response" time for tickets from a specific queue for a given time range. The "first response" is from when the ticket was submitted by email to when the status was changed to open. So it would output a single number that says the average ticket response time for this month was 2 hours and 15 minutes or something along those lines. I need to run this report for this one queue (we have 10 queues) each month for management reporting. If anyone has a good idea on how to do this I would really appreciate it.
Answered 03/07/2011 by: amccabe
Senior Yellow Belt

Please log in to comment
1
I am researching how to create a report that will do the following: find any PST files that are on users' computers, and list their file size, and last modified date. Is it possible to search file systems with KACE and generate a report for this?
Answered 03/21/2011 by: Sean85
Orange Belt

Please log in to comment
0
Sean85, if you figure this out or get this working in any way, will you please post how you did it? I'm trying to do the exact same thing and haven't come up with any good solutions yet.
Answered 03/21/2011 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
1
Sean,

This would be great for a script!

I would write a script that searches the hard drive for the pst files, when it finds them, record their size and write it all to the registry (or a text file).

You could then pull a custom inventory rule to get this info out, create labels, etc...
Answered 03/22/2011 by: dchristian
Red Belt

Please log in to comment
0
I'm putting together some basic metrics to report back to my director. I would like to use an existing precanned report and simply change the output format. The existing report is called "Stalled/Open Tickets by Owner". All I want out of this report are the owers name and total number of tickets in each owners queue. (I don't need the data on each ticket) What is the easiest way to get this? Thanks in advance for any assistance.

The original report query is:
select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE,
CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened'
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
Answered 03/24/2011 by: apouteau
Yellow Belt

Please log in to comment
0
I'm looking for a report that I thought I saw on here but perhaps imagined it. I'd like a report that lists the number of tickets opened and tickets closed per day for the last 365 days. The output would look like below. Does anyone have one for this?

Day | Opened | Closed
------------------------------------
01/01/2011 | 22 | 30
02/01/2011 | 19 | 23
...
...
...etc
Answered 04/06/2011 by: stubox
Blue Belt

Please log in to comment
0
Hi,

We have a large implementation with a single org. Many different sites with workstations assigned to smart lables via IP address. I need a report that can show patch compliancy of systems in certain machine labels (or site locations). In addition, I need to check patch compliancy for patches associated with a specific patch lable.

In a nutshell, the report should provide the following: Machines in at a specific site (ie the machine IP address smart label) not compliant by patch (ie patches assigned to a specific patch lable)

Thanks in advance for any help you can provide.

Scott
Answered 04/20/2011 by: scottw
Yellow Belt

Please log in to comment
1
Report for Tickets Created by Whom in the last day, week, or month, grouped by Whom

I can pull the tickets created for the last day no problem but I want to capture who created the ticket. For example did someone from the Service Desk create it from an incoming call, did a user create it by sending an e-mail, did a user create it by logging in to the system?

Basically I want to capture the information that appears in the header of each initial comment for a ticket:

nwalsh on Apr 21 2011 08:51:07 AM (via email)
Owners Only:

* Ticket Created
* Added attachment 'image001.png'


Any ideas?
Answered 04/21/2011 by: cawilson
Senior Yellow Belt

Please log in to comment
0
I need some help if anyone is willing:

I have created two new custom fields and would like to be able to report on the amount of time from ticket creation until the change of those fields (two separate reports). I'm using CUSTOM_1 and CUSTOM_2 of the SERVICE TICKET queue.

Thanks in advance for any help provided.
Answered 05/02/2011 by: nslatter
Senior Yellow Belt

Please log in to comment
0
I am looking for a report that will display the machines that are failing critical patches, which patches are failing and the asset/model of the machine.

Thanks!
TMeyers
Answered 07/11/2011 by: TMeyers
Yellow Belt

Please log in to comment
0
Hello - I am looking for some assistance. I looked through the report wizard and do not see any easy way of getting what I need.

I am looking for a report that will show me all machines with the status "waiting to connect" in the Schedule task status > current phase section

If anyone would be so kind to assist I would really appriciate it. I am still learning SQL

Thanks
Answered 08/18/2011 by: Palmjx
Senior Yellow Belt

Please log in to comment
0
Palmjx,

Take a look at the KBSYS.KONDUCTOR_TASK table.

That should get you going in the right direction.
Answered 08/18/2011 by: dchristian
Red Belt

Please log in to comment
0
Hello Fellow Kacers!

I am trying to create report that shows the AVG ticket turn around time (open to close) for a given period (say a month). I nearly have it there, but I want the avg to displayed in a new row. So basically, all the columns in the select statement need to display with an addition row for avg turnaround time. Ideally, I'd like to display it in hours, but I'm not sure of the right sytax for the TIMESTAMPDIFF func (or if that is even the right function to use).

Any help is greatly appreciated. It's almost there...just needs some tweaking.


select HD_TICKET.ID,
HD_TICKET.TIME_OPENED, HD_TICKET.TIME_CLOSED, CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_TO_CLOSE,
TIMESTAMPDIFF(MINUTE, HD_TICKET.TIME_OPENED, HD_TICKET.TIME_CLOSED) as "TIME IN MINUTES", AVG("TIME IN MINUTES"),
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
Answered 11/23/2011 by: sho_nuff1997
Yellow Belt

Please log in to comment
0
sho_nuff1997,

You could use this to get the time in minutes
((UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED) -UNIX_TIMESTAMP(HD_TICKET.TIME_OPENED))/60) AS "TIME IN MINUTES",
What do you mean by
"all the columns in the select statement need to display with an addition row for avg turnaround time"
Would this be the same average over and over?
Answered 11/23/2011 by: dchristian
Red Belt

Please log in to comment
0
Thank you for your reply, dchristian.

If you remove the 'TIMESTAMPDIFF' portion of the select statement you get a query that returns a number of rows (one for each ticket for the period) with the following columns:

#, Id, Time Opened, Time Closed, Time To Close, Priority, Category, Status, Impact, Machine, Name, Submitter Name

but if I put the 'TIMESTAMPDIFF' back in the select statement, the query returns only one row with the same columns.

I need to have the a row for each ticket and then a seperate row with an avg of the 'TIME TO CLOSE' column.
Answered 11/28/2011 by: sho_nuff1997
Yellow Belt

Please log in to comment
1
Is it possible to run a monthly report of user logon/logoff times along with the pc associated?
Answered 12/01/2011 by: ohiosoundguy
Eighth Degree Black Belt

Please log in to comment
0
@ohiosoundguy hmm... that's a good one.

I would write a script that looks at the event log to get the logon / logoff times.

You could then use custom inventory rules to pull in the results.
Answered 12/01/2011 by: dchristian
Red Belt

Please log in to comment
0
Morning all,

I have been trying to create a report to show all of my assets by office. Something that should be pretty simple but we can't figure this one out at all. Does anyone have such a report already or willing to offer up any guidance? I need to display asset name, serial number, cost, office, model and make for each asset type (these fields exist in all asset types) and each asset is linked to my Offices asset type.
I can report on these for one asset type no problem but when I try to add in my other asset types(of which I have 5), I just get nowhere.

I did find at the recent KACE conference that we can go into the Assets section and click on View by | Location to show all assets that way. That's great but the table doesn't display the fields that I'm after and I can't see a way to customise that either - hence I'm trying to achieve this through reporting.

Cheers. Dave
Answered 12/12/2011 by: dpyett
Third Degree Blue Belt

Please log in to comment
0
Dpyett,

The trouble with assets are the tables are different for each KBox.

When you create a new asset or field, the tables / columns get created on the fly.

You would need to develop something custom.
Answered 12/13/2011 by: dchristian
Red Belt

Please log in to comment
0
I am looking to create a report that will:

1) list a specific software name from all computers in org (only 300 machines), last logged on user (to help me track down whom i need to contact), and version of the software

I found this example on kace resources but it does not list usernames, according to the mysql workbench query for user names are always null no matter what i set the m.user_fullname to?? I know I am so close I can taste it but I am a complete newb with sql.

select S.DISPLAY_NAME as SOFTWARE_NAME,
S.DISPLAY_VERSION as VERSION,
M.NAME as MACHINE_NAME,
M.USER_FULLNAME as LAST_USER
FROM SOFTWARE S
left join MACHINE_SOFTWARE_JT MSJ on S.ID = MSJ.SOFTWARE_ID
left join MACHINE M on MSJ.MACHINE_ID = M.ID
where S.DISPLAY_NAME like 'Windows Internet Explorer 8%'
and not IS_PATCH
order by S.DISPLAY_NAME, S.DISPLAY_VERSION, M.NAME

Thanks in advance!
Answered 01/10/2012 by: terabyte33
Yellow Belt

Please log in to comment
0
Hey Terabyte,

You might have better luck using the USER field from the machine table.
SELECT S.DISPLAY_NAME as SOFTWARE_NAME,
S.DISPLAY_VERSION as VERSION,
M.NAME as MACHINE_NAME,
M.USER as LAST_USER
FROM SOFTWARE S
left join MACHINE_SOFTWARE_JT MSJ
on S.ID = MSJ.SOFTWARE_ID
left join MACHINE M
on MSJ.MACHINE_ID = M.ID
WHERE S.DISPLAY_NAME LIKE 'Windows Internet Explorer 8%'
and not IS_PATCH
ORDER BY S.DISPLAY_NAME, S.DISPLAY_VERSION, M.NAME

Problem with that, is its username not full name.
This query will link up to the user table and pull the full name (just remember your user's have to be imported).
SELECT S.DISPLAY_NAME as SOFTWARE_NAME,
S.DISPLAY_VERSION as VERSION,
M.NAME as MACHINE_NAME,
M.USER as LAST_USER,
COALESCE(U.FULL_NAME,') AS USER_NAME_FORM_USER_TABLE
FROM SOFTWARE S
LEFT JOIN MACHINE_SOFTWARE_JT MSJ
on S.ID = MSJ.SOFTWARE_ID
LEFT JOIN MACHINE M
on MSJ.MACHINE_ID = M.ID
LEFT JOIN USER U
ON M.USER = U.USER_NAME
WHERE S.DISPLAY_NAME LIKE 'Windows Internet Explorer 8%'
and not IS_PATCH
ORDER BY S.DISPLAY_NAME, S.DISPLAY_VERSION, M.NAME

I left both user fields so you could compare.
Answered 01/10/2012 by: dchristian
Red Belt

Please log in to comment
0
dchristian,

Thanks for the fast reply, i really appreciate it. When you say the users have to be imported does that include ldap imports? We have users imported into the kbox via ldap queries primarily for the service desk will this suffice for users being imported or am i still missing something? I am assuming because these are imported via ldap that is why I still recieve null values correct?
Answered 01/10/2012 by: terabyte33
Yellow Belt

Please log in to comment
0
You are correct.

They user names will need to be there (imported from ldap).
Answered 01/10/2012 by: dchristian
Red Belt

Please log in to comment
0
The usernames are imported via ldap into the kbox and I ran the report you "tweaked" for me on a different org and it worked. I have a few records that are Null but I can live with that. Once again thank you so much!
Answered 01/10/2012 by: terabyte33
Yellow Belt

Please log in to comment
0
Afternoon all,

I'm trying to get a report of all software within rooms.

Rooms are specified in the hostname e.g. DWF0N123499999 (N1234 is the room)
However, it needs to be a report of all the software in each room, so I have created a smart label for most rooms.

I beleive I could possibly do this a few ways...
Pull off all software for an individual label name, or, group up some of the labels and pull off all software by label group?
Or, get the report to somehow look at the 5th, 6th, 7th, 8th and 9th character of the hostname and list the software for each unique instance of that block of characters?

I know what I need to ask of it, just not how :(



Any help would be greatly appreciated.

I've tried out a few reports from here that work off label names, but we have 172 rooms! A) I'm going to have to add 172 labels and B) add them all to the SQL of a report?


Even better, I have a spreadsheet of all room numbers
Answered 01/18/2012 by: Husky
Yellow Belt

Please log in to comment
0
Hi everyone,

I am trying to create a report which shows our software inventory that is not in a label but does not contain titles that are not installed. I also need it to list the machine names where the unlabled software is installed.

Thank you. I have part of this. I just need help tweaking it.
Answered 01/30/2012 by: tayana
Orange Senior Belt

Please log in to comment
0
Okay I have my report almost completed on how I want it, Thanks to this forum I have created this so far, which is not really that intense, but I am proud of myself.

I am wanting to add 'worked logged' to this and have not been successfully. Can someone let me know where the 'work time' lives?

Then after that is added I was wanting this to do averages for me, ie User A had X amount of tickets and took him an average of Y time to complete.
and Team 1 with Users A, B, and C had a combined X amount of tickets and took them and average of Z time to complete.




select HD_TICKET.ID,
HD_TICKET.CREATED AS CREATED,
HD_TICKET.TIME_CLOSED AS CLOSED,
CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.CREATED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.CREATED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.CREATED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.CREATED))),
'%kh %im')) AS TIME_TO_CLOSE,
HD_PRIORITY.NAME as PRIORITY,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER from HD_TICKET
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
where HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL
Answered 02/03/2012 by: codywalraven
Senior Yellow Belt

Please log in to comment
0
I have no knowledge of SQL and I've been trying to modify the built in Escalated/Open Tickets by Owner report. What I need to do is have a report that shows all opened tickets that reached thier escalation time in each Priority state. (i.e Medium 10 days High 4 hours etc.) All we need to see in the report is Created, Time Opened, Title, Ticket #, and Owner. We thought the built in report would work but we are seeing results with tickets that should not be in the report (Tickets that are only a day old).
Any help would be much appreciated.
Answered 02/21/2012 by: dalook
Orange Belt

Please log in to comment
0
How do we use the wizard to generate a bar graph report for the total number of tickets recieved over the past year seperated by month? Also i'd like a graphical report from the wizard that can help me show how many total tickets my desktop support team has closed in the past month seperated by week.

The default weekly report will show me the details that i need to see for the tickets closed over the past 7 days ( or 30 days, etc...) but i'd like an easy graph to compare just the number of ticket closures.

This is similar to post #5 which i don't see any response to.

Thank you.
Answered 02/21/2012 by: Jroy
Senior Yellow Belt

Please log in to comment
0
I'm new here and I followed this post to try to figure out a report I need but wasn't able to. Here's what I need:

A report that will give me all of the software installed on computers in a specific label. Help. Thanks!
Answered 02/21/2012 by: jenbar
Senior Yellow Belt

Please log in to comment
0
Hi, I have NO SQL knowledge so I am appealing for help.
There is a report in the K1000 for Dell machines with an expired warranty.
Can someone modify the existing or write one to show Dell machines still under warranty with all the info that is shown in the existing report?

Very much appreciate it.
Answered 02/22/2012 by: Tekkie
Yellow Belt

Please log in to comment
0
Jenbar,

Are you talking about machine label or software label?

If your looking for machine label, this is possible thru the wizard.
Answered 02/22/2012 by: dchristian
Red Belt

Please log in to comment
0
Tekkie,

Thats not a bad idea.

See if this works:
SELECT M.NAME AS MACHINE_NAME,
M.CS_MODEL AS MODEL,
DA.SERVICE_TAG,
DA.SHIP_DATE,
M.USER_LOGGED AS LAST_LOGGED_IN_USER,
DW.SERVICE_LEVEL_CODE,
DW.SERVICE_LEVEL_DESCRIPTION,
DW.END_DATE AS EXPIRATION_DATE
FROM KBSYS.DELL_WARRANTY DW
LEFT JOIN KBSYS.DELL_ASSET DA
ON ( DW.SERVICE_TAG = DA.SERVICE_TAG )
LEFT JOIN MACHINE M
ON ( M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG
OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG )
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!='
AND DA.DISABLED != 1
AND DW.END_DATE > NOW()
AND DW.SERVICE_TAG IN (SELECT SERVICE_TAG
FROM KBSYS.DELL_WARRANTY
WHERE END_DATE > NOW());
Answered 02/22/2012 by: dchristian
Red Belt

Please log in to comment
0
im hoping you could help me put together a patch report.
i have selected a group of machines and labeled them 'web team'
i ran a detect patch schedule to see if they were missing patches.
now i would like a report that looks at that label and gives me the names/titles of the patches that are missing for that group of machines that were scanned.
any help would be appreciated. I am a newbie in terms of SQL and not sure where to begin.
Answered 02/22/2012 by: ryklism
Orange Belt

Please log in to comment
0
i have found this FAQ report that shows all patched installed or not. how do I filter for just the not installed?

Select M.NAME as MACHINE_NAME,
PP.TITLE AS PATCH_NAME,
PP.IMPACTID AS IMPATCT,
CASE WHEN MS.STATUS='PATCHED'
THEN 'Installed' WHEN MS.STATUS='NOTPATCHED'
THEN 'Not Installed' END AS 'PATCHED STATUS'
FROM PATCHLINK_MACHINE_STATUS MS
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = M.ID
JOIN LABEL L ON L.ID = MLJT.LABEL_ID AND PP.IMPACTID = ('Critical')
AND PPS.STATUS = 0 /* 0=active patches */
AND L.NAME LIKE ('Lecterns')
order by MACHINE_NAME, PP.TITLE
Answered 02/22/2012 by: ryklism
Orange Belt

Please log in to comment
0
found my own answer. just had to add "AND MS.STATUS='NOTPATCHED'"

here is the complete report

Select M.NAME as MACHINE_NAME,
PP.TITLE AS PATCH_NAME,
PP.IMPACTID AS IMPATCT,
CASE WHEN MS.STATUS='PATCHED'
THEN 'Installed' WHEN MS.STATUS='NOTPATCHED'
THEN 'Not Installed' END AS 'PATCHED STATUS'
FROM PATCHLINK_MACHINE_STATUS MS
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = M.ID
JOIN LABEL L ON L.ID = MLJT.LABEL_ID AND PP.IMPACTID = ('Critical')
AND PPS.STATUS = 0 /* 0=active patches */
AND L.NAME LIKE ('INSERT LABEL HERE')
AND MS.STATUS='NOTPATCHED'
order by MACHINE_NAME, PP.TITLE
Answered 02/22/2012 by: ryklism
Orange Belt

Please log in to comment
0
@ryklism,

SELECT Distinct CASE
WHEN MACHINE.SYSTEM_DESCRIPTION = ' THEN MACHINE.NAME
WHEN MACHINE.SYSTEM_DESCRIPTION != ' THEN
Concat(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION)
END AS MACHINE_NAME,
P.TITLE AS DISPLAY_NAME,
P.IDENTIFIER AS KB_ARTICLE
FROM PATCHLINK_MACHINE_STATUS S,
MACHINE,
KBSYS.PATCHLINK_PATCH P,
PATCHLINK_PATCH_LABEL_JT PL,
LABEL PATCH_LABEL,
LABEL MACHINE_LABEL,
MACHINE_LABEL_JT ML
WHERE MACHINE.ID = S.MACHINE_ID
AND S.PATCHUID = P.UID
AND PL.PATCHUID = P.UID
AND PATCH_LABEL.ID = PL.LABEL_ID
AND ML.MACHINE_ID = MACHINE.ID
AND MACHINE_LABEL.ID = ML.LABEL_ID
AND S.STATUS != 'PATCHED'
/* Uncomment Below Line to be able to search by patch label as well */
/* AND PATCH_LABEL.NAME IN('win 7 x64 critical < 60') */
AND MACHINE_LABEL.NAME IN ('web team')
ORDER BY MACHINE_NAME,
P.TITLE;
Answered 02/22/2012 by: dchristian
Red Belt

Please log in to comment
0
dchristian,
Computer label.
Thanks
Answered 02/22/2012 by: jenbar
Senior Yellow Belt

Please log in to comment
2
@jenbar,

You can do something like this:
SELECT DISTINCT MACHINE.NAME AS SYSTEM_NAME,
LABEL.NAME AS LABEL_NAME,
SOFTWARE.DISPLAY_NAME AS SOFTWARE_DISPLAY_NAME
FROM MACHINE
LEFT JOIN MACHINE_LABEL_JT
ON ( MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID )
LEFT JOIN LABEL
ON ( LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
AND LABEL.TYPE <> 'hidden' )
LEFT JOIN MACHINE_SOFTWARE_JT
ON ( MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID )
LEFT JOIN SOFTWARE
ON ( SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID )
WHERE LABEL.NAME = 'workstations' -- <---- Your label Goes here
/* Delete the below line if you wanna see the MS updates */
AND SOFTWARE.DISPLAY_NAME NOT LIKE '%UPDATE FOR%'
ORDER BY SYSTEM_NAME,
SOFTWARE.DISPLAY_NAME


Change workstations to whatever label your looking for.
Answered 02/22/2012 by: dchristian
Red Belt

Please log in to comment
0
Now the powers that be would like a report that not only shows the escalated open tickets but all tickets that are over their escalated time even if they are in a stalled state. so basically if we have a Medium Proirity escalated after 10 days we need have it flagged even if it has been stalled. Is there a way of doing this?

Thanks
Answered 02/23/2012 by: dalook
Orange Belt

Please log in to comment
0
Hi I'm very new to Kbox. I'm looking for a way to make a report show me a list of Computer names, models, descriptions and amount of Ram on each unit.
Also are there any good guides or tutorials written for new users to Kace? Thanks for the help
-David
Answered 02/23/2012 by: Davidr28
Senior Yellow Belt

Please log in to comment
0
Davidr28,

Take a look at the "Computer Listing by Memory" report.
Answered 02/27/2012 by: dchristian
Red Belt

Please log in to comment
0
DALOOK,

Does this do what your looking for?
SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.ESCALATED,
HD_TICKET.DUE_DATE,
Concat(IF (Time_to_sec(NOW()) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(NOW()) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_OPEN,
HD_PRIORITY. NAME AS PRIORITY,
HD_CATEGORY. NAME AS CATEGORY,
HD_STATUS. NAME AS STATUS,
HD_IMPACT. NAME AS IMPACT,
MACHINE. NAME AS MACHINE_NAME,
Ifnull((SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.OWNER_ID = USER .ID), ' Unassigned') AS OWNER_NAME,
(SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER .ID) AS SUBMITTER_NAME
FROM HD_TICKET
LEFT JOIN HD_CATEGORY
ON HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS
ON HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN HD_PRIORITY
ON HD_PRIORITY_ID = HD_PRIORITY.ID
LEFT JOIN HD_IMPACT
ON HD_IMPACT_ID = HD_IMPACT.ID
LEFT JOIN MACHINE
ON HD_TICKET.MACHINE_ID = MACHINE.ID
WHERE HD_STATUS.STATE != 'closed'
AND ESCALATED != '0000-00-00 00:00:00'
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
Answered 02/27/2012 by: dchristian
Red Belt

Please log in to comment
0
@dchristian - that's perfect! Thanks so much!
Answered 02/27/2012 by: jenbar
Senior Yellow Belt

Please log in to comment
0
Greetings,
I've recently been playing around with my kbox's OVAL capabilities. There are two canned reports available for OVAL results that give summaries; total count of vulnerabilities per machine or total count of machines per vulnerability. Is there a way to get the results that show up on an individual machine's detail page into a report that would cover all computers? I'm looking for something like:

192.168.16.4 TESTPC002 7132: Untrusted search path ...
192.168.16.5 TESTPC003 6340: The TCP implement...
192.168.16.5 TESTPC003 12514: Microsoft Intern...
192.168.16.6 TESTPC004 12355: The printing...
192.168.16.6 TESTPC004 12700: mshtml.dll in...
Answered 02/27/2012 by: HonestlyImNotBatman
Yellow Belt

  • try this:
    SELECT
    MACHINE.NAME,
    OVAL_STATUS.RESULT,
    OVAL_DEFINITION.SOURCE,
    OVAL_DEFINITION.OVALID,
    OVAL_DEFINITION.DESCRIPTION
    FROM
    OVAL_STATUS,
    MACHINE,
    KBSYS.OVAL_DEFINITION OVAL_DEFINITION
    WHERE
    MACHINE.ID = MACHINE_ID
    AND OVAL_STATUS.OVAL_DEFINITION_ID = OVAL_DEFINITION.ID
    AND OVAL_STATUS.RESULT = 'VULNERABLE';

    For the Break on Columns, enter:
    NAME
Please log in to comment
0
Iknowyourbatman,

Is this what your looking for?
SELECT
M.IP,
M. NAME AS MACHINE_NAME,
S.OVAL_DEFINITION_ID,
D.DESCRIPTION
FROM
OVAL_STATUS S,
KBSYS.OVAL_DEFINITION D,
MACHINE M
WHERE
S.OVAL_DEFINITION_ID = D.ID
AND S.MACHINE_ID = S.MACHINE_ID
AND S.RESULT = 'VULNERABLE'
AND D.CLASS = 'vulnerability'
ORDER BY
M.IP
Answered 02/28/2012 by: dchristian
Red Belt

  • When testing this it returns "Error Running Report....Internal error (3). Please contact KACE Support."
Please log in to comment
0
ORIGINAL: dchristian

Iknowyourbatman,

Is this what your looking for?


Thanks! Those are definitely the droids I'm looking for, but currently the "Report queued" page seems to endlessly refresh. That may just be the kbox struggling to pull the data, so I'm going to schedule the report to run early AM. I'll let you know the results tomorrow.
Answered 02/28/2012 by: HonestlyImNotBatman
Yellow Belt

Please log in to comment
1
Not quite for some reason it is not getting all the results it should. We need all the results with a time opened longer than 10 days (Medium Priority)regardless of the status the ticket is put in. As well as list all High Priority tickets that have past thier escalation regardless thier status.
Answered 02/29/2012 by: dalook
Orange Belt

Please log in to comment
0
Hey folks,

I was wondering if someone could help me... I need a sql report that will show me computers where
the system model is any of these

7649A99
9439b27
9637dg8
2716wyv
Latitude E5500
Latitude E5510
OptiPlex 330
OptiPlex 360
OptiPlex 380
OptiPlex 745
OptiPlex 755


and

the OS name contains XP


and

the label name = SSC

___________________________


Trying to get this info to identify machines that can be upgraded to win7 by location... Then would also want to see the reverse of that to see what machines we need to buy to go to win7
Answered 03/08/2012 by: billcosby
Senior Yellow Belt

Please log in to comment
2
Bill,

What you are trying to get at with the Model names is a regex, and all you need to do is separate the model names with a pipe "|".

Here you go:

select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (((( CS_MODEL rlike '7649A99|9439b27|9637dg8|2716wyv|Latitude E5500|Latitude E5510|OptiPlex 330|OptiPlex 360|OptiPlex 380|OptiPlex 745|OptiPlex 755') AND OS_NAME like '%XP%') AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and LABEL.TYPE != 'hidden' and LABEL.NAME like '%SSC%')) ))
Answered 03/08/2012 by: scottlutz
Orange Senior Belt

Please log in to comment
0
Thank you so much Scott!
Answered 03/08/2012 by: billcosby
Senior Yellow Belt

Please log in to comment
0
Hey Scott - what word would I need to change to get machines that are not those model types...

instead of

CS_MODEL rlike

would it be

CS_MODEL not like

?
Answered 03/08/2012 by: billcosby
Senior Yellow Belt

Please log in to comment
0
Bill,

Since the creation of labels is fundamental to effective K1000 usage, I suggest that you try to play with the Smart Label function to see what gets you the best results.

You will find this at the top right of almost every page in the K1 console.

:)
Answered 03/08/2012 by: scottlutz
Orange Senior Belt

Please log in to comment
0
Thanks Scott!
Answered 03/08/2012 by: billcosby
Senior Yellow Belt

Please log in to comment
0
I've got 3 separate ones that I need help with. I can get close to what I need in the wizard but the wizard is missing some key info that would allow me to actually do them properly or I am just too dense to figure it on my own. I've had no formal training in this and was just thrown at it. It's getting a little frustrating at this point.


List of computers with failed “managed software installations”
List of computers that haven’t rebooted in x number of days (30 and over 60)
List of computers broken down by manufacturer and model with total counts

Counts are a big thing for one of my teammates. He doesn't need long reports, just counts for the most part so is there a way that I can use the wizard to create a report and then edit the report to only display the count and a couple relevant lines? (I know it will vary by report to a degree)
Answered 03/14/2012 by: Gearshock
Senior Yellow Belt

Please log in to comment
2
@Gearshock
Managed install report:
[link]http://itninja.com/question/pdf-printers93[/link]

Last reboot report:
SELECT
M. NAME,
M.LAST_REBOOT
FROM
MACHINE M
WHERE
M.LAST_REBOOT < DATE_SUB(NOW(), INTERVAL 30 DAY)
AND M.LAST_REBOOT > DATE_SUB(NOW(), INTERVAL 60 DAY)
ORDER BY
M.LAST_REBOOT,
M. NAME

Model and manufacture with counts:
SELECT
COALESCE(M.CS_MANUFACTURER,'Total =====') as Manufacturer,
COALESCE(M.CS_MODEL,'=========') AS Model,
COUNT(*) as Counter
FROM
MACHINE M
GROUP BY
M.CS_MANUFACTURER,
M.CS_MODEL WITH ROLLUP
Answered 03/14/2012 by: dchristian
Red Belt

Please log in to comment
0
Thank you very much! That was incredibly helpful. I've been fighting those things for awhile now. I kept trying to figure them out on my own and just wasn't having any luck.

I have one last question, where did you learn SQL and what do you think a good route for a total code/programming/scripting noob with no experience in the field would be? It seems fairly simple and intuitive if I am understanding the gist of the commands right.
Answered 03/14/2012 by: Gearshock
Senior Yellow Belt

Please log in to comment
2
@Gearshock,

No worries we all start somewhere.

W3schools is a great place to start learning.

After that Google and the MySQL website are your 2 best friends.
Answered 03/14/2012 by: dchristian
Red Belt

Please log in to comment
0
Thanks, I am checking it out now.
Answered 03/14/2012 by: Gearshock
Senior Yellow Belt

Please log in to comment
0
Hello,
Please help. We have a k1000 box. Now my boss wants a report that shows at what time a user logged on, the computer name, IP address and what time they logged off at the end of the day.
I have been researching, but I don't find anything simple. I am not a SQL programmer. Anyone has the instructions and code to get a report?
Answered 03/14/2012 by: armando008
Yellow Belt

Please log in to comment
0
Hello,

I am asking to see if someone can create me a report that polls all my windows 7 machines and reports back their patching status. (Like who has SP1 who doesn't). I would like it to exclude Mac OsX

Thank you
Jon
Answered 03/15/2012 by: advkace
Blue Belt

Please log in to comment
0
@armando008,

This is a lil more complicated.

Take a look at this post.

[link]http://itninja.com/question/create-report-to-monitor-user-logoff[/link]
Answered 03/15/2012 by: dchristian
Red Belt

Please log in to comment
-1
@advkace,

If all your looking for is whether SP1 is installed or not, you can look at the machine table.

SELECT
M. NAME,
M.OS_NAME,
M.SERVICE_PACK
FROM
MACHINE M
WHERE
OS_NAME LIKE '%WIN%7%'
ORDER BY
OS_NAME,
SERVICE_PACK,
NAME
Answered 03/15/2012 by: dchristian
Red Belt

Please log in to comment
-1

Hi all,

We are new to Kace and SQL scripting, so my question is probably an easy one for you experts! Our boss would like a report that shows Total number of calls per month listed by category, and the avergae time to close calls again per category! Does anyone have any ideas? 

Also, can anyone recommend a website or book for SQL for beginners?

Thanks a million

Rich

Answered 09/06/2012 by: its_rcc
White Belt

Please log in to comment
0
I'm looking for a SCCM 2012 R2 SQL Query Report that will search for specific folder for the purposes of tracking licenses. The Software creates a generic license.dat file and places it in the folder by the name of the License Key.

C:\ProgramData\Inuvio\XXXX-XXXX-XXXX-XXXX-XXXX-XXXX-XXXX-XXXX\license.dat

Answered 08/21/2014 by: martyrtek
White Belt

Please log in to comment
0
Hello, 
I have a "report wizard" that gives me a report of all closed tickets and who owned the ticket when it was closed

I need to create a report of what user closed a ticket in a report format with totals. Basically I have a helpdesk bucket and all the tickets on the report are closed by the "ticket owner" helpdesk. I need to know who is actually closing the ticket. I am not seeing that field and I don't have access to the database to look for it. Does someone have this report handy or know what field I am needing?
Answered 08/22/2014 by: matcoffe
White Belt

Please log in to comment
0
I am wanting to pull a report that gives me a list of computers that have not rebooted since a Microsoft Update was installed.  MS14-045 was  causing BSoD on multiple computers in our environment after the PC rebooted.  I am not finding a way to do this through the standard report builder, looks like SQL is the only way.  I could really use some assistance.  Basically what im needing is something like this:

List computers that have installed KB2976897 and
Have not rebooted since the installation
Answered 08/27/2014 by: Zach_Lundberg
Senior White Belt

Please log in to comment
0
Good afternoon! I was looking for a tweak to a report that has been posted, http://www.itninja.com/question/need-help-breaking-this-down-to-weeks.  I'd like to get the report to specify a particular category if possible.  It also doesn't have to go all the way back to inception, just the beginning of 2014. 
thank you for any help you can provide!!!
Answered 11/24/2014 by: annleacock
Senior Yellow Belt

Please log in to comment
0
I would like a report that tells me how many machines a user is logged in to (or was the last one to log into). Not sure how to have the sql look at machine names and users and group them like that.
Answered 01/08/2015 by: lmland
Tenth Degree Black Belt

  • Here's the query I ended up with works well for simply number of machines a user is logged into.

    select USER, count(NAME) as Num_Machines
    from MACHINE
    where USER not like '%student'
    group by USER
    having count(NAME) > 3
    order by Num_Machines desc
Please log in to comment
1
I would like to create a computer inventory report based on the last logged in user's location, grouped by location.

Thanks in advance for any assistance with this!
Answered 01/26/2015 by: iMonkey
Yellow Belt

  • This query will show computer location, computer name and last user. It ignores computers not assigned to a location.

    select a2.name location,
    m.name computer, m.user
    from ASSET_ASSOCIATION aa
    left join ASSET a on (aa.asset_id = a.id)
    left join ASSET a2 on (aa.associated_asset_id = a2.id)
    right join MACHINE m on (a.mapped_id = m.id)
    where aa.asset_field_id = 19
    order by location;
    • First off, thanks so much for the reply. Unfortunately, no results were found.
      • I forgot we track location as a custom field on assets so it won't work in anyone's environment. How do you track locations?
    • That's just my challenge. We have location as a field for the user (imported from AD). So I need to see the machines that each user has "last logged onto" and grouped by those users' locations. So the first group displayed would be everyone from location "Annapolis" and the computers they're marked as being last to log into. Then the next group "Austin", so on and so forth. Hope this makes sense.
      • OK.
        So we want to select all the machines and join the user table where the machine username is equal to the user username.
        select m.NAME,
        u.FULL_NAME,
        u.LOCATION
        from MACHINE m
        left join USER u on (m.USER_NAME = u.USER_NAME)
        order by u.LOCATION,
        m.NAME
Please log in to comment
0
I found this report script on a Kace blog to convert Dell Service Tag #'s into Express Service codes:

select NAME,BIOS_SERIAL_NUMBER,@ecode:=CAST(CONV(BIOS_SERIAL_NUMBER, 36, 10) as CHAR) as 'Base-10', CONCAT(LEFT(@ecode, 3), '-', SUBSTRING(@ecode, 4, 3), '-', SUBSTRING(@ecode, 7, 3), '-', SUBSTRING(@ecode, 10, 3)) as 'Express Code'from MACHINEwhere CS_MANUFACTURER like '%Dell%'select 


But when I put it in I get the following error:

mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select LIMIT 0' at line 8] in EXECUTE( "select NAME,BIOS_SERIAL_NUMBER,@ecode:=CAST(CONV(BIOS_SERIAL_NUMBER, 36, 10) as CHAR) as 'Base-10', CONCAT(LEFT(@ecode, 3), '-', SUBSTRING(@ecode, 4, 3), '-', SUBSTRING(@ecode, 7, 3), '-', SUBSTRING(@ecode, 10, 3)) as 'Express Code' from MACHINE where CS_MANUFACTURER like '%Dell%'select LIMIT 0")

I know nothing about SQL so I don't even know where to start to fix it.  Any thoughts?

Thanks!

Answered 01/30/2015 by: j.hough_FNP
Orange Senior Belt

  • You have an extra select at the end, and limiting the returned results to 0 will return 0 results. Here it is modified to return 10 rows.
    select NAME,BIOS_SERIAL_NUMBER,
    @ecode:=CAST(CONV(BIOS_SERIAL_NUMBER, 36, 10) as CHAR) as 'Base-10', CONCAT(LEFT(@ecode, 3), '-', SUBSTRING(@ecode, 4, 3), '-', SUBSTRING(@ecode, 7, 3), '-', SUBSTRING(@ecode, 10, 3)) as 'Express Code'
    from MACHINE
    where CS_MANUFACTURER like '%Dell%'
    limit 10
Please log in to comment
0
I need to report on our KACE Service Desk queue for all calls created in the last 3 months excluding those logged between 0845 and 1700 to create an out of hours report. any advice would be greatly appreciated as wizard cant cope with this detail and my SQL is at best read only.

cheers

Answered 02/11/2015 by: psedgwickJC
Senior White Belt

  • Here I'm selecting all the tickets in the HD_TICKET table. I then use the where clause to filter out anything more than 3 months old and then tickets between 8:45AM and 5PM.
    SELECT t.CREATED 'Time Created',
    t.ID 'Ticket',
    u.FULL_NAME 'Submitter',
    t.TITLE
    FROM HD_TICKET t
    LEFT JOIN USER u on (t.SUBMITTER_ID = u.ID)
    WHERE DATE(t.CREATED) >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
    AND ((HOUR(t.CREATED) < '9' AND MINUTE(t.CREATED) < '45') OR HOUR(t.CREATED) > '17')
    ORDER BY t.CREATED
Please log in to comment
0
Looking for some help finalizing a report. Basically it counts by model and  breaks by the 2nd octet of IP (this identifies separate physical locations) so that way the user can see a break down of Machines by Campus and count of each model. 
Break on Columns:
OCT2

SELECT 
SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',2),'.',-1) AS OCT2, CS_MODEL, COUNT(MACHINE.ID) AS QTY, SUM(QTY) AS TOTAL
FROM MACHINE
GROUP BY OCT2,CS_MODEL 
order by OCT2 asc


Last thing I need and do not know how to do is get a SUM of all counts in the header of each break example output, with desired SUM of COUNT in bold
MODEL                                        QTY
______________
47 Model at 101                               *45*
    Model 1                                        5
    Model 2                                        30
    .....    
    Model 47                                       10
__________
14 Model at 102                                *21*
    Model 1                                        3
    Model 2                                        7
    ......
    Model 14                                       11

Bonus question that I don't expect any help with and have been fixing after the report runs is the 2nd octet relates to a Campus ID. so is there way to change the number to a three letter identifier from a list.
ie replace the number that is OCT2 in the break with the 3 letter identifier in a list:
1; KHS
2; FRH
4; CHS
5; TCH
39; NDL
41; KMS
Answered 04/02/2015 by: matiasm
Orange Senior Belt

Please log in to comment
0
Hello,

I am looking for a simple report that will allow me to view Dell machines that are past 3 years from their build date.  Can anyone assist me on doing this?
Answered 04/08/2015 by: JCRAWFORD
White Belt

  • From the SQL report wizard set the Topic to "Dell Warranty" you can add a sub topic of Device if you want more information.

    From the Dell Warranty Fields you can select ship date and filter that as < whatever date you want.

    If you are familiar with SQL see this post for more in depth idea on report the dell device information
    http://www.itninja.com/blog/view/report-showing-dell-service-info-found-on-inventory-page

    If you can't get the report you're looking for reply back and I'll try and whip something up for you to copy and paste.
Please log in to comment
0
Hello,

I need to generate a report with the following and was hoping someone could assist me:

1. User Full Name
2. BIOS Serial # (or Dell Service Tag #)
3. System Manufacturer
4. System Model
5. Computer Description
6. Chassis Type
7. Dell Warranty Expired Date (only Client Gold Support\ProSupport)

Thanks in advance. 
Answered 04/09/2015 by: AEM
White Belt

Please log in to comment
0
http://www.itninja.com/question/k1000-service-desk-report-by-ldap-user-group

I'm looking for help on a report on a Service Desk report for closed tickets sorted by LDAP label.
Answered 05/07/2015 by: itsamurai
Senior White Belt

Please log in to comment
0
I have a set of Assets under the Asset Type "Networking Equipment". I have a label created and applied to some assets in there. The label is specifying what assets in the Networking Equipment are critical for the day to day. Is there a way to generate a report from that Asset Type, only including assets with that label, and excluding all other assets of that asset type?
Answered 06/03/2015 by: apowell
White Belt

Please log in to comment
0
HI, Looking for a break down of calls logged in a week where they are sorted in order of the time of day created in hh:mm:ss but not in order of date. I want to see trends of when the busiest time of day is.
Thanks
Answered 06/22/2015 by: psedgwickJC
Senior White Belt

Please log in to comment
0
We are migrating to the KACE Service Desk in a couple weeks, so i have a couple reports that i need to have ready prior to then.  I not having any luck with the report wizard for any Service Desk related reports and i familiar enough with SQL to create a custom report. 

I feel like the type of report im looking for is basic, so hopefully they're simple to create for anyone that knows anything about SQL. 

The main report that i need to produce is a report of tickets that have been opened within previous week and the number of tickets that have been closed in the previous week.  I really don't need any other information in this report other than Opened Tickets with the total and Closed Tickets with the total.  Getting these totals on a single report would be ideal, but if they need to be tool i can't be picky. 

The next one i need is a report that shows by technician  how many tickets they entered (logged) and how many they closed.  Again this is a weekly report, so the last 7 days would be great.  Again all i really need is a number per broken down by each technician 

The final is similar to the last report but instead of open/closed tickets per technician, i'm looking for the total number of comments the technician posted for the week. 

These reports are important to us as they show us breaks out what our technicians are doing each week while comparing their performance to each other. 

Does anyone think they can help me with these reports or point me to someone that can. 

Thanks 

mihenry
Answered 07/18/2015 by: mihenry
Senior White Belt

Please log in to comment
0
Another request for a report that i have asked for is to report on the number of tickets that haven't updated in X number of days that includes a listing those tickets with a link to go directly to each ticket as needed. 

This is a built in report in our current system, all we have to do is select the number of days and run.
Answered 07/27/2015 by: mihenry
Senior White Belt

Please log in to comment
0
I require my technicians to update their owned tickets on a weekly basis.  I thought I had it figured out by using the modified date but if the technician checks the "owners only" box for their comment, it doesn't change the modified date on the ticket itself.

Is there a way I can create a report that shows me all tickets that have not had a comment inserted in the past 9 days even if the technician checks the "owners only" box?
Answered 08/31/2015 by: jstraughn
White Belt

Please log in to comment
0
New to KBOX and I know absolutely nothing about SQL unfortunately.

I have a need to display the combined time of my co-workers input time on their line, the "Worked Time" on a ticket, as well as make the code only go for 1 "Owner" at a time. It would mean several copies but I cannot seem to find out how to make the Combined total of the tickets come up for hours nor separate specific users.

The Wizard doesn't seem to do it for me, and I have tried to modify the standard "Work Report for Last 30 Days", with only the luck of changing the interval. heres my code below, any help would be appreciated!

select W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET, U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE,
format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
  and isnull(W.VOIDED_BY)
  and W.USER_ID = U.ID
  and W.STOP > DATE_SUB(NOW(), INTERVAL 1 DAY)
order by U.FULL_NAME, TICKET, W.STOP, HOURS_WORKED


The combined time of Hours Worked would be display don the header for that section, in this case across from (empty).

Thank you,m
~Avarist
Answered 09/30/2015 by: Avarist
White Belt

  • Hello,

    could please any one help me to create report as time to create, time to resolved, technician ID, time to respond (duration) considered Business hours and holidays.

    time to create = Time the ticket was created
    time to resolved = Time the ticket was resolved (in progress to resolved time )
    technician ID = ticket owner
    time to respond = How long was taken to resolve the ticket, this should be considered only business hours and holidays. e.g ( business hours Sunday - Thursday 8.30-17.30, time to create Sunday 16.30, time to resolved Monday 9.30, time to respond should be 0day, 2 hours, 00 min.)

    Best Regards
    Sasi
Please log in to comment
0
Sorry, added this originally as a comment on the original post. I think it belongs here, instead. 


Hi folks, I'm trying to create a report that will show me all open tickets for a Support Rep AND all tickets closed by that Rep over the previous week/month/year. I can use the Wizard to create two separate reports that will give me this information but I'd really love to have it all contained within one report. Is this possible?
Answered 10/13/2015 by: Sabious
White Belt

Please log in to comment
0
Patching Report Request.

I am in need of a report that will list patches installed on a computer by date the patches were installed. in essence i need a report that will tell me when each patch was installed on a specific computer.

Thank you in advance for any help
Answered 10/26/2015 by: comjam
White Belt

  • this question is 5 years old you really should post a new question on this
Please log in to comment
0
Hello,

could please any one help me to create report as time to create, time to resolved, technician ID, time to respond (duration) considered Business hours and holidays.

time to create = Time the ticket was created
time to resolved = Time the ticket was resolved (in progress to resolved time )
technician ID = ticket owner
time to respond = How long was taken to resolve the ticket, this should be considered only business hours and holidays. e.g ( business hours Sunday - Thursday 8.30-17.30, time to create Sunday 16.30, time to resolved Monday 9.30, time to respond should be 0day, 2 hours, 00 min.)

Best Regards
Sasi


Answered 01/05/2016 by: sasikaran
White Belt

  • Sasi, This thread has gotten too big so I suggest posting a new question for report requests. Regarding you issue: on which column or step are you having trouble if you try with the Report Wizard?
Please log in to comment
0

I am looking for a report that will list machine names that do no have older Windows Updates installed. I want it to list all systems that need patches from Jan 1 to Dec 31 of 2015 or 2014 or 2013, etc. I found a machine in our environment that is missing some updates from 2014 and wanted to identify any additional machines that could be in the same situation.


Thanks,

Mike

Answered 05/25/2016 by: M&M
White Belt

  • Are you using KACE patching to manage Windows OS updates?
    • Yes, we are using KACE to patch all Windows OS
Please log in to comment
0
I'm looking for a way to include non-closed tickets from all previous months in my monthly reports. 

I have been using this to create monthly reports that show the ticket status, but this does not include previous month's tickets that haven't been closed. 

For Example: 

if April has 10 closed tickets and 5 un-closed (includes new, opened, re-opened, waiting on customer, waiting on 3rd party) tickets.

May will have 10 closed reports, 2 open reports, and 5 unclosed tickets from the previous month. 

How could I go about doing this?
Answered 06/28/2016 by: jcadenhead
White Belt

  • Can you post the query you're currently using? You also might consider moving this to a new question (rather than posting in this very old thread)
    • Here is the query we are currently working with. We used Navicat MySQL to build and test the query, however we are really struggling with its report builder. We currently are looking for another MySQL reporting tool so we can generate an actual report.

      select LA.NAME AS GROUPS,M.NAME AS ClientName,L.NAME AS PatchLabel,PN.NAME,PMS.STATUS as PatchStatus

      FROM PATCHLINK_PATCH_STATUS PS

      JOIN PATCHLINK_PATCH_LABEL_JT PL ON PS.PATCHUID=PL.PATCHUID

      JOIN LABEL L ON L.ID=PL.LABEL_ID

      JOIN PATCHLINK_MACHINE_STATUS PMS ON PMS.PATCHUID=PS.PATCHUID

      JOIN MACHINE M ON M.ID=PMS.MACHINE_ID

      JOIN PATCHLINK_MACHINE_APPLICABLE_PACKAGE PMAP ON PMAP.PATCHUID=PMS.PATCHUID

      JOIN KBSYS.PATCHLINK_PACKAGE PN ON PN.PATCHUID=PMAP.PATCHUID

      JOIN MACHINE_LABEL_JT ML ON ML.MACHINE_ID=M.ID

      JOIN LABEL LA ON LA.ID=ML.LABEL_ID

      WHERE PMS.STATUS='NOTPATCHED'

      AND ML.LABEL_ID IN ('59','73','60','74')
      • FYI: I'm using the program "Toad" for queries, and have also used MySQL Workbench. Both are free and work with KACE.
    • Sorry, I didn't notice your hyperlink to the ITNinja blog post
  • So does the "not closed" count refer to the number of tickets that were opened but not closed during that month? Or do you mean the total number of non-closed tickets at the end of that month? Are you using more than one queue?
Please log in to comment
Answer this question or Comment on this question for clarity