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?

Thanks

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

The first one is easy.

 select 
    OS_NAME,
    COUNT(OS_NAME) as OSCOUNT,
    (count(OS_NAME) / _total) *100 as ospercent
from
    MACHINE,
    (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
Fourth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity