Splunk Search

Not able to generate timechart from a multivalue field

Communicator

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.

0 Karma

Esteemed Legend

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

Esteemed Legend

You have to put this in there somewhere:

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

Ultra Champion

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.

0 Karma

Communicator

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.

0 Karma

Contributor

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

0 Karma

Communicator

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.

0 Karma

Ultra Champion

In your table there is also values like that?

0 Karma

Communicator

hi @FrankVl
In table i am getting proper result.

0 Karma

Ultra Champion

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

0 Karma

Communicator

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

0 Karma

Ultra Champion

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

0 Karma

Communicator

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
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!