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
..| eval bar = urldecode(q) | eval colCount=mvcount(split(bar,":"))-1 | stats count by q colCount | eventstats sum(colCount) as TotalColons
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
It looks like it's still seeing the whole "q" field but the count is pretty far off.
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.
This didn't see to work. It gave me a separate column for the colons, but the count wasn't correct.
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