Help by creating Report, Please

johnny07 · 04-15-2009, 04:48 AM

Hello, i hope someone can help me to create a report.
building sql queries is not my thing.

all i want is a report that shows computers without "sophos anti-virus" showing up in "add remove programs"

I think this not to difficult for people with knowledge of sql syntax.

Thanks in advance,

Johnny

JeffreyJRiggs · 04-15-2009, 08:58 AM

Quote:

Originally Posted by johnny07

Hello, i hope someone can help me to create a report.
building sql queries is not my thing.

all i want is a report that shows computers without "sophos anti-virus" showing up in "add remove programs"

I think this not to difficult for people with knowledge of sql syntax.

Thanks in advance,

Johnny

well here are some samples that you can play around with...

this will help you look for an app....
like adobe

Code:

-- code to look for a type of app
Select t0.Name, t0.version,Count(t0.name)
From [inv_aex_os_add_remove_programs] t0 
--Edit app looking for. keep it short for more apps
Where t0.[name] like'soph%'
Groupby t0.Name, t0.version

so just put a not like for sophos...
just test the one above to verify that it sees sophos first..

Code:

-- code to look for a type of app
Select t0.Name, t0.version,Count(t0.name)
From [inv_aex_os_add_remove_programs] t0 
--Edit app looking for. keep it short for more apps
Where t0.[name] notlike'sopho%'
Groupby t0.Name, t0.version

johnny07 · 04-16-2009, 02:39 AM

Thank you Jeffrey for your support.
but your code returns all rows that do not contain sopho%, thats not what i want.
i nee to know wich computers (Names) do not have sophos installed.
Thanks anyway.

i just found the solution in another topic on this board.

search for "computers without specific application"

and the code is

select vc.name from vcomputer vc WHERE GUID NOT IN (select _resourceGuid from inv_aex_os_add_remove_programs t1 where t1.name LIKE '%Sophos%') AND vc.ismanaged=1