How to parse the payload inside a csv log to run a stats count search on the extracted fields?

[anthonycopus]

Hi,

What would be the simplest way of parsing the following logs so I can search what is inside the {} field:

"Feb 25, 2015 11:59 PM",20,PreviewScreen_Next,,201502051926,iPhone,Apple iPhone 6,51FC9233-F23B-44FA-B3CC-E91F56F26C30,{ field1 : Original;  media : MediaType;  post : mu},

The name of the field in the csv is "Params". All the other fields are fine as I can search them, but I need to be able to do a stats count by media for example.

Thanks

[emiller42]

The content of that field is ALMOST json, which would make it super easy to parse. If it were:

{"field1": "Original", "media": "MediaType", "post": "mu"}

You could use the spath command to parse it.

| spath input=Params

But it isn't, so that's not useful. However, you should be able to use extract without having to use a complex regex.

... | extract pairdelim=";{}" kvdelim=":" 

I tested it with the following:

index=* | head 1 | eval _raw="{ field1 : Original;  media : MediaType;  post : mu}"  | extract pairdelim=";{}" kvdelim=":" | table field1 media post

which outputs:

field1	media	post
Original	MediaType	mu

This will be flexible to any kvpairs that happen to show up in the Params field. (Note it'll get weird if the values themselves contain {}:; as that'll be what it's looking for to separate kvpairs)

[MuS]

Hi anthonycopus,

try something like this:

your base search here | rex "field1\s:\s(?<field1>.+?);\s+media\s:\s(?<media>.+?);\s+post\s:\s(?<post>.+?)}" | stats count by media

Hope that helps ...

cheers, MuS

[anthonycopus]

Thanks, this works. Is there a combination which would work if the logs follow this trend, but some may have more fields than others? E.g. one log has field1 and media in the payload, but the other has only field1

[MuS]

You could setup field extraction for each field separately, take a look at the docs here http://docs.splunk.com/Documentation/Splunk/6.2.2/Knowledge/ExtractfieldsinteractivelywithIFX