topic Re: Search Performance issue - Need help in All Apps and Add-ons

Search Performance issue - Need help

gopiven — Wed, 30 Sep 2020 03:05:08 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: Search Performance issue - Need help

HiroshiSatoh — Mon, 18 Nov 2019 06:36:09 GMT

In your example, base search is a technique that performs 12 searches once.

In base search, the search is performed in the most detailed unit. Aggregate each panel as needed.

As a restriction, if the number of base searches is large, it may be faster to search for each panel.

BASE SEARCH:
(your search)|bin span=24h _time|stats count by _time,your_category

PANEL01:
|bin span=7d _time|stats sum(count) as count by _time,your_category

PANEL02:
|bin span=30d _time|stats sum(count) as count by _time,your_category

<dashboard>
   <search id="baseSearch">
     <query>(your search)|bin span=24h _time|stats count by _time,your_category</query>
   </search>
   <label>test</label>
   <row>
     <panel>
       <chart>
         <search base="baseSearch">
           <query>|bin span=7d _time|stats sum(count) as count by _time,your_category</query>
         </search>
         <option ･･････
       </chart>
     </panel>
   </row>
   <row>
     <panel>
       <chart>
         <search base="baseSearch">
           <query>|bin span=30d _time|stats sum(count) as count by _time,your_category</query>
         </search>
         <option ･･････
       </chart>
     </panel>
   </row>
 </dashboard>

Re: Search Performance issue - Need help

siddharthkhatsu — Wed, 30 Sep 2020 03:00:12 GMT

Answering one by one,
1) how would you make a Base search for this issue?
Lets see here the sub-query that is common across all queries will return you raw events, so to create base search i would recommend to table out the fields you would require as the base search is executed once and the results are stored in memory, so it will be better to store only the fields we would require and not all the unnecessary data which might affect the performance. so here after you use lookup to fetch the required values add
.. | table Clientid,date_wday,date_hour,Platform add any other fields you might require and make it a base search and here is how

Add a search with id above in the form <search id=”base_search_id”><query> whatever query we made earlier</query></search>
Now use the base search in your chart by referencing it <search base=”base_search_id”><query> the remaining serach query</query></search>
Example 1st search index=main source=demo_source | table fielda,fieldb| stats count(fielda) by fieldb 2nd search index=main source=demo_source | table fieldc,fieldd | stats count(fieldc) by fieldd base search will be <search id=”base_search_id”><query>index=main source=demo_source | table fielda,fieldb,fieldc,fieldd </query></search> chart 1 will have <search base=”base_search_id”><query>stats count(fielda) by fieldb</query></search> chart 2 will have <search base=”base_search_id”><query>stats count(fieldc) by fieldd</query></search>

2) is the data model & search base query concepts are same?
NO, Data models are used to map the fields_names with a common field name. Like the data might have username, USER, user_name, uname and many other of that sort but we know it all refers to the same username, so the data model allows us to map all these names to a common know or a standard name for a specific field.
please refer here for full details link
P.S. you cannot use the same query after making a data model
and also acceleration is also a feature of data model it creates an accelerated index for the selected data which makes searching faster. You can read about it more here

Suggestion
`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)is same ascase(
sourcetype="PROD_APPLOG",HTTP_USER,

sourcetype="PROD1_APPLOG",Http_User,

sourcetype="PROD_WEBLOG",HTTP_USER,

sourcetype=="F5_APPLOG",http_user,

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

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

as if the condition is matched it doesnt look for the next condition. so if sourcetype="PROD_APPLOG" than userid will always be HTTP_USER and not any other field you mentioned after that. I Hope you got it.

Re: Search Performance issue - Need help

gopiven — Thu, 21 Nov 2019 04:58:24 GMT

Thanks Sid!
I am understanding it better now.

However I have scheduled search ( running every hour) which creates output lookup file and from that I am drawing the trends now.
The 7 days trends working fine.
However I am not able to segregate the last 24hrs events ( mean I dont know how to filter last 24hrs events to draw the trend).
Kindly help me how to filter the last 24hrs trend from overall lookup ( which contains 7 days data).

Thanks in advance!