I have been using a very cool utility for a while now that I just realized I failed to plug, it’s called Open Audit. What it does is basically is run a wmi scan on your network (it will do nmap too) and submit that data back to a mysql database through a web service. Then there’s a fancy UI that you can do searches, queries, etc.. You can get cool stuff like hardware type, see how many sticks of ram, or an IP address, or a driver, or a hotfix.
The application is written in PHP and mysql and I run the application on a windows host (it can run on just about anything) and use XAMPP, which is a pretty cool. It’s a single download that contains apache, php and mysql, all preconfigured and ready to rock and roll. All you need is some minor configuration.
While it’s pretty cool as it comes, the real power is that you can modify it all you want. So what you can do is take one of the default “views” such as list_viewdef_all_servers.php. If you open it, there’s a sql query inside that looks like this:
SELECT * FROM `system` WHERE (system_os_name LIKE ‘%Server%’)
Then you can copy/rename the page and modify that query however you like. Here’s a modification that I made so I could find servers on our internal (but shouldn’t be) net.
SELECT * FROM `system` WHERE (system_os_name LIKE ‘%Server%’) AND net_ip_address LIKE ‘192.100%’
After doing this for every custom query that I wanted, I realized that all of this data is in a mysql database. This allows you to run queries straight up, and since you used XAMPP, well you can then log in there, choose your database, find the query window, and paste your sql query right there and get the results on the spot. It’s pretty cool.
This now leads me to a gotcha I encountered today while doing my own query this way. What I had was a query that looked like this:
SELECT * FROM `system` WHERE (system_os_name LIKE ‘%Server%’) AND net_ip_address LIKE ‘192.100%’
And I was trying to find a subnet that was 10.1.13, so I made the query like this:
SELECT * FROM `system` WHERE (system_os_name LIKE ‘%Server%’) AND net_ip_address LIKE ‘10.1.13.%’
This kept returning zero results, which I knew was not the case. After looking at the data, I saw that the IP addresses were stored like this:
010.001.013.xxx
As a result I had to change my query to look like this instead:
SELECT * FROM `system` WHERE (system_os_name LIKE ‘%Server%’) AND net_ip_address LIKE ‘010.001.013.%’