topic Re: Can Splunk do this with a Search Query....? in Splunk Search

Can Splunk do this with a Search Query....?

lpolo — Wed, 14 Nov 2012 15:16:45 GMT

I have a log that registers search queries with the following format:

  _time                     q       MAC
11/13/12 12:46:31.000 AM    CA      0000000B4BE8
11/13/12 12:46:31.200 AM    CARL        0000000B4BE8
11/13/12 12:46:31.250 AM    CARLIE      0000000B4BE8
11/13/12 12:46:31.255 AM    CARLIE B    0000000B4BE8
11/13/12 12:46:31.270 AM    CARLIE BR   0000000B4BE8
11/13/12 12:46:32.100 AM    CHAR        0000000B4BE8
11/13/12 12:46:32.150 AM    CHARL       0000000B4BE8
11/13/12 12:46:32.155 AM    CHARLI      0000000B4BE8
11/13/12 12:46:32.223 AM    CHARLIE     0000000B4BE8
11/13/12 12:46:32.354 AM    CHARLIE B   0000000B4BE8
11/13/12 12:46:33.400 AM    CHARLIE BR  0000000B4BE8

The result set I need should be:

MAC      count      q
0000000B4BE8 5     CARLIE BR 
0000000B4BE8 6     CHARLIE BR

The recommended query found in below answer is the following:

search construct|
| stats count by q, MAC
| eval len=length(q)
| eventstats max(len) AS longest by MAC| where len=longest
| table MAC q count

The query does not returned the distinct longest q. If this query is ran against the sample log presented before the result set is:

MAC      count    q
0000000B4BE8 6       CHARLIE BR

It should be:

MAC      count      q
0000000B4BE8 5     CARLIE BR 
0000000B4BE8 6     CHARLIE BR

Any ideas? How to fix it.

Thanks,
Lp

Re: Can Splunk do this with a Search Query....?

alacercogitatus — Wed, 14 Nov 2012 15:34:27 GMT

Sure it can. Once you have the log in splunk, find it by using a search for that log, pipe it to stats, and away you go!

search_for_log | stats count by q

Re: Can Splunk do this with a Search Query....?

sowings — Wed, 14 Nov 2012 15:36:28 GMT

Yes; you'll want to write a field extraction to either capture the "A" "AL" "ALF" "ALL" values as their own field, or include them as part of the "q" field, then you can | stats count by <field>.

If you capture it as q, the command would look like | stats count by q; if you kept the contents of q the same (right now, auto-KV extraction would extract it as "UP"), but created a new field (e.g. "otherfield") for the A- values, your command would be | stats count by q, otherfield

Re: Can Splunk do this with a Search Query....?

lpolo — Wed, 14 Nov 2012 16:00:33 GMT

No it does not do the trick. Look at the question again.

Re: Can Splunk do this with a Search Query....?

lpolo — Wed, 14 Nov 2012 16:05:47 GMT

Thanks but it does not do the trick to select the longest q.

Re: Can Splunk do this with a Search Query....?

alacercogitatus — Wed, 14 Nov 2012 16:07:35 GMT

Well then taking into account the nonquoted values for q (auto kv breaks here) add some rex to your search.

search_for_log| rex field=_raw "q=(?<q_extracted>UP\s+[\w]+)\s+MAC)"|stats count by q_extracted

Re: Can Splunk do this with a Search Query....?

sowings — Wed, 14 Nov 2012 16:09:19 GMT

Then perhaps you should have been more explicit in your question.

I'd tackle that by collecting q as a field, use eval to set | eval len=length(q), then | eventstats max(len) AS longest next another search to filter to just those that have that length: | where len=longest and finally a stats command to get your final output: | stats count by q.

Re: Can Splunk do this with a Search Query....?

sowings — Wed, 14 Nov 2012 16:10:15 GMT


| eval len=length(q)
| eventstats max(len) AS longest
| where len=longest
| stats count by q

Re: Can Splunk do this with a Search Query....?

lpolo — Wed, 14 Nov 2012 16:12:03 GMT

thanks let me test. It might do the trick.

Re: Can Splunk do this with a Search Query....?

lpolo — Wed, 14 Nov 2012 16:20:00 GMT

It tried. It does not do the trick. It will always select the longest string. If you add |stats count by q MAC you will get the longest by MAC but not the distinct longest by MAC.

Re: Can Splunk do this with a Search Query....?

sowings — Wed, 14 Nov 2012 16:37:45 GMT

In every instance of your content sample, MAC is "1". Can you update that with actual sample data, and a clearer description of what you're after?

Re: Can Splunk do this with a Search Query....?

lpolo — Wed, 14 Nov 2012 18:04:54 GMT

I updated the question. thanks for your help.

Re: Can Splunk do this with a Search Query....?

sowings — Wed, 14 Nov 2012 18:39:17 GMT

Out of the box, Splunk will extract q having these values: UP SON HEL HELLO. I've had to use a regex on this sample to extract the full (with spaces) q field.

rex "q\=(?[\w\s]+)(?:\s+MAC=)"

Since you wanted it broken out by MAC, I also had to add this to my eventstats command, and the final stats call.


| eval len=length(q)
| eventstats max(len) AS longest by MAC
| where len=longest
| stats count by MAC, q
| table MAC q count

Re: Can Splunk do this with a Search Query....?

lpolo — Wed, 14 Nov 2012 19:09:28 GMT

It does not find the distinct searches. It just grabs the longest q. any idea?

Re: Can Splunk do this with a Search Query....?

sowings — Wed, 14 Nov 2012 19:22:09 GMT

You asked for the longest q. The search string I've posted in the comment above (combined with the rex statement to capture all of the "q" field) works and produces the output you've asked for in the original question.

If you're having trouble with it, post the results you get from the above search, so that we can figure out what's going wrong.

Re: Can Splunk do this with a Search Query....?

lpolo — Wed, 14 Nov 2012 19:25:30 GMT

I am already extracting q and MAC both are extracted automatically by Splunk. Do I need the rex command?

Re: Can Splunk do this with a Search Query....?

sowings — Wed, 14 Nov 2012 19:34:57 GMT

If you're extracting q automatically by Splunk, you're using the automatic key=value extraction. Because of the whitespace in the q field, Splunk will see that as a separate word, and not as part of the value of "q". Therefore, yes you will need the rex command I've provided above.

Re: Can Splunk do this with a Search Query....?

lpolo — Wed, 14 Nov 2012 19:55:27 GMT

I updated the question. It did the job. thanks I learned from your example. One more question. Should I expect any search limitations with this query?

Re: Can Splunk do this with a Search Query....?

lpolo — Wed, 14 Nov 2012 19:57:44 GMT

It is quite slow... Any way to improve the query?

Re: Can Splunk do this with a Search Query....?

sowings — Wed, 14 Nov 2012 20:03:43 GMT

Search limitations? As in performance? No, I wouldn't think so. Eventstats can be a little heavy, but given what you're doing, it's necessary, so....

If you find that there are performance problems with the search, you could perform the stats count by q, MAC first, then do the eval, eventstats, where, etc, after.

Stats will reduce the overall number of rows by performing aggregation, and discarding detail (in this case, time stamps go away, but you get the count), so the next search commands have smaller data sets to work with, and are therefore a bit faster.