Performance issue on dashboard : Base search query and data model acceleration assistance required

gopiven — Wed, 30 Sep 2020 02:43:46 GMT

Hello Experts

Actually I am trying to show the usage trends across one application on different platforms (Online, Mobile & other platforms) as different trends as 30 days, 7 days and 24 hrs trends.

Here are the details:

There are 3 indexes 1a,2b and 3c with many source types.
index=1a (ONLINE PLATFORM)
In index=1a the field ( say "ClientId" which I required is directly there I am doing the lookup against the file. ( since in the index 1a, both userid and clientId fields are there I Can evaluate the Userid and then join the ClientId through the lookup.
Source types are sourcetype="ONLINE_ACTIVITYLOG"

index=2b (other platform)
But in index=2b, I have to evaluate the field "Userid" from different source types and do input lookup and join the "ClientId" from the same input lookup.
Source types are :

sourcetype="PROD_APPLOG",HTTP_USER,  
sourcetype="PROD_APPLOG",UserID,  
sourcetype="PROD_APPLOG",userId,  
sourcetype="PROD_APPLOG",usrLogin,    
sourcetype="PROD_APPLOG",http_user,  
sourcetype="PROD_APPLOG",user_cookie,  
sourcetype="PROD_APPLOG",userID,

sourcetype="PROD1_APPLOG",Http_User,  
sourcetype="PROD1_APPLOG",prod_USER,

sourcetype="PROD_WEBLOG",HTTP_USER,  
sourcetype="PROD_WEBLOG",user_cookie,  
sourcetype="PROD_WEBLOG",userID, 

sourcetype=="F5_APPLOG",http_user,  
sourcetype=="F5_APPLOG",user_cookie,

index=3c (MOBILE PLATFORM)
Source types are:
sourcetype="MOBILE_WEBLOG",HTTP_USER,
sourcetype="MOBILE_APPLOG",user_cookie

Inputlookup Filename: UserId.csv
Inputlookup file format: 
Userid Clientid
User1 Client1
User2 Client2

As mentioned, When I tried to show the trend for 30 days,7 days & 24 hrs (across 12 panels in one dashboard) - the data is not at all loading and performance is very slow.
When I verified with few of my Engineering colleagues, they said "I am searching the same query in multiple panels on the dashboard that causing slowness and asking me to CREATE a BASE SEARCH and use that to draw the trend as required"

As I am fairly new to splunk,

I am confused how to create a base search for this issue since it is across multiple indexes.
Also is the data model & search base query concepts are same?
And they are asking me to accelerate the search once created the base query

***.

Could you please help me to create search base query for above issue.

ACTUAL QUERY which I am using across all the panels in the dashboard:

index= "1a"  OR index="2b"  OR index="3c"

|  eval Platform = case(
index="1a", "Online",
index="2b", "Mobile",
index="3c", "OtherPlatforms")

|  eval Userid= case(  
sourcetype="PROD_APPLOG",HTTP_USER,  
sourcetype="PROD_APPLOG",UserID,  
sourcetype="PROD_APPLOG",userId,  
sourcetype="PROD_APPLOG",usrLogin,    
sourcetype="PROD_APPLOG",http_user,  
sourcetype="PROD_APPLOG",user_cookie,  
sourcetype="PROD_APPLOG",userID,

sourcetype="PROD1_APPLOG",Http_User,  
sourcetype="PROD1_APPLOG",prod_USER,

sourcetype="PROD_WEBLOG",HTTP_USER,  
sourcetype="PROD_WEBLOG",user_cookie,  
sourcetype="PROD_WEBLOG",userID, 

sourcetype=="F5_APPLOG",http_user,  
sourcetype=="F5_APPLOG",user_cookie,

sourcetype="ONLINE_ACTIVITYLOG" AND  ACTIVITY_CATEGORY=="{signin}",USR_LOGIN,

sourcetype="MOBILE_WEBLOG",HTTP_USER,
sourcetype="MOBILE_APPLOG",user_cookie)

| lookup Userid.csv Userid AS Userid output Clientid 

| stats dc(Clientid) as total_clients by date_hour,date_wday,Platform | chart avg(Clientid) over date_hour by Platform

only the "| stats dc(Clientid) as total_clients by date_hour,date_wday,Platform | chart avg(Clientid) over date_hour by Platform" -> this part is varying across all panels as I am showing as chart(avg) & dc etc.,

Re: Performance issue on dashboard : Base search query and data model acceleration assistance required

gopiven — Mon, 04 Nov 2019 01:32:57 GMT

Could someone help me on this please?

topic Performance issue on dashboard : Base search query and data model acceleration assistance required in Splunk Search

Performance issue on dashboard : Base search query and data model acceleration assistance required

Re: Performance issue on dashboard : Base search query and data model acceleration assistance required