topic Re: query running using KV store is taking logn time in Splunk Search

query running using KV store is taking logn time

vikashperiwal — Wed, 30 Sep 2020 03:04:42 GMT

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

Re: query running using KV store is taking logn time

to4kawa — Tue, 26 Nov 2019 11:23:23 GMT

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?

Re: query running using KV store is taking logn time

to4kawa — Wed, 27 Nov 2019 12:18:17 GMT

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.

Re: query running using KV store is taking logn time

vikashperiwal — Wed, 27 Nov 2019 12:40:39 GMT

Sorry but the one you suggested changes our requirement.

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

Re: query running using KV store is taking logn time

arjunpkishore5 — Wed, 27 Nov 2019 13:21:10 GMT

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_configuration_of_an_automatic_KV_Store_lookup

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