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 requestFor more information, visit our FAQ's.

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.

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

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.

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.

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:

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?

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.

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.

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.

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.

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%'

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".

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.

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.

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?

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

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

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.

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

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?

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!

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

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

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.

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 ...

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..

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?

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.

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?

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.

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?

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)

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:

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.

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.

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?

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.

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

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

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?

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!

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?

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.

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.

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.

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.

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?

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.

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?

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

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:

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.

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.

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)

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.

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?

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?

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.

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?

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 andHave not rebooted since the installation

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

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?

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.

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

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:

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?

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.

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.

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.

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?

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!

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.)

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?

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.

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 createdtime to resolved = Time the ticket was resolved (in progress to resolved time ) technician ID = ticket ownertime 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.)

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.

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

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?