topic Re: Count values in different fields in Splunk Search

Count values in different fields

rodkinal — Mon, 05 Mar 2018 14:53:38 GMT

Hello,

I'm having an issue regarding some fields. I have several fields which start with the same name but end different. Let me explain it with an example:

car.chevrollette = Camaro
car.nissan = gtr
car.bmw = 320d
car.mercedes = 220CDI
car.bmw = 118d
car.mercedes = ClassG
car.fiat = Croma
car.nissan = micra
car.bmw = 118d
[and so on...]

I would like to count the number of cars regarding one brand, for example... Mercedes = 2, Nissan=2, BMW=2, Fiat=1...
I don't know how to count based on the field instead of the value to create a table or a chart regarding the ocurrences. I would also like to count the number o ocurrences based on the value, for example: 118d=2, ClassG=1, Chevrollete=1, gtr=1...

¿Is there anyway to do this?

Any clue will be welcome!
Thank you very much in advance! 🙂

Re: Count values in different fields

niketn — Mon, 05 Mar 2018 15:49:50 GMT

@rodkinal, is this how your raw data looks like or is this after field extraction? Are the field names single value or multiple value?

Following is a run anywhere search based on the sample data provided (pipes | from makeresults till extract generate the sample data:

| makeresults
| eval data="car.chevrollette=Camaro;car.nissan=gtr;car.bmw=320d;car.mercedes=220CDI;car.bmw=118d;car.mercedes=ClassG;car.fiat=Croma;car.nissan=micra;car.bmw=118d"
| makemv data delim=";" 
| mvexpand data
| rename data as _raw
| extract kvdelim="="
| stats count(car_*) as *

Re: Count values in different fields

livehybrid — Mon, 05 Mar 2018 15:50:07 GMT

Are each of these on new lines/events?

index=yourIndex
| rex field=_raw "car\.(?<manufacturer>[a-zA-Z]+) = (?<model>[a-zA-Z0-9]+)"
| stats count by manufacturer

Re: Count values in different fields

rodkinal — Mon, 05 Mar 2018 16:38:46 GMT

Helo niketnilay,

Thank you very much for your quick response. The issue here is that we don't know all the possibilties so we can't build a data string. Anyway, thank you very much for your help. It's very appreciated 😄

Re: Count values in different fields

rodkinal — Mon, 05 Mar 2018 16:41:17 GMT

Hello livehybrid. I have already tried to look into the raw json, but the issue here is that the json file contains several "car.manufactuer=model" entries so, using this way, we only can list the first entry. I really appreciate you answer! 🙂 Kind regards!

Re: Count values in different fields

niketn — Mon, 05 Mar 2018 16:46:49 GMT

@rodkinal if you can provide the sample of raw data that you have we can create a query that would work for all combination. Above one is just run anywhere example based on data you have provided.

Based on the details provided if you already have field names as car.nissan, car.bmw with corresponding stats, all you need is to plug in the final stats command to your current search returning the fields i.e.

<YourBaseSearch>
| stats count(car.*) as *

If this does not work then maybe the data/extracted field is not of the form you have mentioned (multivalued fields need to be handled differently then single valued field etc). So, for us to assist you would need to provide some mock sample data as you are getting in your raw logs.