Splunk Search

How to speed up Splunk query that uses appendcols?

POR160893
Builder

Hi,

I have the following Splunk query:

index=ABC sourcetype=DEF dv_assignment_group="SECURITY-NETWORK-L3" 
| table _time, description, dv_parent, dv_state, dv_assigned_to
| dedup dv_parent
| appendcols [| inputlookup user_identities.csv
| where L6MgrName="John Doe"
| where NOT match(businessemail,"(?i)dellteam")
| eval copy=mvrange(0,3)
| mvexpand copy
| eval rnd=random()
| sort 0 rnd
| fields - copy rnd
| rex field=businessemail "(?<businessemail>[^@]+)@[^.]+\.com"
| eval businessemail=replace(businessemail, "\.", " ")
| search businessemail ="*"
| fields businessemail]
| eval "Employee to Review"=businessemail, "Time" = _time, "Description" = description, "Ticket Number" = dv_parent, "State" = dv_state, "Employee Assigned To" = dv_assigned_to
| where isnotnull(Time) or isnotnull("Ticket Number")
| table Time, Description, "Ticket Number", State, "Employee Assigned To", "Employee to Review"

However, the part of the query that involves the appendcols function is quiet slow. i.e.:
| appendcols [| inputlookup user_identities.csv
| where L6MgrName="John Doe"
| where NOT match(businessemail,"(?i)dellteam")
| eval copy=mvrange(0,3)
| mvexpand copy
| eval rnd=random()
| sort 0 rnd
| fields - copy rnd
| rex field=businessemail "(?<businessemail>[^@]+)@[^.]+\.com"
| eval businessemail=replace(businessemail, "\.", " ")
| search businessemail ="*"
| fields businessemail]

How can I optimise this search to speed it up?


Thanks,

Labels (5)
0 Karma
1 Solution

manjunathmeti
SplunkTrust
SplunkTrust

hi @POR160893,

You can move where command in the main search,

| inputlookup user_identities.csv where L6MgrName="John Doe" AND NOT businessemail="dellteam" AND businessemail="*"
| eval copy=mvrange(0,3)
| mvexpand copy
| eval rnd=random()
| sort 0 rnd
| fields - copy rnd
| rex field=businessemail "(?<businessemail>[^@]+)@[^.]+\.com"
| eval businessemail=replace(businessemail, "\.", " ")
| fields businessemail

View solution in original post

manjunathmeti
SplunkTrust
SplunkTrust

hi @POR160893,

You can move where command in the main search,

| inputlookup user_identities.csv where L6MgrName="John Doe" AND NOT businessemail="dellteam" AND businessemail="*"
| eval copy=mvrange(0,3)
| mvexpand copy
| eval rnd=random()
| sort 0 rnd
| fields - copy rnd
| rex field=businessemail "(?<businessemail>[^@]+)@[^.]+\.com"
| eval businessemail=replace(businessemail, "\.", " ")
| fields businessemail

POR160893
Builder

Thanks and I gave you Karma also 🙂

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Thank you 🙂

Get Updates on the Splunk Community!

Splunk Lantern | Getting Started with Edge Processor, Machine Learning Toolkit ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...