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!

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Admin Console: A Single, Unified Interface for All Your Cloud Admin Needs

WATCH NOWJoin us to learn how the admin console can save you time and give you more control over the Splunk® ...