How to subtract _time field from Multifield value

[zhonk]

Hello I have a search with an MV Value this is called HeartBeatTime. I like to create an allert when the HeartBeatTime is over 5 Minute. My question is how can I get the time diff about _time and HeartBeatTime?

Here is my search:


index=temp host="ctw-prod-qa"
| rex max_match=5 "serviceUserName=\"(?[^\"])"
| rex max_match=5 "serviceIPAddress=\"(?[^\"])"
| rex max_match=5 "serviceStartupTime=\"(?[^\"])"
| rex max_match=5 "serviceStatus=\"(?[^\"])"
| rex max_match=5 "serviceHeartBeatTime=\"(?[^\"])"
| eval User_Number = mvcount(UserName)
| eval TimeDiff=_time - strptime(HeartBeatTime,"%Y-%m-%d %H:%M:%S.%3N")
| table _time,UserName,Status, HeartBeatTime,TimeDiff, IPAddress, User_Number
| eval final_User_Number=if(isnotnull(User_Number),User_Number,0)


[zhonk · Accepted solution]

Hi,
it works now, with a little trick.

With mvzip I create one field with all Multivalues and after that I expand it. So I get for each UserName one line and can make the Diff calc.
Here is my new Code:

index=temp host="ctw-prod-qa"
| rex max_match=5 "serviceUserName=\"(?[^\"])"
| rex max_match=5 "serviceStatus=\"(?[^\"])"
| rex max_match=5 "serviceIPAddress=\"(?[^\"])"
| rex max_match=5 "serviceHeartBeatTime=\"(?[^\"])"
| rex max_match=5 "serviceStartupTime=\"(?[^\"])"
| eval HeartBeatTime=if(isnotnull(HeartBeatTime),HeartBeatTime,"1970-01-01 01:00:00.000")

| eval User_Number = mvcount(UserName)
| eval final_User_Number=if(isnotnull(User_Number),User_Number,0)
| eval Feld1 = mvzip(UserName,HeartBeatTime)

| eval Feld1 = mvzip(Feld1,Status)
| eval Feld1 = mvzip(Feld1,IPAddress)
| eval Feld1 = mvzip(Feld1,Startuptime)
| mvexpand Feld1
| rex field=Feld1 "(?\w*),(?\S*\s*\S*),(?\w*),(?\S*),(?\S*\s*\S*)"

| eval Time=_time
|eval heartbeaepoch=strptime(HeartBeatTime,"%Y-%m-%d %H:%M:%S.%3N")
| eval TimeDiff=Time - heartbeaepoch
| table _time, UserName,Startuptime , HeartBeatTime,Status,IPAddress,TimeDiff, User_Number, final_User_Number

Thanks

[richgalloway]

@zhonk If your problem is resolved then please accept an answer to help future readers.

[to4kawa]

What's your Splunk version?
some multivalue function need version 8.

[zhonk]

Hi @to4kawa our splunk version is 7.3.4.

[to4kawa]

your |eval TimeDiff=_tmie - sp... can't work, because HeartBeatTime is multivalue.
Correcting query is difficult, will you provide logs?

[harishalipaka]

hi @zhonk

Updated my Answer Please try like below @zhonk

|makeresults |eval HeartBeatTime="2020-04-16 12:23:32.9",heartbeatEpoch=strptime(HeartBeatTime,"%Y-%m-%d %H:%M:%S.%3N"),Time=_time|eval TimeDiff=Time-heartbeatEpoch,diff=tostring(TimeDiff,"Duration")

[zhonk]

Hi @harishalipaka i have change the code but the field TimeDiff is empty.

[harishalipaka]

@zhonk

Did you check individually -- query debug

are u getting Heart Beat epoch and _time epoch converting or not..