query running using KV store is taking logn time

[vikashperiwal]

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

[arjunpkishore5]

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

[to4kawa]

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]

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.

[vikashperiwal]

Sorry but the one you suggested changes our requirement.

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