Help with mvcombine needed

[damucka]

Hello,

I have the following case:
- In my SPL, based on the output of the dbx SQL queries executed over the map command, I am building the column LINE, which basically has result of the SQLs. Now, I want to present this output in the alert text. For this I need to address the LINE over $result.LINE$. The issue is, that I am getting only the first row of LINE column into the alert result.
My workaround for that is that I am using mvcombine over the LINE column, which assembles all rows together into one, works fine.
The only problem I have is, that the emtpty rows (well, they consist of many space characters) get chopped off by the mvcombine. Unfortunately I need them because I use them as the text formatting for the alert text.

How would I overcome the issue?
below the SPL and the screenshot of the result with and without mvcombine

Kind Regards,
Kamil

|makeresults

| eval SID = "BWP"
| eval HOST = "ls5923"

| eval SQL = "CPU_HEADLINE"
| lookup datafetch_sql_texts2.csv SQL output SQL_TEXT as CPU_HEADLINE
| eval SQL = "CPU_HEADLINE_BWP"
| lookup datafetch_sql_texts2.csv SQL output SQL_TEXT as CPU_HEADLINE_BWP
| eval SQL = "CPU_DEATILS_BWP"
| lookup datafetch_sql_texts.csv SQL output SQL_TEXT as CPU_DEATILS_BWP
| eval SQL = "MEM_HEADLINE"
| lookup datafetch_sql_texts2.csv SQL output SQL_TEXT as MEM_HEADLINE
| eval SQL = "MEM_BWP"
| lookup datafetch_sql_texts.csv SQL output SQL_TEXT as MEM_BWP

| eval CPU_HEADLINE_BWP = replace(CPU_HEADLINE_BWP,"x22", "\"")
| eval CPU_HEADLINE_BWP = replace(CPU_HEADLINE_BWP,"ML_HOST", HOST)
| eval CPU_HEADLINE_BWP = replace(CPU_HEADLINE_BWP,"ML_SID", SID)

| eval CPU_DEATILS_BWP = replace(CPU_DEATILS_BWP,"x22", "\"")
| eval CPU_DEATILS_BWP = replace(CPU_DEATILS_BWP,"ML_HOST", HOST)
| eval CPU_DEATILS_BWP = replace(CPU_DEATILS_BWP,"ML_SID", SID)

| eval MEM_BWP = replace(MEM_BWP,"x22", "\"")
| eval MEM_BWP = replace(MEM_BWP,"ML_HOST", HOST)
| eval MEM_BWP = replace(MEM_BWP,"ML_SID", SID)

 |rename comment AS " *********************************** Evaluate the RCA ***********************************************  "
| eval RCA = "Missing Data MEMORY_USED , CPU"
| eval CPU_HEADLINE_BWP = if(like(RCA, "%CPU%"), CPU_HEADLINE_BWP, "")
| eval CPU_HEADLINE = if(like(RCA, "%CPU%"), CPU_HEADLINE, "")
| eval CPU_DEATILS_BWP = if(like(RCA, "%CPU%"), CPU_DEATILS_BWP, "")
| eval MEM_HEADLINE = if(like(RCA, "%MEMORY_USED%"), MEM_HEADLINE, "")
| eval MEM_BWP = if(like(RCA, "%MEMORY_USED%"), MEM_BWP, "")


 |rename comment AS " *********************************** Set table with SQL Queries to Execute ***********************************************  "

 | table [|makeresults |  eval search ="CPU_HEADLINE CPU_HEADLINE_BWP CPU_DEATILS_BWP MEM_HEADLINE MEM_BWP" | table search ] 
 | transpose 
 | rename "row 1" AS RCA_SQL_TEXT 
 | table RCA_SQL_TEXT

| where isnotnull(RCA_SQL_TEXT)
| map maxsearches=20 search="dbxquery query=\"$RCA_SQL_TEXT$\" connection=\"HANA_MLBSO\" "
| mvcombine LINE

Result without mvcombine, empty lines (spaces) are there before and after the CPU line:

Result with mvcombine, empty lines are skipped, but I need them for the formatting of the $result.LINE$ in my alert text ...

[woodcock · Accepted solution]

Can you not just swap out | mvcombine LINE for | stats list(LINE) AS LINE BY some other fields maybe here?

[woodcock]

We need the contents of the datafetch_sql_texts2.csv file.

[to4kawa]

| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"-30m@m"), relative_time(_time,"@m"))
| makecontinuous span=1m
| eval tmp="----------------------------------------------------------------------"
| streamstats count
| eval value = if(count % 2 == 1,substr(tmp,random() % 5 + 1,random() % 60 + 5) ," ")
| table value
| mvcombine value

Hi, Changing NULL to blank seems to solve the problem

 base search 
 | eval LINE = NULL
 | map maxsearches=20 search="dbxquery query=\"$RCA_SQL_TEXT$\" connection=\"HANA_MLBSO\" "
 | eval LINE = if(isnull(LINE), " ",LINE)
 | mvcombine LINE

How about it?

[damucka]

@to4kawa

Not sure if I understood ...
I implemented:

| eval LINE = if(isnull(LINE), " ",LINE)

but it did not help. Also in your example, the space lines that are in the initial table get skipped by mvcombine, they are not there in the output.

Could you advise?

[to4kawa]

Changing to blank when LINE field is NULL

If you look closely at the example query, you will see a blank line.

I am correcting the previous answer.

[damucka]

Sorry, still not getting it ...

I execute your example, first without mvcombine - there I can clearly see the empty lines in between, then with mvcombine - then the empty lines are gone, or I can't see them at least.

For my case - the empty lines are not NULL lines, they are series of space characters. Therefore I am not sure whether the "if" statement would replace anything. Also, I do not see any change after I implemented your suggestion

[to4kawa]

value
-----------------------------------

----------------

-----------------------------------------

---------------------------------------------------------

----------------------------------------------------

-----------------------------------------------------------

-------------------------------------------------------------

--------------------

----------------

--------------------------------------------------------

-------

--------------------------------------

--------

------------------------------

--------------------------------------------------------

-----------------------------------------

my sample query result, there is the empty line.

| eval LINE = if(isnotnull(LINE),LINE," ")

how about it?

[damucka]

Strange, I copy pasted your output into the notepad and I cannot see the empty lines in-between.

Also in my case it did not bring anything, the beginning of my output looks as follows after mvcombine:

******************************* CPU *************************************
41% avg DB CPU utilization on BWP ls5928. Avg Statement Count is 1758 per second. 

No empty line between the headline and text. This means the mvcombine skips these lines.

Also, I am not sure if the suggested code:

| eval LINE = if(isnotnull(LINE),LINE," ")

changes anything, because in my case the empty lines are the strings consisting of several space characters (" "), which I would expect to be interpreted as not NULL by the above. Therefore the above line does not change anything.

[to4kawa]

| mvcombine delim="

" LINE
| nomv LINE

how about it?