How to optimize my SPL?

[uagraw01]

Hello Splunkers,

I want to optimize my splunk search. I have attached the screenshot of my search. From the raw data i am retreving the services name in or condition. I don't want to hardcore all the services name by using OR clause. Please give me some suggestions, how can i optimise the search with using OR clause.

[Gr0und_Z3r0]

Hi @uagraw01 

In your search terms for the services, replace that with "ERROR" 
Update your extractions for Error Code this way 

| rex "errorCode=(?P<EC1>[\d]+)" 
| rex "Error Code :: (?P<EC2>[\d]+)"
| eval ErrorCode=coalesce(EC1,EC2)
| where ErrorCode>499

And continue to extract service and other fields as required and check the results.
If the result yields services that need not be included in your result, try to add them using the NOT keyword as part of your search like

NOT("xyz-service" OR "abc-service")

[uagraw01]

@Gr0und_Z3r0 My ask here is to optimize my existing query. Because here we are using or condition to fetch the services name from the raw data . I also want to use tstats command in my existing query but it is basically running on the metadata fields. So if you can suggest any approach from which I can optimise my search.

[uagraw01]

@venky1544  Thanks for letting me know. I have attached the screenshot now.

[venky1544]

Hey @uagraw01 

your screenshot is not uploaded probably you missed it 😋

[venky1544]

Hi @uagraw01 

How are these service placed in the raw data

looking at your services majority of them are ending with a space status and other ones which don't have status are starting with keyword Mulesoft probably you use a regex to a grouped name and then use that for rest of the query

sterling.http.orderInvoice.post status
sterling.http.orderHistory.get status
AdyencheckoutPaymentmethods status

rex field=_raw " (?<payservices>[\w.]+ status)"

MulesoftReserveInventory
MulesoftOrderExport
MulesoftOrderInvoice

rex field=_raw "(?<services>Mulesoft\w+)"

Hope this helps 

if this help karma points are  appreciated /accept the solution it might help others 

[uagraw01]

@venky1544 Thanks for your answer and investing your time on my query. Just let me know by this way can we optimize the query ? 

Let me suggest :

1. Can I use lookup and put all the services and join the index search with lookup. ( Is that optimize my search ?)

2. Can I use data model and fetch the data directly by using the pivot ? (Is that optimize my search ?)

Please provide your suggestion on these points as well.

[venky1544]

Hi @uagraw01 

lookup is a option you can go for it but question is how are you populating it in the lookup. My guess by EOD you would be  manually populating it right or when you have to add a new service you have to manually populate the lookup and this would be again mean hardcoding them manually which is what you wanted to avoid in the first place ?? how are these services placed in the raw data is the question 

if this help karma points are  appreciated /accept the solution it might help others 

[uagraw01]

@venky1544  

As per the attached screen my raw data is looking like this and i have highlighted that service name in the event screenshot

[venky1544]

Hi @uagraw01 

if you have the services in key value pair why are you hardcoding them in your query 

are you excluding some specific services ??

definitely regex would not be my first choice if we have the key value pair 

[SinghK]

Spoiler

Better would be to extract those fields during indexing  or add them to props for search time extractions and then use them in search. With 2or 3 rex extractions search must be running slow..

[uagraw01]

@SinghK Actually i have very limited access here. Search time field exaction i can't do here. I have no power broker nor admin access. Have dependencies on other teams.