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!
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
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
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