Splunk Search

How can I refine these queries?

tkwaller_2
Communicator

Hello

I have a dashboard that has 6 panels on it. It seems this dashboard is causing an issue with CPU on loading as it uses 5 CPUs at over 100% each. Some of these queries are huge, containing joins, mvfields/commands etc. Here is an example search from one of the panels:

Heres an example of a chart that lives on this dashboard, this takes probably the longest out of all of them

index=fp_dev_tsv info_owner_orgID="NameHere" md_type="services" |
rename info_name as RRNAME
| rex mode=sed field="related_vendors" "s/[][]//g"
| rex mode=sed field="related_vendors" "s/'//g"
| makemv delim="," related_vendors
| eval RV = mvindex(related_vendors,0)
|join RV [ search index=fp_dev_tsv md_type=vendors info_owner_deptTechnical = "*"|rename id as RV | rename info_name as VendorName]
| eval RR_Score = 
case(data_rPS=1000, "s/'//g",
data_rPS=750 AND data_rPS=999, ",",
data_rPS=500 AND data_rPS=749, "Critical", 
data_rPS=100 AND data_rPS=499, "High", 
data_rPS=99, "Medium") | dedup RRNAME
| rename info_owner_deptTechnical AS LOB
| fillnull value=None RR_Score
| chart count(RRNAME) over LOB by RR_Score

Heres an example of a table that lives on this dashboard

index=fp_dev_tsv md_type=vendors |rename id as RV | rename info_name as VendorName

| rex mode=sed field="related_eS" "s/[][]//g"
| rex mode=sed field="related_eS" "s/'//g"
| makemv delim="," related_eS
| eval RE=mvcount(related_eS)

| rex mode=sed field="related_services" "s/[][]//g"
| rex mode=sed field="related_services" "s/'//g"
| makemv delim="," related_services
| eval RS=mvcount(related_services)

| rex mode=sed field="related_aM" "s/[][]//g"
| rex mode=sed field="related_aM" "s/'//g"
| makemv delim="," "related_aM"
| eval RA=mvcount(related_aM)

| join RV [ search index=fp_dev_tsv info_owner_orgID="NameHere" md_type="services" |
rename info_name as RRNAME
| rex mode=sed field="related_vendors" "s/[][]//g"
| rex mode=sed field="related_vendors" "s/'//g"
| makemv delim="," related_vendors
| eval RV = mvindex(related_vendors,0)]

| eval RR_Score = 
case(data_rPS=1000, "Critical",
data_rPS=750 AND data_rPS=999, "High",
data_rPS=500 AND data_rPS=749, "Medium", 
data_rPS=100 AND data_rPS=499, "Low", 
data_rPS=99, "Nominal")
|dedup RV, RRNAME
| fillnull value=Nominal RR_Score
| rename info_owner_deptBusiness as "Business Department" info_owner_deptTechnical as "LOB" data_stakeholders_business_name as "Business Stakeholder Name" data_stakeholders_business_email as "Business Stakeholder Email"  VendorName AS Vendor 
| table Vendor RR_Score "LOB" "Business Department" "Business Stakeholder Name" "Business Stakeholder Email" RS RE RA
|rename  RA as "AC" RS as "RRCount" RE as "ESC" RR_Score AS "Vendor RR"
| dedup Vendor

As I said crazy queries that include a lot of data and spans the last 30 days on each.
Can anyone advise on the best way to tweak these types of queries for performance?

I appreciate all the help!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

General Rules -

1) List each field you need from each type of record.
2) Pull all the records in a single time at a single pass through all the indexes.
3) Use fields to throw away everything you don't need, as soon as possible.
4) dedup as early as possible, to throw away unneeded records. Use keepempty=true to dedup only the records that match all the listed dedup fields.
4) You can do multiple sed commands at the same time on the same field, just put one or more space between them.
5) Your RR_Score code makes no sense, because data_rPS can only have one value at a time, so it will never equal both 750 and 999, for example. We've added greater or lesser to make it viable code, but the values above 750 are still nonsensical.

For example, try this... it should scream relative to your other code. VendorName is not technically needed for the chart, so delete the line that calculates it, and the stats for it, unless you have another reason to keep it around.

index=fp_dev_tsv (md_type="services" info_owner_orgID="NameHere") 
             OR  (md_type="vendors" info_owner_deptTechnical = "*")
| fields info_name related_vendors id info_owner_deptTechnical data_rPS
| eval RRNAME = case(md_type="services", info_name)
| dedup RRNAME keepempty=true
| rename info_owner_deptTechnical as LOB
| eval VendorName = case(md_type="vendors", info_name)
| rex mode=sed field=related_vendors "s/[][]//g  s/'//g"
| makemv delim="," related_vendors
| eval RV = case(md_type="services",mvindex(related_vendors,0), 
                 md_type="vendors" ,id)
| eval RR_Score =  case(data_rPS>=1000, "s/'//g",
                    data_rPS>=750 AND data_rPS<=999, ",",
                    data_rPS>=500 AND data_rPS<=749, "Critical", 
                    data_rPS>=100 AND data_rPS<=499, "High", 
                    data_rPS<=99, "Medium") 
| stats min(_time) as _time, 
        max(RR_Score) as RR_Score, 
        max(RRNAME) as RRNAME, 
        max(VendorName) as VendorName, 
        max(LOB) as LOB 
        BY RV
| fillnull value=None RR_Score
| chart count(RRNAME) over LOB by RR_Score
0 Karma

tkwaller_2
Communicator

Hello
Sorry it took a bit to come back to, I have been testing with a simple portion of this:

index=fp_dev_tsv (md_type="services" info_owner_orgID="NameHere") 
                  OR  (md_type="vendors" info_owner_orgID="NameHere" info_owner_deptTechnical = "*")
                  OR (md_type=engagementScopes info_owner_orgID="NameHere")
    | fields info_name related_vendors related_services id info_owner_deptTechnical data_rPS related_eS
    | eval RRNAME = case(md_type="services", info_name)
    |table RRNAME

The table however returns no results saying no fields exist.

Thanks
Todd

0 Karma

tkwaller_2
Communicator

A bit more info, it seems that the "fields" portion is causing an issue. Maybe because in md_type=services and md_type=vendors both have a field titled info_name with different values?

If I take out the fields clause adn do something like this:

| eval RRNAME = case(md_type="services", info_name)
| table RRNAME

If I do this I get no results:

| fields info_name related_vendors related_services id info_owner_deptTechnical data_riskProfileScore related_engagementScopes
| eval RRNAME = case(md_type="services", info_name)
| eval VendorName = case(md_type="vendors", info_name)
|table VendorName RRNAME
0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!