Splunk Search

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

lpolo
Motivator

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

Tags (1)
0 Karma

lpolo
Motivator

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.

0 Karma

lpolo
Motivator

thanks let me test. It might do the trick.

0 Karma

sowings
Splunk Employee
Splunk Employee

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

sowings
Splunk Employee
Splunk Employee

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.

lpolo
Motivator

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

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

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

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

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

0 Karma

lpolo
Motivator

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...