Open-AudIT

First I want to say thank you to everyone who have written something to help someone who is as unfamiliar as I am with this software system. Through this site I've been able to create a system in my office to track and monitor my equipment more efficiently than walking to every computer and looking at it. Now the question.

Is there a tutorial on how to write custom queries for Open-AudIT? My goal for my query is to generate a list of all my inventory organized by machine model and including the current user and hostname fields. The purpose of this query is to generate a list in which I can go through and determine which machines are due for replacement (as we seem to rotate to a new model every cycle, this would work for the short term). As I would have to run this query almost quartly, I would like to find a way to add it into my Open-AudIT query tab.

Secondly I was also wondering how hard would it be to write a similar query with current user and hostname but instead of organized by machine model, organized by first audited date, this would be useful as I'd like to standardize the systems we deploy and if we deploy the same model 2 years in a row, it would defeat the end goal of the first script.

From what I understand of the Open-AudIT system, the data is stored in a set of tables within the database. Is there a mapping of the relationship of these tables? I'm no expert of SQL, but I've been known to write a query or two. Any and all help would be greatly appreciated.

Thanks.

You may not need to write any SQL you might be able to use the All Systems More Info report, the magnifier (which lets you choose values for the fields) the sort buttons, and the green down arrow to show the full list. Then export to CSV, and remove what you dont want in your Spreadsheet program.

However, that may not fully fit your requirements. If not, then its time to learn some PHP.

Copy the All System More info php file (its called list_viewdef_all_systems_more.php 'cos its a list view definition, of all system, more info.... nothing if not logical are we ) and call it list_viewdef_myreport1.php for example.

The PHP file is a text file as you may already be aware, so edit list_viewdef_myreport1.php

This is what you will start with

[code]
<?php

$query_array=array("headline"=>__("List all Systems"),
"sql"=>"SELECT * FROM `system`, `processor`, `hard_drive` WHERE system_uuid = hard_drive_uuid AND system_uuid = processor_uuid AND hard_drive_uuid = processor_uuid AND system_timestamp = processor_timestamp AND system_timestamp = hard_drive_timestamp AND processor_device_id = 'CPU0' AND hard_drive_index = 0",
"sort"=>"system_name",
"dir"=>"ASC",
"get"=>array("file"=>"system.php",
"title"=>__("Go to System"),
"var"=>array("pc"=>"%system_uuid",
"view"=>"summary",
),
),
"fields"=>array("10"=>array("name"=>"system_uuid",
"head"=>__("UUID"),
"show"=>"n",
),
"30"=>array("name"=>"system_name",
"head"=>__("Hostname"),
"show"=>"y",
"link"=>"y",
),
"40"=>array("name"=>"net_user_name",
"head"=>__("Username"),
"show"=>"y",
"link"=>"y",
),
"50"=>array("name"=>"system_vendor",
"head"=>__("Vendor"),
"show"=>"y",
),
"120"=>array("name"=>"system_model",
"head"=>__("Model"),
"show"=>"y",
),
"70"=>array("name"=>"system_description",
"head"=>__("Description"),
"show"=>$show_description,
),
"80"=>array("name"=>"system_id_number",
"head"=>__("Serial #"),
"show"=>"y",
),
"90"=>array("name"=>"processor_name",
"head"=>__("CPU"),
"show"=>"y",
),
"100"=>array("name"=>"system_memory",
"head"=>__("RAM"),
"show"=>"y",
),
"110"=>array("name"=>"hard_drive_size",
"head"=>__("First Disk Space"),
"show"=>"y",
),

),
);
?>
[/code]

As you can see the first few lines include the title (the headline), folowed by a SQL query, then a bunch of numbered sections which are an array of the fields to be viewed the order of these is determined by the number at the start of each array element. You can only display fields which are selected by the SQL query, so the SQL is obviously the key.

If you need to know what fields are available to choose from, then I suggest that you install PHPMyAdmin. You may already have this installed, if you installed XAMPP.

If you want to use OpenOffice to view the tables and field relationships, take a look at the flash tutorial in the help menu.

Once you are happy with your selections, you can view the results by loading the PHP page with something like

https://{yourserver}/openaudit/list.php?view=all_systems_myreport1

Note that the name is shortened, and doesn't include the list_viewdef or the .php

You can then edit include_menu_array.php (which has a similar stucture) and add in your new report.

If you need more details, just nudge me sometime next week and I will flesh things out a bit further. Have fun.

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]

Thanks so much for your reply, I'll start work on this right after my meetings this morning.