Splunk Search

Timechart on a rate calculated by 2 sourcetype (monthly)

tabbit2002
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)
0 Karma

bowesmana
SplunkTrust
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

 

0 Karma

tabbit2002
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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...