Important Notice: On February 29th, this community was put into read-only mode. All existing posts will remain but customers are unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

Trying to create a report and need to know if something is possible.

Kyle Rockwell

December 12, 2012 01:03

I want to create a report that can run and tell me on a single line what version of three different software are on a computer. I can run the report and get it on different lines but I would like one line per computer. So what it would look like would be one column with type of Antivirus (Symantec / Forefront ) one column with office version( 2007 / 2010 ) and then one with if Lync is installed. Please advise if this is possible. Also is it possible to get hard drive size and applications in one report?

Comments

1 comment

Adam Ruth
December 12, 2012 01:27
Kyle,

Good question. This can be done with an SQL report, but it can get a bit complicated. There are a couple of ways to do it, here's an example that uses a sub-query for each application column.
```
select
 Computers.Name as "Computer Name",
 Computers.Description,
 (select Version from Applications where ComputerId = Computers.ComputerId and Name = 'Dropbox' collate nocase) as "Dropbox Version",
 (select Version from Applications where ComputerId = Computers.ComputerId and Name = 'iTunes' collate nocase) as "iTunes Version"
from Computers
where <ComputerFilter>
```
You would need to add a new column for each application and set the filter and column name.

Hard drive size can be done similarly, though it will depend a bit on exactly what you want to see. You could add a column like this:
```
(select sum(Size)/1024/1024/1024 from DiskDrives where ComputerId = Computers.ComputerId) as "Disk Size"
```
This will give you the total size of all disks in GB.
0