Splunk Search

How to Write Case/If Statement for Certain vs All (*) Values

genesiusj
Builder

Hello,

This is a difficult one to explain. Best to show the code and the intended outcomes. Note, there are 7+ possible values, but I will use only 2 for clarity.

 

index=mf SYSNAME=mf MFSOURCETYPE=SMF110 TRAN !="C*" OAPPLID=* 
| eval TransactionSpeed=(SUSPTIME_MICROSEC + USRCPUT_MICROSEC) 
| eval avgTransactionSpeedSec=round(avgTransactionSpeed/1000000,5) 
| eval OAPPLID=if( like(OAPPLID,"CI0%"),OAPPLID,"ALL") 
| eval upper_limit=case(OAPPLID="CI04JPAD",.1,OAPPLID="CI04JPAF",.2,OAPPLID="ALL",1.5) 
| eval lower_limit1=upper_limit/3 
| eval lower_limit2=(upper_limit/3)*2 
| stats avg(TransactionSpeed) AS avgTransactionSpeed, values(upper_limit) AS upper_limit, values(lower_limit1) AS lower_limit1, values(lower_limit2) AS lower_limit2 
| gauge avgTransactionSpeedSec 0 lower_limit1 lower_limit2 upper_limit

 

 When OAPPLID is a single value ("CI04JPAD" or "CI04JPAF") the upper_limit is set to .1 or .2 respectively.

Screenshot - 6_19_2020 , 8_01_53 AM.pngScreenshot - 6_19_2020 , 8_04_02 AM.png

When OAPPLID is set to * (all possible values) the upper_limit should be set to 1.5. instead, upper_limit is set to 100.

Screenshot - 6_19_2020 , 8_00_09 AM.png

Stay safe and healthy you and yours.

God bless,
Genesius

Labels (1)
0 Karma

Richfez
SplunkTrust
SplunkTrust

I am not sure where the `100` is coming from, but normally I include a fall-through statement in case statements, that will always be true and relies on no condition.

 eval upper_limit=case(OAPPLID="CI04JPAD",.1,OAPPLID="CI04JPAF",.2,true(),1.5) 

Give that `true()` a try!

0 Karma

genesiusj
Builder

@Richfez 

Thanks. I did not know about the true() function. Learned something new. 😀

However, results still ending up with '100'. 🤔

Thanks and God bless,
Genesius

0 Karma

Richfez
SplunkTrust
SplunkTrust

Intriguing.  There's nowhere a '100' could sneak in at.

What do you get when you do something like

index=mf SYSNAME=mf MFSOURCETYPE=SMF110 TRAN !="C*" OAPPLID=* 
| eval TransactionSpeed=(SUSPTIME_MICROSEC + USRCPUT_MICROSEC) 
| eval avgTransactionSpeedSec=round(avgTransactionSpeed/1000000,5) 
| eval OAPPLID=if( like(OAPPLID,"CI0%"),OAPPLID,"ALL") 
| eval upper_limit=case(OAPPLID="CI04JPAD",.1,OAPPLID="CI04JPAF",.2,OAPPLID="ALL",1.5) 
| where upper_limit>2
| table upper_limit, OAPPLID

What is the value of OAPPLID where upper_limit ends up being 100?  (I filter to where it's >2, since all values should be between 0.1 and 1.5).

Also just to confirm, you could change ".1" to "0.1", and you could even put that in quotes and see if the behavior changes.

Let me know what you find!

Tags (1)
0 Karma

genesiusj
Builder

@Richfez 

Thanks for your reply.

| where upper_limit>2   No results.

I removed the above command, with these results.

Screenshot - 6_19_2020 , 2_33_15 PM.png

OAPPLID is never set to "ALL".

Thanks and God bless,
Genesius

0 Karma

genesiusj
Builder

I think I might need to provide more context.

In the search, there are 8 possible values for OAPPLID, selected from a drop-down input on the dashboard.

CI04JPAD
CI04JPAF
CI004JMS
CI004MAL
CI04JPDG
CI00400Z
CI04JPTE
*

If * is selected on the drop-down this means any combination of one or more of those 7 values. 

 

<search code>
| eval upper_limit=case(OAPPLID="CI04JPAD",.1,OAPPLID="CI04JPAF",.2,OAPPLID="CI004JMS",.3,OAPPLID="CI004MAL",.4,OAPPLID="CI04JPDG",.5,OAPPLID="CI00400Z",.6,OAPPLID="CI04JPTE",.7) 
| eval lower_limit1=upper_limit/3 
| eval lower_limit2=(upper_limit/3)*2 
| stats avg(TransactionSpeed) AS avgTransactionSpeed, values(upper_limit) AS upper_limit, values(lower_limit1) AS lower_limit1, values(lower_limit2) AS lower_limit2 
| gauge avgTransactionSpeedSec 0 lower_limit1 lower_limit2 upper_limit

 

If * is selected, and there is only one value for OAPPLID at that time, for example, CI04JPAF, upper_limit should be set to 1.5 and not .2.

I hope this is clearer. I'm still struggling with putting the idea into words.

What does * represent to Splunk? Is there a length associated with *?
I tried adding a (len(OAPPLID)=1),1.5 to the beginning of the if clause, but didn't work either.

I'm thinking I may need to use <set> and <unset> of the upper_limit in the dashboard for each possible OAPPLID. I would prefer to have the SPL handle rather than the XML.

I hope this didn't confuse matters more. I appreciate everyone's input thus far.

Thanks and God bless.
Genesius

0 Karma

Richfez
SplunkTrust
SplunkTrust

Well, ... Maybe that helped?  It brings up a couple of questions.  Or at least a long string of words from me, hopefully some of these are correct but maybe many aren't - you'll be able to read through it and tell, I hope and correct the wrong ones?  🙂

So, let's step back just a bit, make sure we understand what's going on and what's desired.  (Also, sing the rubber ducky song from Sesame Street at this point, because I'm rubber ducking you.  Or myself.  Something.)

The dropdown you have, is it a multi-select or just a regular drop down (e.g. can they pick only one item, or can they pick many?)  What is the name of the drop-down (meaning, if you want to address it in the SPL what would I use there?  I'll assume $OAPPLID$ for the moment)

And what is the purpose of that dropdown?  I hope it is to *filter* the returned events.  In which case you'll have as part of your search "OAPPLID=$OAPPLID$", right?  (I always tag a "_tok" after those just to make it extra clear it's coming from the tokens set by the input, OAPPLID=$OAPPLID_tok$, but no worries either works). 

So now you've filtered the events somewhat, maybe to OAPPLID=<some value> but maybe to OAPPLID=*.

And if I correctly interpret what you are saying, you then have some "upper_limit" you'd like to create, that's based on a value derived from what's matched?  Is that from the events (OAPPLID - my assumption) or from the dropdown ($OAPPLID$)?

Well, in either of those cases, if OAPPLID (of either type) was ONE of the possible values, you want upper_limit to be a particular value < 1 or so. If OAPPLID is of several values, you want it to be set to 1.5.

This is really convoluted.  Or at least it feels that way, and I'd love to actually learn the rationale behind this because maybe there's a completely different way to handle this. 

But, no worries!  Onwards we go, blindly throwing spaghetti on the wall and hoping it sticks!

One thing to mention is that there IS such a thing as "mvcount", like 

| eval n=mvcount(OAPPLID) | eval upper_limit = case(n>1, 1.5, OAPPLID="foo", 0.1, OAPPLID="bar", 0.2, ...)

If there's more than one value of OAPPLID, you should in that case pick up upper_limit of 1.5, otherwise it launches into the other pile of individual matches.

And, maybe that's your solution.

I seem to have run out of words.  That was a pile of conjecture and squiggly-brow thinking, so two things to check into:

a) Make sure you are using the right form (OAPPLID vs. $OAPPLID$) depending on which thing you are trying to reference.

b) Maybe break this down and get it working for a trivial version, then add to it.

c) check into the mvcount method

d) correct anything in the above that's wrong!

D is the second letter of the alphabet, right?  Close enough for me!

Happy splunking, and let us know ... what you find out!

-Rich

 

0 Karma

genesiusj
Builder

@Richfez 

Thanks Rich.

To bring up another Sesame Street song, 🎼 "Sunny days. Chasing the clouds away,....". So I don't want to spend too much time indoors this weekend. 😉

The dashboard panel code is from Syncsort's IronStream app for Splunk. We are tweaking copies of the CISC dashboard for specific OAPPLIDs, as well as other fields IronStream provides. OAPPLID is a text token, $regionFilter$, we are converting to a drop-down token, with the same name, to provide better reporting.

Here is the original XML code for the IronStream panel.

    <panel>
      <title>Average Transaction Response Time (secs)</title>
      <chart>
        <search>
          <query>index=$index$ SYSNAME=DOL1 MFSOURCETYPE=SMF110 TRAN !="C*" OAPPLID=$regionFilter$|eval TransactionSpeed=(SUSPTIME_MICROSEC + USRCPUT_MICROSEC) | stats avg(TransactionSpeed) as avgTransactionSpeed|eval avgTransactionSpeedSec=round(avgTransactionSpeed/1000000,5) | gauge avgTransactionSpeedSec  0 .003 .006 .0065 .007 .0075 .008 .0085</query>
          <earliest>$gaugeStartTime$</earliest>
          <latest>$gaugeEndTime$</latest>
          <refresh>$autoRefresh$</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="charting.chart">radialGauge</option>
        <option name="charting.chart.style">minimal</option>
        <option name="refresh.display">progressbar</option>
        <option name="charting.gaugeColors">[0xBF3030,0xFFE800,0x84E900]</option>
      </chart>
    </panel>

The gauge command is using hardcoded figures for different values. We need to make this dynamic, based on the value of the OAPPLID.
If I may, let's step back a bit. The upper_limit values chosen here are strictly for a proof of concept. I chose the .1 - .7 values as a quick visual to show the SPL is working. In reality, some of the upper_limit values might be the same. The upper_limit might be less than all the others as it is an average of all the OAPPLIDs combined.

🤔 the above answered A.

Onward and up/downward to B.

By "trivial version" I 🤔 you mean less values of OAPPLID? If so, I was doing that in my original posting. But because the other possible values of OAPPLID are still, well, possible, I thought that might be the culprit.?!

I think I just confused myself.  🤔 

See. I mean C.

Learned another new clause, without losing my sanity. Get it sanity, clau... Never mind. I'm losing my mine.

I would have loved mvcount to work. But alas. Nope.

Now onto my own D.

Whatever solution I find here can/will be used with future clients and projects. Nothing ever goes to waste. Everything is recycled.

Thanks again for your help and our banter. Feels refreshing in this time of uncertainty.

Stay safe and healthy, you and yours.

God bless,
Genesius

 

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 ...