Splunk Search

How to get percentage of values for a field based on total number from different search

fullstackdev
New Member

Hi,

I have been pulling my hair to get this to work, but couldn't, and any help would be very much appreciated.

I have a set of events created for when tickets are created. One of field is created time like this: 2019-08-26T18:20:08.930Z

I have another set of events created for when some type of query is made for ticket, and it includes time when the ticket was originally created.

I would like to create a table of percentage of type of queries made from total number of orders created on the date.

For example, ticket events are like the following:
{"event":"ticket_created","ticket_id": "id_1", "created": "2019-08-26T18:20:08.930Z"},
{"event":"ticket_created","ticket_id": "id_2", "created": "2019-08-26T18:20:08.930Z"},
{"event":"ticket_created","ticket_id": "id_3", "created": "2019-08-26T18:20:08.930Z"},

And query events would be like this:
{"event":"query","query_type":"type1","ticket_id": "id_1", "ticket_created": "2019-08-26T18:20:08.930Z"},
{"event":"query","query_type":"type2","ticket_id": "id_2", "ticket_created": "2019-08-26T18:20:08.930Z"},

And table I am trying to create (from which visualization can be created):
Date type1 type2
2019-08-26 33% (1 out of 3 tickets) 33% (1 out of 3 tickets)
2019-08-27 N% M%
2019-08-28 I% J%

So, far I was only able to generate just total numbers (query types by converted date appended with total ticket count by converted date). I can't seem to figure out how to dynamically divide sum of types divided by total number of tickets grouped by converted date.

The following is the query I did, and it generates a table like the following:
sourcetype="sourcetype" event="query"
| eval ticketCreated=strptime(created_at, "%Y-%m-%dT%H:%M:%S.%QZ")
| eval ticketCreatedDate=strftime(ticketCreated, "%Y-%m-%d")
| chart count by ticketCreatedDate, query_type
| appendcols [search sourcetype="sourcetype" event="ticket_created"
| eval ticketCreated=strptime(ticket_created, "%Y-%m-%dT%H:%M:%S.%QZ")
| eval ticketCreatedDate=strftime(ticketCreated, "%Y-%m-%d")
| stats count as ticketCount by ticketCreatedDate]

Date type1 type2 ticketCount
2019-08-26 1 1 3

Any help would be much, much appreciated.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

What you need is a foreach command.

Try something like this (with little modifications to optimize the search

sourcetype="sourcetype" event="query" 
| eval ticketCreatedDate=replace(created_at, "*.+)T.+","\1")  
| chart count by ticketCreatedDate, query_type 
| appendcols [search sourcetype="sourcetype" event="ticket_created" 
| eval ticketCreatedDate=replace(ticket_created, "*.+)T.+","\1")   
| stats count as ticketCount by ticketCreatedDate]
| foreach * [| eval "<<FIELD>>"=if("<<FIELD>>"="ticketCount" OR "<<FIELD>>"="ticketCreatedDate" , '<<FIELD>>' , tostring(round('<<FIELD>>'*100/ticketCount))."%") ]

Updates to existing query: reduced two evals to one (date is already in the format you need to extract the data part from created date)

Foreach command: Your search result before foreach will have fields ticketCount and ticketCreatedDate and one field for each of ticket type. So, foreach looks at each field, it does nothing if field name is ticketCount or ticketCreatedDate, but for all other fields, it calculates the percentage using ticketCount field.

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

What you need is a foreach command.

Try something like this (with little modifications to optimize the search

sourcetype="sourcetype" event="query" 
| eval ticketCreatedDate=replace(created_at, "*.+)T.+","\1")  
| chart count by ticketCreatedDate, query_type 
| appendcols [search sourcetype="sourcetype" event="ticket_created" 
| eval ticketCreatedDate=replace(ticket_created, "*.+)T.+","\1")   
| stats count as ticketCount by ticketCreatedDate]
| foreach * [| eval "<<FIELD>>"=if("<<FIELD>>"="ticketCount" OR "<<FIELD>>"="ticketCreatedDate" , '<<FIELD>>' , tostring(round('<<FIELD>>'*100/ticketCount))."%") ]

Updates to existing query: reduced two evals to one (date is already in the format you need to extract the data part from created date)

Foreach command: Your search result before foreach will have fields ticketCount and ticketCreatedDate and one field for each of ticket type. So, foreach looks at each field, it does nothing if field name is ticketCount or ticketCreatedDate, but for all other fields, it calculates the percentage using ticketCount field.

0 Karma

fullstackdev
New Member

@somesoni2 You are a savior! Thank you so much!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Can you share the query using which you were able to generate total numbers?

0 Karma

fullstackdev
New Member

@somesoni2 I've updated the question with a search and subsearch I created.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...