Custom Report Request

[matthew1g]

I want to run a report that would show me how many Desktops, Laptops & Tablets we have on our domain. Our computer naming scheme uses -LT- or -PC- or -TB- to show this. Is there a way to write a report that would show me the totals of each type? e.g: there are 405 computers with -LT- in the name and 500 computers with -PC- in the name.

thanks!

[matthew1g]

That is exactly what I needed. Thank you very much!

[ChrisS · Accepted solution]

try this:

Select
'-PC-' = (Select Count(*) From tblComputers Where tblComputers.Computer Like '%-PC-%'),
'-LT-' = (Select Count(*) From tblComputers Where tblComputers.Computer Like '%-LT-%'),
'-TB-' = (Select Count(*) From tblComputers Where tblComputers.Computer Like '%-TB-%')

Cheers,

ChrisS

[matthew1g]

thanks for you help. Thats not exactly what i am needing. I know how to get a report that lists all the computers with -pc- in the name. What i want is a report that says: there are 500 computers with -pc- in the name. and then in the same report it tells me a count of -lt-, -tb-, and any other names i want to report on.

i think the output would look something like this:

-PC-: 500
-LT-: 200
-TB-: 100


thanks again!

[ChrisS]

Hi

This report should give you all the pc names that contain -pc-change pc in the code below as you will for LT or TB as neccesary
If the Computer names start with -pc- then remove the leading %
If the Computer names end with -pc- then remove the trailing %

the report will give you:
Computer Serial Number Domain OSname Description InstallDate Manufacturer Model PurchaseDate Warrantydate

Select Top 1000000 tblComputers.Computer,
  tblComputerSystemProduct.IdentifyingNumber As [Serial
Number],
  tblComputers.Domain, Web40OSName.OSname, tblOperatingsystem.Description,
  Web40OSName.Compimage As icon, tblOperatingsystem.InstallDate,
  tblComputersystem.Manufacturer, tblComputersystem.Model,
  tblCompCustom.PurchaseDate, tblCompCustom.Warrantydate
From tblComputers Left Outer Join
  tblOperatingsystem On tblComputers.Computername =
    tblOperatingsystem.Computername Inner Join
  web40ActiveComputers On tblComputers.Computername =
    web40ActiveComputers.Computername Inner Join
  Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join
  tblComputersystem On tblComputers.Computername =
    tblComputersystem.Computername Inner Join
  tblBIOS On tblComputers.Computername = tblBIOS.Computername Left Join
  tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
  Inner Join
  tblComputerSystemProduct On tblComputers.Computername =
    tblComputerSystemProduct.Computername
Where tblComputers.Computer Like '%-pc-%'

Optionally add this at the end (add a space after the last ') if you would like to further filter on a PC manufacturer (change hp to dell or ibm, fujitsu, etc)

And tblComputersystem.Model Like 'hp%'


hope its what your after

ChrisS