Friday, March 20, 2009

Lansweeper – Inventory Tool Extraordinaire

 Lansweeper is a free and very cool tool for network inventory purposes.  There is a Premium Version that provides some extras that are well worth the investment.  I’ve been using it for a few image months now and it’s become one of my favorite tools.  What I like about it is that it’s very extendable and it’s pretty easy to create your own reports. I’ll let you check out the basics for yourself on their demo site, but I thought I’d walk you through how to extend the reporting functionality.  To do this you need lsbuilder, which is part of the Premium Version.  However, lsbuilder is just an easy way to create your own SQL queries so you could probably do that with the standard SQL tools. I wanted a report of Computer Manufacturer, Computer Name, Computer Model and the last logged in user.  Unfortunately, this report didn’t exist in the base tool, so I created it on my own.  First open up lsbuilder:

image

As you can see I’ve got SQL Express loaded on locally so it’s pointing back to itself.  Make sure you are logged on here with enough rights in SQL to do the magic you need.

image

You’ll notice the Report Output tab and the Report Builder Tab…we wanna go to the Report Builder Tab. After messing around a bit I found you could drag and drop tables from the table list over to the builder piece.  Selecting the items from the tables will produce those items in the final report.  After some poking around I found the values I wanted in the tblComputersytem and tblComputers tables.

image

As you can see above, I’ve linked to tables together with a join by dragging Computername from one table to the other.  If you click the SQL tab from the builder windows you can see the resulting code.

image

One thing you’’ll see is that I had to rename Computername in the tblComputersystem.Computername to Computername1 because there was a collision on this name between the two tables.  No big deal really.  Since the SQL is off the page I’ll drop it here for you to see better:

SELECT
  tblComputersystem.Manufacturer,
  tblComputersystem.Model,
  tblComputersystem.Computername AS Computername1,
  tblComputers.Username,
  tblComputers.Computername
FROM
  tblComputers
  INNER JOIN tblComputersystem ON (tblComputers.Computername = tblComputersystem.Computername)

Now just hit the disk icon to save the report to the report list.  Now you can click the Report Output tab to get your list:

image

This gives me the exact report I was looking for with Manufacturer, Model, Computer Name and the last logged in user.  Using the “Export to Excel” button I was able to manipulate the report further (by removing the duplicated ComputerName field) and to pretty it up for printing.  Yeah..I know there’s a way to remove the duplicate computername with the SQL code directly but this is just a quick and dirty example and I’m trying to be brief. :)

Hit me up on the comments if you find/develop any other cool and useful reports.

No comments: