I have the following working Query for a single product AHSDFKSD1
ns=a* DECISION IN (ELIGIBLE, INELIGIBLE)
PRODUCT IN (AHSDFKSD1)
| timechart span=24h limit=0 count by DECISION
| eval total= ELIGIBLE+INELIGIBLE
| eval ELIGIBLE=round(ELIGIBLE/total,4)*100
| eval INELIGIBLE=round(INELIGIBLE/total,4)*100
| fields - total
Output
_time ELIGIBLE INELIGIBLE
2020-05-25 17:00 87.93 12.07
How can I modifying this query to output data per product? (Or even a totally different query if output is as follows)
Example I could have over 20 products AHSDFKSD1, GFAGDAYD2, GSDAUFCBE3, IGAGSDASHD4, GASDAHJDSGDA5 ........
I am looking for following output:
PRODUCT _time ELIGIBLE INELIGIBLE
AHSDFKSD1 2020-05-25 17:00 87.93 12.07
GFAGDAYD2 2020-05-25 17:00 80.03 19.97
GSDAUFCBE3 2020-05-25 17:00 87.90 12.10
IGAGSDASHD4 2020-05-25 17:00 92.93 7.07
How can I achieve this? Please assist. Thanks.
Here's one way -
ns=a* DECISION IN (ELIGIBLE, INELIGIBLE)
PRODUCT=*
| bin _time span=24h
| stats count as daycount by _time PRODUCT DECISION
| eventstats sum(daycount) as totcount by _time PRODUCT
| rename COMMENT as "This assigns the calculated value to a new variable with the name that is in the field DECISION"
| eval {DECISION} = round(daycount/totcount,4)
| stats max(ELIGIBLE) as ELIGIBLE max(INELIGIBLE) as INELIGIBLE by _time PRODUCT
If it's possible that you might get zero transactions for a particular product in a 24 hour period, then you might want to add this to get the zeroes.
ns=a* DECISION IN (ELIGIBLE, INELIGIBLE)
PRODUCT=*
| bin _time span=24h
| stats count as daycount by _time PRODUCT DECISION
| rename COMMENT as "This adds a zero record for each time period, each product and each decision"
| appendpipe [
| rename _time as Time
| stats values(Time) as Time values(PRODUCT) as PRODUCT values(DECISION) as DECISION
| mvexpand Time
| mvexpand PRODUCT
| mvexpand DECISION
| eval daycount=0
]
| eventstats sum(daycount) as totcount by _time PRODUCT
| eval totcount=if(totcount=0,1,totcount)
| rename COMMENT as "This assigns the calculated value to a new variable with the name that is in the field DECISION"
| eval {DECISION} = round(daycount/totcount,4)
| stats max(ELIGIBLE) as ELIGIBLE max(INELIGIBLE) as INELIGIBLE by _time PRODUCT
Hi,
Try using the bin command
ns=a* DECISION IN (ELIGIBLE, INELIGIBLE)
|bin _time AS "TIME" span=1h
| convert ctime(TIME)
| stats count by TIME,DECISION,PRODUCT
| eval total= ELIGIBLE+INELIGIBLE
| eval ELIGIBLE=round(ELIGIBLE/total,4)*100
| eval INELIGIBLE=round(INELIGIBLE/total,4)*100
| fields - total