topic Re: Can I create a KPI in ITSI that is a sum of two other KPIS? in Splunk ITSI

Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Mon, 22 Oct 2018 19:46:40 GMT

I have to admit, there are certain aspects of ITSI I find limiting.
For example: I need to create KPI which the sum of two fields in the last 5 minutes, say field foo and bar.
I can specify I want the sum of either of those individually as a metic but I cannot specify that I want the sum of both of them combined into one KPI.
Is there a way to do this that anyone is aware of?

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

skoelpin — Mon, 22 Oct 2018 20:22:42 GMT

What's the purpose here?

Are you wanting to create a KPI with the summed values then display it on a glasstabe/deep dive view?

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Tue, 29 Sep 2020 21:43:52 GMT

The purpose is to create a KPI of the sum of the two other KPIs and then display it in deep dive or possibly a glass table, yes.

I think I kind of found a work around but I guess backfill doesnt work for it.

sourcetype=sourcet1 xyz | eventstats sum(calls_kpi1) as calls1 sum(calls_kpi2) as calls2 | eval calls_proc = calls1 + calls2

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Wed, 24 Oct 2018 15:30:35 GMT

@skoelpin
I've also found that when I use the query above to create a KPI base search, the preview for the Thresholds is all out of whack and I cannot set it properly. The value range is showing in Deep Dive as between 11 - 44 and yet the values shown in the preview for Thresholds are in the millions, which makes no sense.

This is pretty discouraging as I am being asked to convert a report into an ITSI dashboard and it requires more complex queries than just summing one KPI or taking the average or another single KPI. Was ITSI just not designed for anything other than basic statistical analysis on single KPIs?

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

skoelpin — Wed, 24 Oct 2018 19:11:29 GMT

The preview will show a sample rather than 1:1 results. Did you use a transformational command in your search? If so then this could be the culprit why it's not showing up correctly in the preview.

How frequently are you running your KPI searches? You should recreate your desired output using the itsi_summary and see what the results are so you can verify its getting written to ITSI correctly. Once you confirm this, you can then start narrowing down the problem. As for complex analysis, I have some pretty complicated queries and ITSI is handling it good

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Wed, 24 Oct 2018 19:25:51 GMT

Interesting. So you have queries that include eventstats and evals and it does backfill and allows for thresholding? Im very curious to know because this will allow me to remove "not a function of ITSI" from my reasons for why this isn't working.

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Tue, 29 Sep 2020 21:47:49 GMT

Also you asked if I used a transformation in my search. This was my search so I think yes: "sourcetype=cpu SCP1_CPU | stats sum(SCP1_CPU) as sum_scp1 sum(SCP2_CPU) as sum_scp2 sum(SCP3_CPU) as sum_scp3 sum(SCP4_CPU) as sum_scp4 | eval avg_scp_cpu = (sum_scp1 + sum_scp2 + sum_scp3 + sum_scp4) / 4"

So I think I am using transformations.

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Tue, 29 Sep 2020 21:47:52 GMT

I will admit I struggle to understand how itsi_summary helps me to recreate the query to verify the results.
index=itsi_summary kpi="SCP CPU Utilization"
That returns an event such as:
10/24/2018 19:32:57 +0000, search_name="Indicator - Shared - 5bcf77972b9d44157e79157f - ITSI Search", search_now=1540409580.000, info_min_time=1540409277.000, info_max_time=1540409577.000, info_search_time=1540409582.235, qf="", kpi="SCP CPU Utilization", kpiid=80e03fe65ca6fb18fccd8fc4, urgency=5, serviceid="1e9057dc-4f5d-4abf-a773-e85349dd8a84", itsi_service_id="1e9057dc-4f5d-4abf-a773-e85349dd8a84", is_service_aggregate=1, is_entity_in_maintenance=0, is_entity_defined=0, entity_key=service_aggregate, is_service_in_maintenance=0, kpibasesearch=5bcf77972b9d44157e79157f, alert_color="#99D18B", alert_level=2, alert_value=11, itsi_kpi_id=80e03fe65ca6fb18fccd8fc4, is_service_max_severity_event=1, alert_severity=normal, alert_period=1, entity_title=service_aggregate

After digging through that I am confused on what I can extract from it to help me understand why Im not able to create thresholds for this query. Which of these fields is the value for that kpi at that time?

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Tue, 29 Sep 2020 21:47:55 GMT

Ah, using itsi_summary, I was able to find the alert_value and it is matching the values that I see in the Deep Dive results. This means the query is working but the preview I see in the Threshold preview is way off for some reason. The values for the alert_value are between 11-13 and the values in the Threshold preview are all 22265 for some reason and I have values for yesterday so I would expect them to be reflected here. Anything else in the itsi_summary you can think of that I could figure out why this is happening? (note: I have other kpis I created a week or so ago which are also using eventstats and eval functions that do show an accurate threshold preview map)
Could I just need to wait longer to see it in the itsi threshold preview map?

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

skoelpin — Wed, 24 Oct 2018 21:10:50 GMT

Correct, I have tons of base searches and adhoc searches that use eval and eventstats which backfills successfully. You should get rid of that reporting command stats and replace it with eventstats like this. Once you do this, your preview will be readable

sourcetype=cpu SCP1_CPU 
| eventstats sum(SCP1_CPU) as sum_scp1 sum(SCP2_CPU) as sum_scp2 sum(SCP3_CPU) as sum_scp3 sum(SCP4_CPU) as sum_scp4 
| eval avg_scp_cpu = (sum_scp1 + sum_scp2 + sum_scp3 + sum_scp4) / 4

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

skoelpin — Wed, 24 Oct 2018 21:15:53 GMT

Yes, you should use itsi_summary and rebuild what you're trying to accomplish in ITSI. If you're getting the same values as you would over the raw data then it's working as expected. It should look like this

index=itsi_summary kpi=<YOUR KPI VALUE> 
| timechart span=5m avg(KPI VALUE)

This assumes you set a 5 minute span for KPI's to report. This will then build a timechart of what you should see in ITSI. You can also run the search over the raw values (your adhoc/base search) and compare the output to your itsi_summary output

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Tue, 29 Sep 2020 21:47:57 GMT

Ugh I copied the wrong one here.
Here is the one I am using. I have one I am using and seeing the unusual behavior for:
sourcetype=cpu SCP1_CPU | eventstats sum(SCP1_CPU) as sum_scp1 sum(SCP2_CPU) as sum_scp2 sum(SCP3_CPU) as sum_scp3 sum(SCP4_CPU) as sum_scp4 | eval avg_scp_cpu = (sum_scp1 + sum_scp2 + sum_scp3 + sum_scp4) / 4

I tried to create one with stats hoping it would work but the one with stats didnt return anything at all.

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Wed, 24 Oct 2018 21:20:25 GMT

Thank you for this query. Yes, when I put that in for itsi_summary, I see values ranging from 11-13 as expected and as I see in Deep Dive. This is not reflected in the preview for ITSI Thresholds. But I think I can set a static threshold since they the range is so small and see if it will at least work that way.
I am going to need Splunk professional services to troubleshoot with me why I cannot get a backfill from eventstats or eval queries I think.

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Wed, 24 Oct 2018 21:31:55 GMT

Sort of good news. Even though I could not see the preview in Thresholding, I can apply it and see it reflected in the Deep Dive so I can at least use Thresholding.

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

skoelpin — Wed, 24 Oct 2018 21:47:40 GMT

I used to do professional services and the ones experienced with ITSI are few and far between, it could take you months to get one on site. You could file a support case and they can help you out too.

Tomorrow I will take the same formatted query and backfill it on my system and let you know how it goes

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

skoelpin — Tue, 29 Sep 2020 21:45:27 GMT

UPDATE: I took your format and successfully backfilled the itsi_summary index for 7 days. One thing to note is you should use a separate function for each eventstats line. It should look like this below. If you look at the SPL prior to putting it into ITSI, you will see avg_scp_cpu isn't created because you're missing fields. Your format is only pulling the last function scp4 and not creating the new field because its missing fields. If you follow the format below, it works because there is an avg_scp_cpu field which makes the backfill possible.

sourcetype=cpu SCP1_CPU 
| eventstats sum(SCP1_CPU) as sum_scp1 
| eventstats sum(SCP2_CPU) as sum_scp2 
| eventstats sum(SCP3_CPU) as sum_scp3 
| eventstats sum(SCP4_CPU) as sum_scp4 
| eval avg_scp_cpu = (sum_scp1 + sum_scp2 + sum_scp3 + sum_scp4) / 4

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Tue, 29 Sep 2020 21:48:26 GMT

Thats very odd because when I run my query, I see the avg_scp_cpu field in the Interesting Fields column when I run my query:
sourcetype=cpu SCP1_CPU | eventstats sum(SCP1_CPU) as sum_scp1 sum(SCP2_CPU) as sum_scp2 sum(SCP3_CPU) as sum_scp3 sum(SCP4_CPU) as sum_scp4 | eval avg_scp_cpu = (sum_scp1 + sum_scp2 + sum_scp3 + sum_scp4) / 4

in a regular search so it is creating the avg_scp_cpu field for me. I also see in there fields for sum_scp1, sum_scp2, sum_scp3, and sum_scp4 in the Interesting fields so it getting those as well... Im not sure what you are talking about.

I will try your new version with 4 eventstats to see if the backfill works for me.

Did you see threshold previews for your new query as well?

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Thu, 25 Oct 2018 17:18:06 GMT

Well I copied your query into an Ad-hoc KPI and did backfill of 7 hours and it filled it in with data that was all in the 3000s whereas the range for the data is 11-13. So it must be calculating it wrong somehow.

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

EricLloyd79 — Thu, 25 Oct 2018 17:20:02 GMT

What did you use your your Calculation when creating the KPI? I used Maximum since its already doing the calculation in the query itself...

Re: Can I create a KPI in ITSI that is a sum of two other KPIS?

skoelpin — Thu, 25 Oct 2018 17:23:18 GMT

I used Average.. You should recreate this in the search prior to backfilling in ITSI

Once you get the desired results, you should then consume into ITSI. Best way to do it is to append a timechart span=1m max(KPI value) to the end of your search.