Not able to generate timechart from a multivalue field

[twh1]

I am getting my input in json format like below,

{"message":{"SID":"DEV","TIMESTAMP":1563095600,"PARAMS":[{"PROC_CODE":"10110","PROC_VALUE":"                                                                                                                                                                                                                                                             2","SYS_NAME":"ALL"},{"PROC_CODE":"10010","PROC_VALUE":"20190712","SYS_NAME":"sapbcsdev_DEV_00"},{"PROC_CODE":"10020","PROC_VALUE":"125853","SYS_NAME":"sapbcsdev_DEV_00"},{"PROC_CODE":"10030","PROC_VALUE":"9","SYS_NAME":"sapbcsdev_DEV_00"},{"PROC_CODE":"10040","PROC_VALUE":"1","SYS_NAME":"sapbcsdev_DEV_00"}

I am printing this value in table by using below query.

index="test_data" sourcetype="SAP:data" | rename message.PARAMS{}.PROC_CODE as PROC_CODE, message.PARAMS{}.PROC_VALUE as PROC_VALUE, message.PARAMS{}.SYS_NAME as SYS_NAME, message.SID as SID, message.TIMESTAMP as TIMESTAMP
| eval TIMESTAMP=strftime(TIMESTAMP, "%Y-%m-%d %H:%M:%S")
| eval mvf1 = mvzip(PROC_CODE, PROC_VALUE, ";") | eval mvf2 = mvzip(mvf1, SYS_NAME, ";") 
| mvexpand mvf2 | eval n=split(mvf2,";") 
| eval PROC_CODE=mvindex(n,0), PROC_VALUE=mvindex(n,1), SYS_NAME=mvindex(n,2) 
| lookup PROC_DETAIL PROC_CODE OUTPUT PROC_CODE PROC_NAME PROC_PARA PROC_TYPE 
| search SYS_NAME="*" PROC_TYPE=* PROC_PARA=*
| table TIMESTAMP SID SYS_NAME PROC_TYPE PROC_PARA PROC_CODE PROC_NAME PROC_VALUE

Below is the output of this query.

TIMESTAMP   SID SYS_NAME    PROC_TYPE   PROC_PARA   PROC_CODE   PROC_NAME   PROC_VALUE
2019-07-14 08:48:20 DEV ALL KPI ALL 10110   Number of App Servers   2
2019-07-14 08:48:20 DEV sapbcsdev_DEV_00    INFO    INSTANCE    10010   INSTANCE START DATE 20190712
2019-07-14 08:48:20 DEV sapbcsdev_DEV_00    INFO    INSTANCE    10020   INSTANCE START TIME 125853
2019-07-14 08:48:20 DEV sapbcsdev_DEV_00    KPI INSTANCE    10030   Workprocess Dia Active Count    9
2019-07-14 08:48:20 DEV sapbcsdev_DEV_00    KPI INSTANCE    10040   Workprocess Upd Active Count    1

Now when I am trying to publish some trent using timechart like avg/ min/ max on PROC_VALUE, i am not getting proper output. I assume that still PROC_VALUE is behaving like multi value field.

[woodcock]

Just add this to the bottom of your existing search:

... | eval PROC_NAME = "PROC_NAME_" . PROC_NAME
| eval {PROC_NAME} = PROC_VALUE
| eval _time = strptime(TIMESTAMP, "%Y-%m-%d %H:%M:%S")
| timechart min(PROC_NAME_*) AS min_* max(PROC_NAME_*) AS max_* avg(PROC_NAME_*) AS avg_*

Here is a run-anywhere PoC:

| makeresults 
|  eval raw="TIMESTAMP=2019-07-14T08:48:20,SID=DEV,SYS_NAME=ALL,PROC_TYPE=KPI,PROC_PARA=ALL,PROC_CODE=10110,PROC_NAME=Number_of_App_Servers,PROC_VALUE=2 TIMESTAMP=2019-07-14T08:48:20,SID=DEV,SYS_NAME=sapbcsdev_DEV_00,PROC_TYPE=INFO,PROC_PARA=INSTANCE,PROC_CODE=10010,PROC_NAME=INSTANCE_START_DATE,PROC_VALUE=20190712 TIMESTAMP=2019-07-14T08:48:20,SID=DEV,SYS_NAME=sapbcsdev_DEV_00,PROC_TYPE=INFO,PROC_PARA=INSTANCE,PROC_CODE=10020,PROC_NAME=INSTANCE_START_TIME,PROC_VALUE=125853 TIMESTAMP=2019-07-14T08:48:20,SID=DEV,SYS_NAME=sapbcsdev_DEV_00,PROC_TYPE=KPI,PROC_PARA=INSTANCE,PROC_CODE=10030,PROC_NAME=Workprocess_Dia_Active_Count,PROC_VALUE=9 TIMESTAMP=2019-07-14T08:48:20,SID=DEV,SYS_NAME=sapbcsdev_DEV_00,PROC_TYPE=KPI,PROC_PARA=INSTANCE,PROC_CODE=10040,PROC_NAME=Workprocess_Upd_Active_Count,PROC_VALUE=1"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv
| rex field=TIMESTAMP mode=sed "s/T/ /"
| eval PROC_NAME = "PROC_NAME_" . PROC_NAME
| eval {PROC_NAME} = PROC_VALUE
| eval _time = strptime(TIMESTAMP, "%Y-%m-%d %H:%M:%S")
| timechart min(PROC_NAME_*) AS min_* max(PROC_NAME_*) AS max_* avg(PROC_NAME_*) AS avg_*

[woodcock]

You have to put this in there somewhere:

... | search NOT "PROC_NAME"="Instance Start*" | ...

[FrankVl]

Can you perhaps share the actual query incl. the timechart attempt and explain the output you get and how that is not what you want?

To make the timechart command work, you need to have an _time field, containing an epoch timestamp.

[twh1]

Hi @FrankVl
I am using below query for timechart.

index="test_data" sourcetype="SAP:data" | rename message.PARAMS{}.PROC_CODE as PROC_CODE, message.PARAMS{}.PROC_VALUE as PROC_VALUE, message.PARAMS{}.SYS_NAME as SYS_NAME, message.SID as SID, message.TIMESTAMP as TIMESTAMP
| eval TIMESTAMP=strftime(TIMESTAMP, "%Y-%m-%d %H:%M:%S")
| eval mvf1 = mvzip(PROC_CODE, PROC_VALUE, ";") | eval mvf2 = mvzip(mvf1, SYS_NAME, ";") 
| mvexpand mvf2 | eval n=split(mvf2,";") 
| eval PROC_CODE=mvindex(n,0), PROC_VALUE=mvindex(n,1), SYS_NAME=mvindex(n,2) 
| lookup PROC_DETAIL PROC_CODE OUTPUT PROC_CODE PROC_NAME PROC_PARA PROC_TYPE 
| search SYS_NAME="*" PROC_TYPE=* PROC_PARA=* PROC_CODE=10130
| timechart span=5m avg(PROC_VALUE) as "Average Value"

I need avg/max/min of PROC_VALUE over the time.

[schose]

Hi,

 index="test_data" sourcetype="SAP:data"
| rename message.PARAMS{}.PROC_CODE as PROC_CODE, message.PARAMS{}.PROC_VALUE as PROC_VALUE, message.PARAMS{}.SYS_NAME as SYS_NAME, message.SID as SID, message.TIMESTAMP as TIMESTAMP 
| eval TIMESTAMP=strftime(TIMESTAMP, "%Y-%m-%d %H:%M:%S") 
| eval mvf1 = mvzip(PROC_CODE, PROC_VALUE, ";") 
| eval mvf2 = mvzip(mvf1, SYS_NAME, ";") 
| mvexpand mvf2 
| eval n=split(mvf2,";") 
| eval PROC_CODE=mvindex(n,0), PROC_VALUE=mvindex(n,1), SYS_NAME=mvindex(n,2)
| eval _time=strptime(TIMESTAMP, "%Y-%m-%d %H:%M:%S")
| timechart span=5m min(PROC_VALUE) avg(PROC_VALUE) max(PROC_VALUE)

should do the job

[twh1]

Hi @schose
I have already tried above query but it's showing me wrong output.
with table command I am getting value in PROC_VALUE like 2,1, 0, 2 but avg is coming like 20190712, which is wrong output.

[FrankVl]

In your table there is also values like that?

[twh1]

hi @FrankVl
In table i am getting proper result.

[FrankVl]

In the table you posted in your question I also see those high values in the PROC_VALUE column.

[twh1]

Hi @FrankVl
I want to create chart only for specific PROC_CODE.

[FrankVl]

Can you then please share the output of the relevant table command, incl. the filter. Because now it is rather confusing.

[twh1]

Hi @FrankVl
Below is the output in table format. I want timechart based on PROC_VALUE field. (like avg/ min/ max)

TIMESTAMP   SID SYS_NAME    PROC_TYPE   PROC_PARA   PROC_CODE   PROC_NAME   PROC_VALUE
2019-07-14 15:13:20 DEV ALL KPI INSTANCE    10130   User Count  4
2019-07-14 15:13:20 DEV ALL KPI INSTANCE    10130   User Count  2
2019-07-14 15:08:20 DEV ALL KPI INSTANCE    10130   User Count  4
2019-07-14 15:08:20 DEV ALL KPI INSTANCE    10130   User Count  2
2019-07-14 15:03:20 DEV ALL KPI INSTANCE    10130   User Count  4
2019-07-14 15:03:20 DEV ALL KPI INSTANCE    10130   User Count  2
2019-07-14 14:58:20 DEV ALL KPI INSTANCE    10130   User Count  4