Hi all,

I am in need of some help with coming up with custom sql reports.

The first one I need is an OS split which which gives me the total number of machines for each OS and the percentage.

This is what I have so far (It just gives me the total numbers)

select OS_NAME, COUNT(OS_NAME) as OSCOUNT from MACHINE group by OS_NAME  order by OSCOUNT desc


The next one I need is to be able to do the same as above but with all versions of Microsoft Office. Any ideas?

And the last one, I would like to be able to see the total number of how many computers have been patched this week and how many haven't?

Also, can anyone recommend any guides which will help me with this?


0 Comments   [ - ] Hide Comments


Please log in to comment

Answer this question or Comment on this question for clarity



The first one is easy.

    (count(OS_NAME) / _total) *100 as ospercent
    (select count(*) as _total from MACHINE) as myTotal
group by OS_NAME
order by OSCOUNT desc

For the second, take a look at the delivered Software reports.  Unfortunately, we can't just look for "Microsoft Office" since it reports in with "Microsoft Word", "Microsoft Excel", ....  So we have to check for systems with those applications installed to deduce which ones have Office.  "Software Title - Computer List (MS Only)" would be a good place to start.

Since we are not doing patching with KACE (at least not yet), I can only suggest looking at the delivered Patching reports and the related patching tables.  I think what you are looking to filter by would be the PATCHLINK_MACHINE_STATUS.DEPLOY_STATUS_DT field.


Answered 07/18/2013 by: grayematter
Fifth Degree Black Belt

Please log in to comment
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja