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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...