All IP Device Reports

 

Recently I have been asked to do a lot of asset management reports. One piece of information that keeps being asked is “How many ____ do we have?” Fill in blank with Servers, PCs, Printers, etc.

 

Since we had Network discovery enable, I though that I would try another way to provide the information that everyone was looking for. Since I know how many IP devices were found during the Network discovery and I know the number of workstation and  server. I could easily figure out how many unknown IP device we had connected to the network.

 

No sooner had I finished this report then I extended it to included HP printers and after some research finally I added other printers. I wanted to used MAC address to determine that a device was indeed a printer however after talking to HP I found out that the IEEE NOW assignees MAC address in small bunches in order to prevent MAC address spoofing. <Grrr…> this meant that I could not use the MAC address to absolutely determine that a device is a printer. There fore I have to resort to the “stand by” method to determine if an IP device is a printer or not.

 

This report will show you the number of Workstation, Servers, HP Printer, Xerox Printer, OKIData, Lexmark Printer Extent Net Print Boxes, Other IP Device and Total IP Devices

 

The following information is used to find each device

 

Workstation

Is a count of v_GS_SYSTEM.SystemRole0 where role = Workstations

 

Server

Is a count of v_GS_SYSTEM.SystemRole0 where role = Server

 

HP

Device name starts with NPI%

 

OKIData

Device name starts with OKI%

 

Xerox

Device name starts with XRX%

 

Lexmark

Device name starts with LM\xK%

 

ESI

Device name starts with ESI%

 

Other IP Device

Other = Total – Server-Workstation – HP- Lexmark – OKIData – ESI

 

Total IP Device

Is a count of v_R_System devices.

 

 

 

Cavettes:

  • Network discovery will only create a DDR record of a device which it can determine it subnet mask and that matches the Network boundary. Therefore your other IP devices are probably higher. http://support.microsoft.com/?kbid=237557
  • If any device is printer is rename from its default device name, it will not be counted as a Printer
  • If any device happened to have a name that begins with any of the Printer Names then you will get some false counts.

 

— Printer Vars
Declare @HP int  — HP Printers
Declare @OKI int — OKIData Printer
Declare @XRX int — Xerox Printer
Declare @ESI int — ESI Printer boxes
Declare @LXK int — Lexmark Printer

— PCs Vars
Declare @WS int
Declare @Ser int

— Other Vars
Declare @Total int
Declare @Dif int

— Popular each Var.
set @WS = (SELECT Count(v_GS_SYSTEM.SystemRole0)FROM v_GS_SYSTEM v_GS_SYSTEM WHERE (v_GS_SYSTEM.SystemRole0 != ‘Server’))
set @Ser = (SELECT Count(v_GS_SYSTEM.SystemRole0)FROM v_GS_SYSTEM v_GS_SYSTEM WHERE (v_GS_SYSTEM.SystemRole0 = ‘Server’))
set @Total = (SELECT Count(v_R_System.ResourceType)FROM v_R_System v_R_System)
set @HP = (SELECT Distinct Count(v_R_System.Name0)FROM v_R_System v_R_System where v_R_System.Name0 like ‘NPI%’ and v_R_System.Operating_System_Name_and0 Is Null)
set @OKI = (SELECT Distinct Count(v_R_System.Name0)FROM v_R_System v_R_System where v_R_System.Name0 like ‘OKI%’ and v_R_System.Operating_System_Name_and0 Is Null)
set @XRX = (SELECT Distinct Count(v_R_System.Name0)FROM v_R_System v_R_System where v_R_System.Name0 like ‘XRX%’ and v_R_System.Operating_System_Name_and0 Is Null)
set @ESI = (SELECT Distinct Count(v_R_System.Name0)FROM v_R_System v_R_System where v_R_System.Name0 like ‘ESI%’ and v_R_System.Operating_System_Name_and0 Is Null)
set @LXK = (SELECT Distinct Count(v_R_System.Name0)FROM v_R_System v_R_System where v_R_System.Name0 like ‘LXk%’ and v_R_System.Operating_System_Name_and0 Is Null)
Set @Dif = @Total – (@WS+@Ser+@hp+@OKI+@XRX+@ESI+@LXK)

Select
@WS as ‘Workstations’,
@Ser as ‘Server’,
@HP as ‘HP Printers’,
@OKI as ‘OKI Data Printers’,
@XRX as ‘Xerox Printers’,
@LXK as ‘Lexmark Printers’,
@ESI as ‘ESI Printer Boxes’,
@Dif as ‘Other IP Devices’,
@Total as ‘Total IP Devices’

 

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *