Splunk Search

How to convert Hourly Stats into daily?

varunghai
Engager

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!

Tags (2)
0 Karma
1 Solution

cmerriman
Super Champion

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

View solution in original post

0 Karma

cmerriman
Super Champion

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

varunghai
Engager

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...