K1000 Reporting - SQL querys help
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
Answers (1)
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.
