topic Re: help with where / append needed in Splunk Search

help with where / append needed

damucka — Mon, 30 Mar 2020 15:36:51 GMT

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

Re: help with where / append needed

richgalloway — Mon, 30 Mar 2020 16:00:22 GMT

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.

Re: help with where / append needed

damucka — Mon, 30 Mar 2020 18:24:46 GMT

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