Splunk Search

help with where / append needed

damucka
Builder

Hello,

I need help with what I thought will be easy: I need to execute the 2-nd select depending on the result of the 1-st select and append the results 2-nd to 1-st.

|dbxquery query="select top 1  m.host, s.service_name, round(m.used_memory_size/1024/1024/1024) as used_memory_size_gb , round(i.memory_allocation_limit/1024/1024/124) as memory_allocation_limit_gb, to_int(round(m.used_memory_size * 100 / i.memory_allocation_limit )) percent
from isp_iop_kpistore.sys.m_service_component_memory as m
join (select host, port, MEMORY_ALLOCATION_LIMIT from isp_iop_kpistore.sys.M_LOAD_HISTORY_SERVICE where time >= add_seconds(now(), -60)) as i
on m.host = i.host and m.port = i.port
join isp_iop_kpistore.sys.m_services as s
on i.host = s.host and i.port = s.port
where m.component = 'Statement Execution & Intermediate Results'
and m.used_memory_size >= i.memory_allocation_limit * 0.01
order by round(m.used_memory_size/1024/1024/1024) desc
" connection="HANA_MLBSO"

|where PERCENT > 20
|append [
 | dbxquery query="
 select now() from dummy
/*select  top 100
component,
category, 
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 1) as "TYPE_1",
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 2) as id_1,
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 3) as "TYPE_2",
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 4) as id_2,
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 5) as "TYPE_3",
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 6) as id_3,
sum(round(inclusive_allocated_size/1024/1024/1024,2)) as allocated_gb,
sum(round(inclusive_size_in_use/1024/1024/1024, 2)) as used_gb
from isp_iop_kpistore.sys.m_context_memory
group by component,
category, 
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 1) ,
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 2) ,
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 3),
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 4) ,
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 5) ,
SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 6) 
order by used_gb desc */
 " connection="HANA_MLBSO"]

Somehow there is no way I can force it to work as I want. When the where condition is met, then yes, I get both results appended. However, when the where the condition is false, still the second SQL gets executed, the results of the first are erased and what I get is only the result of the second SQL.
I guess I make some conceptual mistake here.

Could you please advise?

Kind Regards,
Kamil

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The where statement in SPL filters events. It doesn't control the execution of later SPL statements. There is no means for that. See https://ideas.splunk.com.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The where statement in SPL filters events. It doesn't control the execution of later SPL statements. There is no means for that. See https://ideas.splunk.com.

---
If this reply helps you, Karma would be appreciated.
0 Karma

damucka
Builder

Thank you, it helped my understanding.
This is not the first time that I miss the real if-else functionality in Splunk SPL, but I guess I am spoiled by other programming languages and this is not that easy to implement here.

I solved above in relatively dirty way with the map command

|dbxquery query="select top 1  m.host, s.service_name, round(m.used_memory_size/1024/1024/1024) as used_memory_size_gb , round(i.memory_allocation_limit/1024/1024/124) as memory_allocation_limit_gb, to_int(round(m.used_memory_size * 100 / i.memory_allocation_limit )) percent
from isp_iop_kpistore.sys.m_service_component_memory as m
join (select host, port, MEMORY_ALLOCATION_LIMIT from isp_iop_kpistore.sys.M_LOAD_HISTORY_SERVICE where time >= add_seconds(now(), -60)) as i
on m.host = i.host and m.port = i.port
join isp_iop_kpistore.sys.m_services as s
on i.host = s.host and i.port = s.port
where m.component = 'Statement Execution & Intermediate Results'
and m.used_memory_size >= i.memory_allocation_limit * 0.01
order by round(m.used_memory_size/1024/1024/1024) desc
" connection="HANA_MLBSO" 

| eval QUERY = "select  top 100 component, category, SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 1) as \"TYPE_1\", SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 2) as id_1, SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 3) as \"TYPE_2\", SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 4) as id_2, SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 5) as \"TYPE_3\", SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 6) as id_3, sum(round(inclusive_allocated_size/1024/1024/1024,2)) as allocated_gb, sum(round(inclusive_size_in_use/1024/1024/1024, 2)) as used_gb from isp_iop_kpistore.sys.m_context_memory group by component, category, SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 1) , SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 2) , SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 3), SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 4) , SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 5) , SUBSTRING_REGEXPR('[^/]+' IN category FROM 1 OCCURRENCE 6)  order by used_gb desc"

| eval DUMMY_QUERY = "select now() from dummy"
| eval SQL_TEXT = if(PERCENT >= 20, QUERY, "")

| map maxsearches=20 search="dbxquery query=\"$SQL_TEXT$\" connection=\"HANA_MLBSO\" 
       | append[|makeresults
       | eval HOST=\"$HOST$\" 
       | eval MEMORY_ALLOCATION_LIMIT_GB=\"$MEMORY_ALLOCATION_LIMIT_GB$\" 
       | eval PERCENT=\"$PERCENT$\" 
       | eval SERVICE_NAME = \"$SERVICE_NAME$\" 
       | eval USED_MEMORY_SIZE_GB = \"$USED_MEMORY_SIZE_GB$\"
       ] "

Kind Regards,
Kamil

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...