Splunk Search

How to get evaluated result for every item in list

angersleek
Path Finder

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

dindu
Contributor

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
0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...