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
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.
thanks let me test. It might do the trick.
| eval len=length(q) | eventstats max(len) AS longest | where len=longest | stats count by q
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.
Thanks but it does not do the trick to select the longest q.
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
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
No it does not do the trick. Look at the question again.