Splunk Search

query running using KV store is taking logn time

vikashperiwal
Path Finder

Hi ,

I have a scenario where i am using KV store to get the events generated. But my query is taking 5hr to run which is unexpected .

Please let me know way to improve the query optimization.
index=ndspr sourcetype=ISUP_EVENT_ACCESS_VW ------ 5M events
lookup (lookup core_ip_voice_keystore )had -- 6,00,000

Total events triggered 5M

Query-----

index=ndspr sourcetype=ISUP_EVENT_ACCESS_VW
| lookup core_ip_voice_keystore DPC as N OPC as O CIC as K OUTPUT OPC DPC CIC ADNUM ADMININF NETNAME ROUTESET TRKGRSIZ
|fields A B C D E F K N O OPC DPC CIC ADNUM ADMININF NETNAME ROUTESET TRKGRSIZ
| search OPC=*
| table A B C D E F K N O OPC DPC CIC ADNUM ADMININF NETNAME ROUTESET TRKGRSIZ

0 Karma

arjunpkishore5
Motivator

Try moving the filter for OPC to the base search

index=ndspr sourcetype=ISUP_EVENT_ACCESS_VW O=*
| lookup core_ip_voice_keystore DPC as N OPC as O CIC as K OUTPUT OPC DPC CIC ADNUM ADMININF NETNAME ROUTESET TRKGRSIZ
| table A B C D E F K N O OPC DPC CIC ADNUM ADMININF NETNAME ROUTESET TRKGRSIZ

If you need to configure automatic time lookup, use the following doc for reference https://docs.splunk.com/Documentation/Splunk/8.0.0/Knowledge/Makeyourlookupautomatic#Example_configu...

In your case.

[ISUP_EVENT_ACCESS_VW]
LOOKUP-core_ip_voice_keystore = core_ip_voice_keystore DPC as N OPC as O CIC as K OUTPUT OPC DPC CIC ADNUM ADMININF NETNAME ROUTESET TRKGRSIZ

And the your search would be.

 index=ndspr sourcetype=ISUP_EVENT_ACCESS_VW OPC=*
| table A B C D E F K N O OPC DPC CIC ADNUM ADMININF NETNAME ROUTESET TRKGRSIZ
0 Karma

to4kawa
Ultra Champion
index=ndspr sourcetype=ISUP_EVENT_ACCESS_VW
| eval DPC=N, OPC=O, CIC=K
| inputlookup append=t core_ip_voice_keystore
| table A B C D E F K N O OPC DPC CIC ADNUM ADMININF NETNAME ROUTESET TRKGRSIZ

Hi, @vikashperiwal
Since inputlookup is used with append = t , search OPC = * is not necessary.
How about it?

to4kawa
Ultra Champion

Hi, @vikashperiwal
If this is still slow, you should consider creating a data model.
I don't know about data model, so please ask again.

0 Karma

vikashperiwal
Path Finder

Sorry but the one you suggested changes our requirement.

We are trying to use automatic lookups , so that we can enhance data faster

0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...