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
Champion

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
Champion

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
Champion

Thank you 🙂

Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

REGISTER NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If ...

Observability | Use Synthetic Monitoring for Website Metadata Verification

If you are on Splunk Observability Cloud, you may already have Synthetic Monitoringin your observability ...

More Ways To Control Your Costs With Archived Metrics | Register for Tech Talk

Tuesday, May 14, 2024  |  11AM PT / 2PM ET Register to Attend Join us for this Tech Talk and learn how to ...