Timechart on a rate calculated by 2 sourcetype (monthly)

[tabbit2002]

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

[bowesmana]

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

[tabbit2002]

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

[bowesmana]

Perhaps I misunderstood your question

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