Splunk Search

help with where / append needed

Contributor

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

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, an upvote would be appreciated.

View solution in original post

0 Karma

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, an upvote would be appreciated.

View solution in original post

0 Karma

Contributor

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!