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
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
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?
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.
Sorry but the one you suggested changes our requirement.
We are trying to use automatic lookups , so that we can enhance data faster