Counting Particular Characters Within a Field

[henryt1]

Hello,

I need to put together a report that involves counting certain characters in a field within Splunk. For instance, my query looks like "... | stats count by q". What I'm returned with the field "q" might look like either of the following:

(summary:(superhydrophob*%20OR%20superoleophob*))%20AND%20(summary:(fabric%20OR%20hair%20OR%20skin))%20AND%20(publicationdate:[2007%20TO%202012])

%28summary%3A%28machine%20AND%20learning%20AND%20error%29%29%20AND%20%28publicationdate%3A%5B2007%20TO%202012%5D%29%20AND%20%28country%3AUS%29

Within those two results I'm interested in how many total colons there are, so because of HTML encoding I would be interested in both ":" as well as "%3A". So the total amount of characters with these two results that I would like to get back would be 6.

Is there anyway I could do this within Splunk? Right now I have to export and use Excel, which is extremely time consuming. Any help would be greatly appreciated, thanks in advance.

-Tyler

[_d_]

..| eval bar = urldecode(q) | eval colCount=mvcount(split(bar,":"))-1 | stats count by q colCount | eventstats sum(colCount) as TotalColons

[alacercogitatus]

Rex and eval might be able to help in this case. My approach first decodes the q field, so you only have to work with one format of colon. Then, you can count the number of matches for a single colon.

Try this:

your_search | eval dq = urldecode(q)|rex max_match=100 field=dq "(?<colons>:)"|stats count(colons) as "Number of Total colons" by dq

[henryt1]

It looks like it's still seeing the whole "q" field but the count is pretty far off.

[alacercogitatus]

How far off was it? and the count is per distinct url. If you just want total, then do "count(colons)". You can also try setting max_match to 0, which means unlimited. But I'm not sure if you have more than 100 colons in a line or not.
I did notice that because you have "%20" (a space), the q did not extract properly on my test. So for the colons it sees, it sees the right number, but doesn't get the whole q because of the space.

[henryt1]

This didn't see to work. It gave me a separate column for the colons, but the count wasn't correct.

[sdaniels]

This should help.

http://splunk-base.splunk.com/answers/28276/count-of-character-in-field

Details on the functions of eval here:

http://docs.splunk.com/Documentation/Splunk/5.0.1/SearchReference/CommonEvalFunctions