topic Re: Not able to generate timechart from a multivalue field in Splunk Search

Not able to generate timechart from a multivalue field

twh1 — Wed, 30 Sep 2020 01:18:34 GMT

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.

Re: Not able to generate timechart from a multivalue field

FrankVl — Sun, 14 Jul 2019 10:11:13 GMT

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.

Re: Not able to generate timechart from a multivalue field

twh1 — Sun, 14 Jul 2019 10:18:44 GMT

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.

Re: Not able to generate timechart from a multivalue field

schose — Sun, 14 Jul 2019 11:01:59 GMT

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

Re: Not able to generate timechart from a multivalue field

twh1 — Sun, 14 Jul 2019 11:56:07 GMT

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.

Re: Not able to generate timechart from a multivalue field

FrankVl — Mon, 15 Jul 2019 07:04:05 GMT

In your table there is also values like that?

Re: Not able to generate timechart from a multivalue field

twh1 — Mon, 15 Jul 2019 09:03:30 GMT

hi @FrankVl
In table i am getting proper result.

Re: Not able to generate timechart from a multivalue field

FrankVl — Mon, 15 Jul 2019 10:45:56 GMT

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

Re: Not able to generate timechart from a multivalue field

twh1 — Mon, 15 Jul 2019 14:28:02 GMT

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

Re: Not able to generate timechart from a multivalue field

FrankVl — Mon, 15 Jul 2019 14:51:32 GMT

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

Re: Not able to generate timechart from a multivalue field

woodcock — Mon, 15 Jul 2019 15:24:23 GMT

You have to put this in there somewhere:

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

Re: Not able to generate timechart from a multivalue field

twh1 — Tue, 16 Jul 2019 03:32:04 GMT

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

Re: Not able to generate timechart from a multivalue field

woodcock — Sat, 27 Jul 2019 23:09:28 GMT

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_*