Splunk Search

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

gopiven
Explorer

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.,

0 Karma

gopiven
Explorer

Could someone help me on this please?

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...