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 Thu Mar 28, 2024 11:55 pm

All times are UTC + 10 hours




Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

It is a good suggestion?
- Zero (No! It's bullshit!) 0%  0%  [ 0 ]
- 1 0%  0%  [ 0 ]
- 2 0%  0%  [ 0 ]
- 3 0%  0%  [ 0 ]
- 4 13%  13%  [ 1 ]
- Five (Yes! It's great!) 88%  88%  [ 7 ]
Total votes : 8
Author Message
PostPosted: Fri Aug 25, 2006 7:01 am 
Offline
Newbie
User avatar

Joined: Wed Aug 16, 2006 9:06 am
Posts: 45
Location: Rome - Italy - Europe (GMT +2)
I have already developped an application that does the hardware and software inventory of my managed assets.
For software and service table i suggest to you to use another kind of table schema to preserve the db integrity rules and to execute query in a faster way.

The informations about a service/software are more or less always the same, same name, description, version, location, and so on (many computers have exactly the same services/softwares) ... same services/softwares on many machines and this means that i have to repeat for all of them the same informations in every record. It is not very good table design. Right?

From OA Software table:
[quote]160 CNT0029.domain.net Microsoft Office Professional Edition 2003 11.0.7969.0 MsiExec.exe /I{90110410-6000-11D3-8CFE-0150048383C... 20060713 Microsoft Corporation \\domain.net \Share\ALE\Deployment\APPL\Office2K3Pr... http://www.microsoft.com/support 168 20060824205126 20060824200825

365 A4E9DF80-79BD-11D8-8961-B932DE2EA168 Microsoft Office Professional Edition 2003 11.0.7969.0 MsiExec.exe /I{90110410-6000-11D3-8CFE-0150048383C... 20060726 Microsoft Corporation E:\MSOCache\All Users\90000410-6000-11D3-8CFE-0150... http://www.microsoft.com/support 184 20060824205303 20060824201007

554 DTS0041.domain.net Microsoft Office Professional Edition 2003 11.0.7969.0 MsiExec.exe /I{90110410-6000-11D3-8CFE-0150048383C... 20060712 Microsoft Corporation \\domain.net\Share\ALE\Deployment\APPL\Office2K3Pr... http://www.microsoft.com/support 169 20060824205505 20060824201204

821 CNT0017.domain.net Microsoft Office Professional Edition 2003 11.0.7969.0 MsiExec.exe /I{90110410-6000-11D3-8CFE-0150048383C... 20060713 Microsoft Corporation \\domain.net\Share\ALE\Deployment\APPL\Office2K3Pr... http://www.microsoft.com/support 168 20060824205126 20060824201628

1093 A4E9DF80-79BD-11D8-8961-B932DE2EA182 Microsoft Office Professional Edition 2003 11.0.7969.0 MsiExec.exe /I{90110410-6000-11D3-8CFE-0150048383C... 20060726 Microsoft Corporation E:\MSOCache\All Users\90000410-6000-11D3-8CFE-0150... http://www.microsoft.com/support 184 20060824205303 20060824201810


More ore less always the same informations repeated many times.

The schema i created for my application is a little more optimized and is incredibly fast. These are the tables, please use your fantasy to make all the changes necessary to try to fit them to open-audit.

Tables:
[quote]CREATE TABLE [color=red]`_applications`[/color] (
`id_application` int(11) NOT NULL auto_increment,
`first_date` datetime NOT NULL default '0000-00-00 00:00:00',
`application` varchar(64) default NULL,
`version` varchar(32) default NULL,
`publisher` varchar(64) default NULL,
`uninstallstring` varchar(254) default NULL,
PRIMARY KEY (`id_application`)
) TYPE=MyISAM AUTO_INCREMENT=1;

CREATE TABLE [color=red]`_computer2application`[/color] (
`id_link` int(11) NOT NULL auto_increment,
`id_computer` int(11) NOT NULL default '0',
`id_application` int(11) NOT NULL default '0',
`first_date` datetime default NULL,
`last_date` datetime default NULL,
PRIMARY KEY (`id_link`),
KEY `idx_id_application` (`id_application`),
KEY `idx_id_computer` (`id_computer`),
KEY `idx_ids_comp_appl` (`id_computer`,`id_application`)
) TYPE=MyISAM AUTO_INCREMENT=1;

CREATE TABLE [color=red]`_computers`[/color] (
`id_computer` int(11) NOT NULL auto_increment,
`first_date` datetime NOT NULL default '0000-00-00 00:00:00',
`last_date` datetime NOT NULL default '0000-00-00 00:00:00',
`clienthwid` varchar(48) default NULL,
`computername` varchar(15) NOT NULL default '',
`computerdomain` varchar(16) default NULL,
`mac` varchar(12) NOT NULL default '',
`cpu` varchar(48) default NULL,
`mhz` smallint(4) unsigned NOT NULL default '0',
`memory` smallint(4) unsigned NOT NULL default '0',
`so` varchar(25) default NULL,
`csd` varchar(14) default NULL,
`build` varchar(4) default NULL,
PRIMARY KEY (`id_computer`),
KEY `idx_computername` (`computername`),
KEY `idx_clienthwid` (`clienthwid`),
KEY `idx_computername_clienthwid` (`computername`,`clienthwid`),
KEY `idx_mac` (`mac`(6))
) TYPE=MyISAM AUTO_INCREMENT=1;

The relations:
[quote]_computers.id_computer -->_computer2application.id_computer
_computer2application.id_application --> _applications.id_application

It means that _computer2application is a "bridge" table beetween computers and applications installed on them.
This is the query i use to extract all the applications installed on a machine:
[quote]SELECT application, version, publisher
FROM _computers
LEFT JOIN _computer2application ON _computers.id_computer = _computer2application.id_computer
LEFT JOIN _applications ON _computer2application.id_application = _applications.id_application
WHERE _computers.computername = 'MyComputer'
ORDER BY _applications.application ASC

Fileds:
_applications.first_date : the first date when THIS application was seem in the assets inventory
_computer2application.first_date : the first time the relation beetween a specific computer and a specific application was discovered
_computer2application.last_date : as above but the last time (last inventory date)
_computers.fist_date and _computers.last_date : same as above but the fist and the last time for the specific computer inventory

You can add another table to record the status of a service on a specific machine (running or not, enabled or disabled and so on).

To speak about numbers, i have 784 computers with 2,703 applications and 61,278 records inside the table _computer2application and for the query above result is 142 rows, query took 0.0038 sec (pent4 1.8Gh, 2 gb ram, mysql 5.0)

1st Note: Index are very important for the speed of this kind of table design.
2ns Note: If there is something different beetween an application (/service) and another (for example only the installation point), i consider that another application. I know, some data in that way are repeated but only twice (in that example), not once for every computer that has installed that application.

Could be a good suggestion?

Thanks for your attention.

[color=blue]L.[/color]

Last edited by lorenz on Fri Aug 25, 2006 7:21 am, edited 2 times in total.

Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Fri Aug 25, 2006 7:13 am 
Yep, that sounds like a very good suggestion. It would take a little time to do, but eventually!


Top
  
Reply with quote  
 Post subject:
PostPosted: Fri Aug 25, 2006 7:20 am 
Offline
Newbie
User avatar

Joined: Wed Aug 16, 2006 9:06 am
Posts: 45
Location: Rome - Italy - Europe (GMT +2)
Yes, i know! It's an heavy modification of db structure and code, but the positive impact could be heavy as well.

[color=blue]L.[/color]


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Wed Aug 30, 2006 5:58 pm 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
Faster and smaller, always good (when you are talking about databases of course. :roll: what were you thinking of?)


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Aug 31, 2006 7:34 am 
Offline
Newbie
User avatar

Joined: Wed Aug 16, 2006 9:06 am
Posts: 45
Location: Rome - Italy - Europe (GMT +2)
I think it could be necessary a db redisign because it's no efficent and when there are many machines the queries are very slow.
Look at the beginning of the thread what's my idea :idea: .

_________________
[color=blue]Lorenz[/color]


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.  [ 5 posts ] 

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 2 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:  
cron
Powered by phpBB® Forum Software © phpBB Group