Dynamic Stats Counting

[jaoui]

I want to take data I am receiving from snmp from my Cisco devices about their inventory and perform a count.

The data I am getting comes in a csv similar to this (I added the csv header just for description of the fields I am extracting).
I am able to generate counts of each model found per hostname through a very manual query:

host="Inv" | stats count(eval(Model="WS-C6509")) as "Catalyst 6509", count(eval(Model="WS-SUP720-BASE")) as Sup-720Base, count(eval(Model="WS-X6148A-GE-45AF")) as Module-6148A, count(eval(Model="WS-X6704-10GE")) as Module-6704, count(eval(Model="WS-CAC-6000W")) as PS-6000W by Hostname

However, I can't always know what will appear in the 'Model' field so I need some way to automatically make the count of unique values per hostname happen

Any suggestions?
Thanks much!!

Here's a snippit of the data I get to play with

Hostname,Slot,Category,Serial,Model,Description,IOS
switch1,WS-C6509,3,SCA043200X0,WS-C6509,Cisco Systems Catalyst 6500 9-slot Chassis System,
switch1,PS 1 WS-CAC-6000W,6,DCH1302Y04L,WS-CAC-6000W,"AC power supply, 6000 watt 1",
switch1,PS 2 WS-CAC-6000W,6,DCH1302Y04F,WS-CAC-6000W,"AC power supply, 6000 watt 2",
switch1,SLOT2,9,SAL061218L8,WS-X6816-GBIC,WS-X6816-GBIC Pure SFM-mode 16 port 1000mb GBIC Rev. 1.3,12.2(14r)S
switch1,Transceiver Gi2/3,9,1101400000000000,,Transceiver 1000BaseLH Gi2/3,
switch1,Transceiver Gi2/5,9,A52717308 ,,Transceiver 1000BaseSX Gi2/5,
switch1,Transceiver Gi2/9,9,A52815844 ,,Transceiver 1000BaseSX Gi2/9,
switch1,SLOT3,9,SAL1250CNKC,WS-X6148A-GE-45AF,WS-X6148A-GE-45AF 48-port 10/100/1000 RJ45 EtherModule Rev. 2.4,8.4(1)
switch1,SLOT4,9,SAL1313MA35,WS-X6148A-GE-45AF,WS-X6148A-GE-45AF 48-port 10/100/1000 RJ45 EtherModule Rev. 2.4,8.4(1)
switch1,SLOT5,9,SAD072704MM,WS-SUP720-BASE,WS-SUP720-BASE 2 ports Supervisor Engine 720 Rev. 2.1,7.7(1)
switch1,SLOT6,9,SAD0733027W,WS-X6704-10GE,WS-X6704-10GE CEF720 4 port 10-Gigabit Ethernet Rev. 1.0,12.2(14r)S5
switch1,SLOT7,9,SAL1312LW1A,WS-X6148A-GE-45AF,WS-X6148A-GE-45AF 48-port 10/100/1000 RJ45 EtherModule Rev. 2.4,8.4(1)
switch1,SLOT1,9,SAL1249CCJP,WS-X6148A-GE-45AF,WS-X6148A-GE-45AF 48-port 10/100/1000 RJ45 EtherModule Rev. 2.4,8.4(1)
switch2,WS-C6509,3,TBM06340969,WS-C6509,Cisco Systems Catalyst 6500 9-slot Chassis System,
switch2,PS 1 WS-CAC-6000W,6,AZS13090AH5,WS-CAC-6000W,"AC power supply, 6000 watt 1",
switch2,PS 2 WS-CAC-6000W,6,AZS13090AHA,WS-CAC-6000W,"AC power supply, 6000 watt 2",
switch2,SLOT1,9,SAL1313MA4K,WS-X6148A-GE-45AF,WS-X6148A-GE-45AF 48-port 10/100/1000 RJ45 EtherModule Rev. 2.4,8.4(1)
switch2,SLOT9,9,SAL1313MA42,WS-X6148A-GE-45AF,WS-X6148A-GE-45AF 48-port 10/100/1000 RJ45 EtherModule Rev. 2.4,8.4(1)
switch2,SLOT2,9,SAL1019MEKM,WS-X6816-GBIC,WS-X6816-GBIC Pure SFM-mode 16 port 1000mb GBIC Rev. 1.9,12.2(18r)S
switch2,Transceiver Gi2/3,9,A52808774 ,,Transceiver 1000BaseSX Gi2/3,
switch2,SLOT3,9,SAL1313MA3Q,WS-X6148A-GE-45AF,WS-X6148A-GE-45AF 48-port 10/100/1000 RJ45 EtherModule Rev. 2.4,8.4(1)
switch2,SLOT4,9,SAL1312LZ2D,WS-X6148A-GE-45AF,WS-X6148A-GE-45AF 48-port 10/100/1000 RJ45 EtherModule Rev. 2.4,8.4(1)
switch2,SLOT5,9,SAD072704HS,WS-SUP720-BASE,WS-SUP720-BASE 2 ports Supervisor Engine 720 Rev. 2.1,7.7(1)
switch2,SLOT6,9,SAD0733027G,WS-X6704-10GE,WS-X6704-10GE CEF720 4 port 10-Gigabit Ethernet Rev. 1.0,12.2(14r)S5
switch2,Transceiver Te6/2,9,ONJ0735001T,,Transceiver 10Gbase-LR Te6/2,
switch2,SLOT7,9,SAL1312LZ7B,WS-X6148A-GE-45AF,WS-X6148A-GE-45AF 48-port 10/100/1000 RJ45 EtherModule Rev. 2.4,8.4(1)


[jaoui]

With many thanks to the first answer, I am working with the following search on the same data:

host="Inv" | eval newfield = if(isnull(Model) OR len(Model)<=1,Description,Model) | chart count over Hostname by newfield limit=0

What I am doing in this search is substituting empty or bogus data in the "Model" field with the data from the "Description" field. This is in an attempt to better group my count of inventory.

The issue I run into when using the Description field, however, is that items that are the exact same type are appended with the module location:

for example:

switch1,Transceiver Gi2/5,9,A52717308 ,,Transceiver 1000BaseSX Gi2/5,


switch1,Transceiver Gi2/9,9,A52815844 ,,Transceiver 1000BaseSX Gi2/9,

both are the same type according to the Description except for one has "Gi2/5" and the other has "Gi2/9"

Any ideas on how to get splunk to ignore or strip out that last part so these two Descriptions are grouped together?

[ftk · Accepted solution]

So you are looking for a table like this?:

Hostname  WS-CS6578  WS-SUP720-BASE something_else
switch1      3            4               1
switch2      4            1               2

You can use chart for this:

host="Inv" | chart count over Hostname by Model usenull=f useother=f limit=0

[ftk]

To be fancier you could make a lookup that correlates the model names to friendly names, and then use the friendly model names in the chart command.