Joining search in Splunk using Ticket Number Field

[leymandudu]

Greetings, I am new to Splunk and I have an assignment where I needed to extract data based on ticket number and time stamp for "Add Task" and "Resolve".

A ticket contains both comment from inception to completion.

Here is an example of my code;

index=sperf_default source=prod.system.btds.ticket.updated.preproc (EB FIX VERIFY/DENY) activity_type="ADD TASK"
| join ticket_number type=inner [ search index=sperf_default source=prod.system.btds.ticket.updated.preproc activity_type="resolve" ] 

Thank you.

[richgalloway]

What is your question? You have a query. Does it not produce the desired results? What results do you get? What results do you expect?

[leymandudu]

Thanks Galloway,

The query is not giving me expected result when exported to excel.

What I am trying to achieve is the following,

Ticket Number      Activity_Type    Time                                         Activity_Type                   Time                             Status

2222000022          Add Task        1/2/2020 15:12:45                  Resolve                 2/1/2020 12:12:12          Closed

I want to be able to calculate the time difference between when the Task is added and when it is resolved.

Thanks

[richgalloway · Accepted solution]

Why export to Excel?  Splunk can calculate the difference.

index=sperf_default source=prod.system.btds.ticket.updated.preproc ((EB FIX VERIFY/DENY) activity_type="ADD TASK") OR (activity_type="resolve")
| stats values(*) as *, range(_time) as diff by ticket_number
| eval diff=tostring(diff, "duration")

[leymandudu]

Thanks for your quick response.

Although the query was able to calculate the difference but it grouped all the ticket into a single field.

What I want is to calculate the difference for each ticket for a specified period.

Thanks

[richgalloway]

Sorry about that. I have updated my answer to group results by ticket number.
You shouldn't have accepted the answer if it didn't solve your problem. 🙂

[leymandudu]

Really appreciate your kindness and timeliness.

Like I said, today is my first day of using this resources and splunk environment.

Now, I understand the function of "accept answer",  but nevertheless, I'd appreciate if you can assist on how to view the time difference by each ticket.

Thanking you in anticipation of your response.

[richgalloway]

Do you try my updated query?

[leymandudu]

Thank you so much, it works.

I really appreciate your kindness.