How to speed up Splunk query that uses appendcols?

[POR160893]

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,

[manjunathmeti · Accepted solution]

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]

Thanks and I gave you Karma also 🙂

[manjunathmeti]

Thank you 🙂