How to count sequence of strings

[lpolo]

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

[lpolo]

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

sourcetype="queries" q NOT NULL |
eval q=upper(q) |
eval QueryFirstLetter=substr(q,1,1) |
transaction maxpause=0s QueryFirstLetter delim="," mvlist=q |
eval Query=mvindex(q, -1) |
top limit=0 Query

[lpolo]

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

sourcetype="queries" q NOT NULL |
eval q=upper(q) |
eval QueryFirstLetter=substr(q,1,1) |
transaction maxpause=0s QueryFirstLetter delim="," mvlist=q |
eval Query=mvindex(q, -1) |
stats count by Query|sort - Query

[lpolo]

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.

[kristian_kolb · Accepted solution]

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

[kristian_kolb]

Well, what results did you get?

/k

[lpolo]

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

Thanks,

[southeringtonp]

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 )

[lpolo]

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

[lpolo]

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

[kristian_kolb]

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