Splunk Search

## Timechart on a rate calculated by 2 sourcetype (monthly)

Observer

Hi all,

I have a problem creating a time chart based on calculations of 2 values from different source-type. Let me put this below:

Source type A: It stores all customers in certain service, and each of the customer will be given an unique indicator call bsn. Each of the entry was given with a same _time, i.e. 2020-08-01 00:00. The # of rows could represent # of customer in August, say it is num2.

Source type B: It stores all the maintenance order received from the pool of customers in source-type A. In general, i will need to count # of customer in specific requirement (which say he is male) has put an order in august. For this number, it is num1

So the idea is want to create a time chart, to show the order rate (num1/num2) by each month accordingly.

However as it involves different source type, while their _time is quite different from one another. I got problem on solving this. Please help

Labels (2)

• ### timechart

SplunkTrust

It sounds like your source type 'A' is actually a lookup for customers, where you're only interested in the customer details from A

I am assuming your search range would be 'August' for the query below

``````sourcetype=a OR sourcetype=b
| eventstats sum(eval(if(sourcetype=a,1,0))) as totalAugustCustomers
| where sourcetype=a
| now do the aggregations as needed for your logic``````

if you just need that count for August then the above will get the count and then remove all other sourcetype a information.

If you are using a different time range for the order search then you'd have to do something different, but need more information about what data you need from A and B

Observer

Thank you for the prompt reply. I bet the code

`eventstats sum(eval(if(sourcetype=a,1,0))) as totalAugustCustomers`

will get me the total # of customers in sourcetype A.

May I also know why you need to put this  inside the code as well?

`where sourcetype=a`

If we follow the same way to get my # in sourcetype B, say it is called totalAugustOrder

How can we put these 2 figures into a timechart, while i can do it separately on monthly basis?

(Since if I choose from 01Jul to 31Aug, i guess the system will simply sum up all orders and TotalCustomer, instead of indicating which number comes from July and August

SplunkTrust

``````sourcetype=a OR sourcetype=b
| bin _time span=1mon
| eventstats sum(eval(if(sourcetype=a,1,0))) as totalCustomers
| where sourcetype=a
| ... other processing logic to get your grouping ...
| timechart span=1mon values(totalCustomers) as totalCustomers count
| eval rate=access/totalCustomers*100
| fields _time rate``````

Is this what you need - i.e. you get the total customers by month and then the timechart. After the timechart, you just calculate the rate and get rid of the counters.

Hope this helps

Get Updates on the Splunk Community!