Splunk Search
Highlighted

Joining search in Splunk using Ticket Number Field

Explorer

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.

 

Labels (3)
Tags (1)
0 Karma
Highlighted

Re: Joining search in Splunk using Ticket Number Field

SplunkTrust
SplunkTrust
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?
---
If this reply helps you, an upvote would be appreciated.
Highlighted

Re: Joining search in Splunk using Ticket Number Field

Explorer

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

0 Karma
Highlighted

Re: Joining search in Splunk using Ticket Number Field

SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

Highlighted

Re: Joining search in Splunk using Ticket Number Field

Explorer

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

0 Karma
Highlighted

Re: Joining search in Splunk using Ticket Number Field

SplunkTrust
SplunkTrust
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. 🙂
---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: Joining search in Splunk using Ticket Number Field

Explorer

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.

0 Karma
Highlighted

Re: Joining search in Splunk using Ticket Number Field

SplunkTrust
SplunkTrust
Do you try my updated query?
---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: Joining search in Splunk using Ticket Number Field

Explorer

Thank you so much, it works.

 

I really appreciate your kindness.

0 Karma