topic Re: How to subtract _time field from Multifield value in Splunk Search

How to subtract _time field from Multifield value

zhonk — Wed, 30 Sep 2020 04:58:23 GMT

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)

Re: How to subtract _time field from Multifield value

harishalipaka — Fri, 17 Apr 2020 08:36:14 GMT

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")

Re: How to subtract _time field from Multifield value

zhonk — Fri, 17 Apr 2020 08:52:03 GMT

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

Re: How to subtract _time field from Multifield value

to4kawa — Fri, 17 Apr 2020 10:53:05 GMT

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

Re: How to subtract _time field from Multifield value

zhonk — Fri, 17 Apr 2020 11:31:38 GMT

Hi @to4kawa our splunk version is 7.3.4.

Re: How to subtract _time field from Multifield value

to4kawa — Fri, 17 Apr 2020 11:35:19 GMT

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

Re: How to subtract _time field from Multifield value

harishalipaka — Fri, 17 Apr 2020 11:35:21 GMT

@zhonk

Did you check individually -- query debug

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

Re: How to subtract _time field from Multifield value

zhonk — Wed, 30 Sep 2020 05:02:16 GMT

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

Re: How to subtract _time field from Multifield value

richgalloway — Fri, 17 Apr 2020 12:05:26 GMT

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