Knowledge Management

help optimise a long running search

dm1
Contributor

I have a search which uses email data to search specific email logs for communications from/to specific organization and lists all necessary attributes required. This search is required to run end of every month and generate a report. This scheduled search roughly takes 25 hours to run.

index=cisco_esa sourcetype=cisco:esa:textmail mail_logs 
| transaction internal_message_id maxspan=300s
| search recipient="*@abc.com" OR sender="*@abc.com"
| table _time internal_message_id sender recipient field2 field3
| outputlookup abc_esa_summary.csv

 

Can somebody please suggest some improvements to this search to make it run faster ?

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @dm1,

transaction is a very slow command, you can replace it using stats, something like this:

index=cisco_esa sourcetype=cisco:esa:textmail mail_logs 
| stats earliest(_time) AS _time values(sender) AS sender values(recipient) recipient values(field2) AS field2 values(field3) AS field3 BY internal_message_id
| search recipient="*@abc.com" OR sender="*@abc.com"
| table _time internal_message_id sender recipient field2 field3
| outputlookup abc_esa_summary.csv

If this solution isn't acceptable for you (maybe the limit of maxspan is relevant!), you could schedule your search to run eventy night with a timeframe of 24 hours, saving results in a summary index  (with the collect command) and every month you can take all the results.

Ciao.

Giuseppe

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dm1,

transaction is a very slow command, you can replace it using stats, something like this:

index=cisco_esa sourcetype=cisco:esa:textmail mail_logs 
| stats earliest(_time) AS _time values(sender) AS sender values(recipient) recipient values(field2) AS field2 values(field3) AS field3 BY internal_message_id
| search recipient="*@abc.com" OR sender="*@abc.com"
| table _time internal_message_id sender recipient field2 field3
| outputlookup abc_esa_summary.csv

If this solution isn't acceptable for you (maybe the limit of maxspan is relevant!), you could schedule your search to run eventy night with a timeframe of 24 hours, saving results in a summary index  (with the collect command) and every month you can take all the results.

Ciao.

Giuseppe

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dm1,

good for you, see next time!

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

0 Karma

dm1
Contributor

@gcusello Thanks! I just tested your search and it seems to be giving what I want in a fairly faster way. Still testing it.

Can you please elaborate on ? I have never tried this

"saving results in a summary index  (with the collect command) and every month you can take all the results. "

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dm1,

the approach is the following:

you modify you search replacing the last row with "collect index=my_summary_index".

you schedule your search to run every night on the last 24 hurs.

in this way you save the results in a summary index and you can run you montly search on the summary index and you have in a table all the results.

In this way you have a very quick search that you can also run every day.

For more infos you can see at https://docs.splunk.com/Documentation/Splunk/8.1.3/Knowledge/Usesummaryindexing

Ciao.

Giuseppe

P.S.: if this answer solves your your need, please accept it for the other people of Community, and Karma Points are appreciated by all the contributors 😉

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@dm1 

Any specific reason to use transaction commands?

Meanwhile you can try by adding fields command before transaction,

index=cisco_esa sourcetype=cisco:esa:textmail mail_logs 
| fields internal_message_id sender recipient field2 field3

 

0 Karma

dm1
Contributor

@kamlesh_vaghela because transaction command groups all messages having same unique message identifier which is internal_message_id

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...