Splunk Search

Transaction very slow, use stats?

aq_natixis
Engager

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,

0 Karma
1 Solution

aljohnson_splun
Splunk Employee
Splunk Employee

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

View solution in original post

aljohnson_splun
Splunk Employee
Splunk Employee

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

aq_natixis
Engager

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)

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

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.

0 Karma
Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...