Registrations to Open-AudIT forums are now closed. To ask any new questions please visit Opmantek Community Questions.

Open-AudIT

What's on your network?
It is currently Sat Oct 19, 2019 11:13 pm

All times are UTC + 10 hours




Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
PostPosted: Sat Nov 06, 2010 12:31 am 
Offline
Newbie

Joined: Fri Nov 05, 2010 3:27 am
Posts: 4
Hi all,

Got an interesting situation - i have 750 workstations in the inventory but i would like to do a search on what is NOT installed - ie are there any without the antivirus and device control pruducts we use. I may be missing something obvious (i hope am!), i can't see how to do it.

Another thing - DropBox is not showing up in the software inventory, even though it is installed and is in Add/Remove programs. How do i get this to report in...?

Any assistance would be most welcome - thanks in advance!


Top
 Profile  
Reply with quote  
PostPosted: Mon Nov 22, 2010 9:43 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1964
Location: Brisbane, Australia
You could try setting up a report with a join such as this (this SQL taken from a report in OAv2) -
Code:
select system.system_id, system.hostname, sys_sw_software.software_name, sys_sw_software.software_version from system left join sys_sw_software on (system.system_id = sys_sw_software.system_id and system.timestamp = sys_sw_software.timestamp and (software_name like '%virus%' or software_name like '%endpoint%')) join oa_group_sys on (system.system_id = oa_group_sys.system_id and oa_group_sys.group_id = ?) group by hostname order by hostname;

Note the left join...

As for DropBox...
It should list most programs in the Registry at -
My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall
If it appears in there, it should show up in Open-AudIT.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Fri Jan 14, 2011 3:44 am 
Offline
Newbie

Joined: Fri Nov 05, 2010 3:27 am
Posts: 4
I have to say that the above didn't really help that much - it refers to tables that are not present (oa_group_sys for example).

I needed to get up to speed on some sql language and mapped out what how I could get the end result in simple language

I wanted List of PCs without software X not installed

So I reasoned that (list of PCs) minus (list of PCs with software X installed) = (list of PCs without software X)

I believe the following statement does the job:

Code:
select system_name from system where system_uuid not in (select software_uuid from software where software_name like '%Sophos%') order by 1


Is this correct or have I overlooked something or got the wrong end of the stick...?


Top
 Profile  
Reply with quote  
PostPosted: Fri Jan 14, 2011 9:47 pm 
Offline
Newbie

Joined: Fri Nov 05, 2010 3:27 am
Posts: 4
will the left join section (provided i like the right tables) provide a better validation/link?

I used just this statement to get the desired results

select system_name from system where system_uuid not in (select software_uuid from software where software_name like '%Sophos%') order by 1;

(list of all pc's) - (list of pc's with sophos) = (list of pc's without sopohs)


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group