topic Re: How to convert Hourly Stats into daily? in Splunk Search

How to convert Hourly Stats into daily?

varunghai — Tue, 30 Jan 2018 10:50:28 GMT

Hi,
I am a Splunk User and been using it for a few months now,

I have created a query which creates a table of count of orders of different payment types.

index=idx sourcetype=order "successfully created order" "[paymentinfo]" | chart count AS PayPal by _time span=1h | join type=outer _time [search index=tomcat sourcetype=order "successfully created order" "[StoreCardPaymentInfo]" | chart count AS StoreCard by _time span=1h] | join type=outer _time [search index=tomcat sourcetype=order "successfully created order" "[GiftCard]" | chart count AS GiftCard by _time span=1h ] | join type=outer _time [search index=tomcat sourcetype=order "successfully created order"  Card_type!=NULL | timechart count(Card_type) by Card_type limit=0 span=1h | rename amex as Amex, maestro as Maestro, master as Mastercard, visa as VISA, diners as Diners] | fillnull

It currently lists the hourly report of the orders

If I want to make that report daily instead of hourly, I added | eval Date=strftime(_time, "%d-%m-%Y") | fields- _time | table Date VISA PayPal Mastercard Maestro GiftCard StoreCard Amex and replaced time span by 1d from 1h, but the issue here is that it doesnt reflect the total count throughout the day, it only shows the total count for the first payment method i.e. PayPal which is the first in the multiple joins so I am guessing it has something to do with the joins

And also the query I have written seems to work fine, is there anything I can change in it or improve?
Please help!

Re: How to convert Hourly Stats into daily?

cmerriman — Tue, 30 Jan 2018 13:10:39 GMT

try combining your searches and using timechart instead of just chart. there might be a way to get rid of that last join, but it's going to take some thinking.

(index=idx sourcetype=order "successfully created order" "[paymentinfo]" ) OR (index=tomcat sourcetype=order "successfully created order" ("[StoreCardPaymentInfo]" OR "[GiftCard]") ) 
|eval PayPal=if(index=idx,1,null())
|eval StoreCard=if(like(_raw,"%StoreCardPaymentInfo%"),1,null())
|eval GiftCard=if(like(_raw,"%GiftCard%"),1,null())
|timechart span=1d count(PayPal) as PayPal count(StoreCard) as StoreCard count(GiftCard) as GiftCard
| join type=left _time 
    [ search index=tomcat sourcetype=order "successfully created order" Card_type!=NULL 
    | timechart count(Card_type) by Card_type limit=0 span=1d 
    | rename amex as Amex, maestro as Maestro, master as Mastercard, visa as VISA, diners as Diners] 
| fillnull

Re: How to convert Hourly Stats into daily?

varunghai — Wed, 31 Jan 2018 11:30:34 GMT

khool, thanks! it helped i will try to get rid of the join and make it a decent query 🙂