topic Re: Transaction very slow, use stats? in Splunk Search

Transaction very slow, use stats?

aq_natixis — Thu, 01 Oct 2015 12:42:45 GMT

Hello,

I have the following logs (1 line = 1 event):

id=**10** from="**10.10.10.44**"
id=10 ### whatever useless log ###
id=**10** bind uid="**toto**"
id=10 ### whatever useless log ###
id=**20** from="**10.10.10.55**"
id=20 ### whatever useless log ###
id=**20** bind uid="**tata**"
id=10 ### whatever useless log ###
id=20 ### whatever useless log ###
id=**30** from="**10.10.10.44**"
id=30 ### whatever useless log ###
id=**30** bind uid="**titi**"
id=30 ### whatever useless log ###
id=**30** bind uid="**toto**"
id=30 ### whatever useless log ###
id=20 ### whatever useless log ###

This is the table result I want :

uid from    count
toto    10.10.10.44 2
tata    10.10.10.55 1
titi    10.10.10.44 1

This is the search I use to get my result:

(...) "bind uid" OR "from"| transaction id startswith="from" | mvexpand uid | stats count by uid,from

I have exactly the result I want, but the search is VERY slow to perform. A lot of time is used to do the transaction.

So how I can improve my search, or how I can use stats instead of transaction?

Regards,

Re: Transaction very slow, use stats?

aljohnson_splun — Fri, 02 Oct 2015 14:45:55 GMT

Its not just stats, but stats on stats on stats! At least this is how I would do it.

If this part of your search

 "bind uid" OR "from"

gets you the events you want so that they're ordered like this:

 id=**10** from="**10.10.10.44**"
 id=**10** bind uid="**toto**"
 id=**20** from="**10.10.10.55**"
 id=**20** bind uid="**tata**"

You should just be able use the id field to group them.

... | stats list(from) as from, values(uid) as uid by id
| stats count by uid from id
| stats sum(count) as count by uuid, from

Your question was also a broad one - how can I improve this search?

Check out this awesome talk given at .conf2015 by Duncan Turnbull & Julian Harty. Here are a few takeaways:

use appropriate time range : speed up 30x - 365x
add index=foo sourcetype=bar and other metafields : speed up 2x - 10x
use the appropriate search mode (fast 😉 : speed up 2x - 5x
avoid not & other exclusionary searches (e.g. !=) : speedup 2x - 20x
use the fields command early in your search to ONLY get the fields you need
avoid subsearches

Re: Transaction very slow, use stats?

aq_natixis — Mon, 05 Oct 2015 12:12:22 GMT

Hello aljohnson,

Thanks for your answer.

I don't know why but the stats commands you gave me don't work... The search part is fast (like few sec), but the "finalize" part takes a looonnnggg time (like 15-30 minutes) for "No results found".

By the way I read the talk you gave and I divide the time by 5 !! So really thanks for that.
But I'm really curious about using stats instead of transaction. I think the mvexpand really slow the process...

EDIT:
I think you meant:
| stats list(from) as from, values(uid) as uuid by id

That's why I didn't got any result. But anyway the mvexpand is very long (45 sec without, an hour with)

Re: Transaction very slow, use stats?

aljohnson_splun — Mon, 05 Oct 2015 23:15:15 GMT

Hi @aq_natixis

1.) Yes, you caught my typo, oops

2.) Is mvexpand the slow portion? It very well may be. Try taking them out ! I think it should still work. I will remove them from my answer. and correct the other typo.

3.) Check the job inspector. This will tell you what is slow.

4.) Great talk - I'm glad it helped !!

5.) If this fixed your issue, please accept the answer.