topic How to count sequence of strings in Splunk Search

How to count sequence of strings

lpolo — Fri, 28 Oct 2011 19:02:30 GMT

I have the following log:

01/02/2011:00:00:01 q=UP
01/02/2011:00:00:02 q=UP A
01/02/2011:00:00:03 q=UP AL
01/02/2011:00:00:04 q=UP ALF
01/02/2011:00:00:05 q=UP ALL

And I would like to have these result set:

q COunt
UP ALF 1
UP ALL 1

Any ideas?
Thanks

Re: How to count sequence of strings

kristian_kolb — Fri, 28 Oct 2011 19:20:11 GMT

Hi,

Is that your actual log, or just a sample of what the log could look like? Do the log messages end just after A, AL or ALF etc?

Not 100% sure of the output you want either. Are you only interested in the count of events for ALF and ALL, but not for A or AL?

Are you familiar with field extractions?

Yes, I have ideas, but some more info would be good.

/kristian

Re: How to count sequence of strings

lpolo — Sat, 29 Oct 2011 01:20:48 GMT

That is the actual log. q is a set of query searches. In this example the user typed q in the sequence I presented as a result the intention of the user was UP ALF. Then another user typed UP ALL.
q can be any string.

Thanks

Re: How to count sequence of strings

southeringtonp — Sat, 29 Oct 2011 01:43:37 GMT

Assuming you always want everything after the equals sign, it's pretty straightforward:

...
| rex field="_raw" "q=(?<querystring>.*)"
| stats count by querystring

Or, you can set up a more permanent field extraction as below, and then use stats without the rex command:

#In transforms.conf...
[qstring]
REGEX = q=(.*)
FORMAT = querystring::$1

#In props.conf...
[putYourSourcetypeHere]
REPORT-qstring = qstring

(search for ... | stats count by querystring )

Re: How to count sequence of strings

kristian_kolb — Sat, 29 Oct 2011 09:36:12 GMT

You should extract the q field by adding to/creating your stanza for the source/sourcetype in props.conf

[your_source_or_sourcetype_here]
EXTRACT-q_string = \sq=(?<qstring>.*)$

Then you can have a search like

... | stats count AS Count by qstring | addcoltotals Count labelfield=qstring label="Total no. queries"

Which should give you;

qstring             Count
--------------------------
UP                      6
UP ALL                  4
UP AF                   2
Total no. queries      12

EDIT: typo in the field extraction..

Hope this helps,

Kristian

Re: How to count sequence of strings

lpolo — Sun, 30 Oct 2011 02:29:33 GMT

Thanks for your response. The issue is not about how extract the value of q.

Re: How to count sequence of strings

lpolo — Sun, 30 Oct 2011 02:34:18 GMT

It does not work... The query does not return the result set I presented in the example.....

Thanks,

Re: How to count sequence of strings

lpolo — Sun, 30 Oct 2011 02:41:26 GMT

After some work I think that this query does the work:

Re: How to count sequence of strings

kristian_kolb — Thu, 10 Nov 2011 12:43:34 GMT

Well, what results did you get?

Re: How to count sequence of strings

lpolo — Thu, 10 Nov 2011 16:15:13 GMT

This query is working fine but If I select a large time period it fails. I will update this notes once I have more information.

Re: How to count sequence of strings

lpolo — Thu, 10 Nov 2011 18:46:21 GMT

I replace the top command by stats. Now it is working.