topic Re: How to get percentage of values for a field based on total number from different search in Splunk Search

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

fullstackdev — Thu, 29 Aug 2019 16:54:44 GMT

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.

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

somesoni2 — Thu, 29 Aug 2019 17:51:42 GMT

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

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

fullstackdev — Thu, 29 Aug 2019 18:06:28 GMT

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

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

somesoni2 — Thu, 29 Aug 2019 18:38:35 GMT

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.

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

fullstackdev — Thu, 29 Aug 2019 18:47:22 GMT

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