Splunk Search

Not able to generate timechart from a multivalue field

twh1
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

woodcock
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

woodcock
Esteemed Legend

You have to put this in there somewhere:

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

FrankVl
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

twh1
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

schose
Builder

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

twh1
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

FrankVl
Ultra Champion

In your table there is also values like that?

0 Karma

twh1
Communicator

hi @FrankVl
In table i am getting proper result.

0 Karma

FrankVl
Ultra Champion

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

0 Karma

twh1
Communicator

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

0 Karma

FrankVl
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

twh1
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
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...