ITNinja

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

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

@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

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

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


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

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 would like to create a computer inventory report based on the last logged in user's location, grouped by location.

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.

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

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.

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

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?

Is it possible to run a monthly report of user logon/logoff times along with the pc associated?

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.

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!

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.

Jenbar,

Are you talking about machine label or software label?

If your looking for machine label, this is possible thru the wizard.

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());

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.

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

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

@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;

dchristian,
Computer label.
Thanks

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

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

Davidr28,

Take a look at the "Computer Listing by Memory" report.

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

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.

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

@dchristian - that's perfect! Thanks so much!

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

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

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.

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

Thank you so much Scott!

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

?

Hello,

Is there a way yet to create a report based on ticket creator? I need to create a report of our after hours support and getting a report based on a short list of ticket creators would be great.

If not,

I am looking for a report to list any tickets submitted between 9pm and 6am or if it works better for the query all tickets excluding those created between 6a and 9p. Any assistance with this would be appreciated.

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.

:)

Thanks Scott!

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)

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. 

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.

Thanks, I am checking it out now.

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?

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

@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]

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?

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:

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

Thanks!

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.

New to KBOX and I know absolutely nothing about SQL unfortunately.

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.

Does anyone have a report that will pull up a user and show all assets that said user has?

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

I'm looking for a way to include non-closed tickets from all previous months in my monthly reports. 

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

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. 

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.

Hello, 

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.

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

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?

Hello,

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.

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!

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

Andy,

This is exactly what I was looking for! Thanks so much.

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!

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


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

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.

Hello,

Andy,

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

-E

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.

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

Fantastic!! That was exactly what I needed! Thank you very much!

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


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


You're good!
Thanks again for your help.

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

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


Thanks man Now I just need to get my butt in gear and learn how to do these query's

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

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?

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?

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!

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

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

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]

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

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

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

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

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.

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

Thanks dchristian that will do the trick!

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

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.

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.

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

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

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

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.

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

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

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?

Palmjx,

Take a look at the KBSYS.KONDUCTOR_TASK table.

That should get you going in the right direction.

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


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?

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.

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

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

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.

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.

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!

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.

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?

You are correct.

They user names will need to be there (imported from ldap).

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!

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

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.

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

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.

@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

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. 

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