Splunk Search

How to calculate the difference between two timestamps to get the duration of a video call?

murat89
New Member

Hi guys,

im a beginner in Splunk and my issue is that I have Cisco logs and I need to find out the conference duration but there is no field like duration so I have to make it through timestamps.
Below you can see that kind of log and I don't know how to get the timestamps and then calculate the difference between them, please help, im thankful for any idea.

Just a part of Cisco log:
2814 2018/01/22 09:56:39.008 APP Info conference "Terminal 1" created
2846 2018/01/22 12:01:30.213 APP Info conference "Terminal 1": deleted via API (no participants)

Tags (1)
0 Karma

niketn
Legend

@murat89, based on the sample data provided please try the following run anywhere search.

PS: First 5 pipes from makeresults to rename are used to generate the mock data. Also while I have extracted _time using rex, you might need the rex command from APP Info conference onward as your data will have timestamp extracted already.

| makeresults
| eval data="2814 2018/01/22 09:56:39.008 APP Info conference \"Terminal 1\" created;2846 2018/01/22 12:01:30.213 APP Info conference \"Terminal 1\": deleted via API (no participants)"
| makemv data delim=";" 
| mvexpand data
| rename data as _raw
| rex "\d{4}\s(?<_time>\d{4}\/\d{2}\/\d{2}\s\d{2}:\d{2}:\d{2}.\d{3})\sAPP Info conference\s\"(?<id>[^\"]+)\"(\s|\:)+(?<status>\w+)"
| eval _time=strptime(_time,"%Y/%m/%d %H:%M:%S.%3N")
| stats first(_time) as _time last(_time) as EndTime values(status) as status by id
| search status=created AND status=deleted
| eval duration=EndTime-_time
| fields - EndTime
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

somesoni2
Revered Legend

Best (in terms of performance) is to use stats on a field (or group of fields), preferably a primary key which is common between both type of events (conference start and end) and can uniquely identify the conference. For example, if there is field call conference_id in your logs, you can do something like this

index=YourIndex sourcetype=YourCiscoSourcetype (conference created) OR (conference deleted)
| eval confStart=if(searchmatch("conference created"),"_time,null())
| eval confEnd=if(searchmatch("conference deleted"),"_time,null())
| stats values(confStart) as confStart values(confEnd) as confEnd by conference_id
| eval "duration(in secs)"=confEnd-confStart
0 Karma

murat89
New Member

Thank you, great solution, i really appreciate that. Unfortunately there is no conference_id but we do have the conference name, here it is "Terminal 1". How to do with that?

0 Karma

starcher
Influencer

Extract the data where the name is to a field called conference_name and change out the by conference_id.

0 Karma

murat89
New Member

I have never created a field in Splunk, I know how to create an event type, a field seems little different to me.

0 Karma
Get Updates on the Splunk Community!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...