4 Replies Latest reply on Dec 23, 2013 9:47 AM by wluther

    how do i count the different "unique" descriptions of all vendors like occam?

    wluther

      I have searched around and found bits here and there, but I am simply not able to put it all together with my primitive sql understanding, or lack there of...

       

      There is a canned report that serves the same purpose as what I need.  The problem I have is, in this world of non-standard standards, not all of my devices list their model info in the same place.

      More specifically, my occam devices seem to list their model info in Nodes.Description, instead of MachineType, like the others.  This "Inventory: Device Types" report gives me exactly what I want from all devices except my lazy Occam devices.

       

      I do not know enough about sql yet to figure out how to get this working... I mean, I can barely even spell slq...

       

      The "Inventory: Device Types" report shows me the number of total Occam devices, but not the totals for each different type of models.

       

      "Machine Type"    Total

      "Occam BLC 6450"    25

      "Occam Networks, Inc."    611

       

      As you can see, the canned inventory report actually only shows me a total for a single Occam model (6450), as well as the total of the other 11 different Occam models.

       

      I was able to verify the number of different Occam models using the query below:

      SELECT

      Nodes.MachineType AS Machine_Type, COUNT(DISTINCT(CAST(Nodes.Description AS VARCHAR))) AS Count_Of_Device_Models

      FROM Nodes

      WHERE Nodes.Vendor LIKE '%occam%'

      GROUP BY Nodes.MachineType

      ORDER BY 2 DESC

       

      Which shows me:

      "Machine Type"    Count_Of_Device_Models

      "Occam Networks, Inc."    11

      "Occam BLC 6450"    1

       

       

      I can do this to show me a list of each different Occam model,

      SELECT

      DISTINCT(CAST(Nodes.Description AS VARCHAR)) AS 'Occam Model'

      FROM Nodes

      WHERE Nodes.Vendor LIKE '%occam%'

       

      Which will show me the names of what I am looking for, but not the count of each different type:

      B6-252

      B6-640

      BLC 6151 01

      BLC 6152 01

      BLC 6212 02

      BLC 6214

      BLC 6252 02

      BLC 6252 03

      BLC 6440 01

      BLC 6450

      BLC 6640 01

      BLC B6 252

       

       

       

      Please help a poor little peon with his sqwell...