Splunk Search

adding a condition

netanelm7
Path Finder

I have 4 fields and each one of them contains a number and i need to check whether the sum of a specific 2 fields is greather then 900 for example.
For some reason it doesnt seem to work.
My query is:

index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB>450 | search IDs="JNL014" | dedup _time | timechart span=1h count(transfer_in_MB) by IDs | appendcols [search index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB>450 | search IDs="JNL00A" | dedup _time | timechart span=1h count(transfer_in_MB) by IDs] | appendcols [search index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB>450 | search IDs="JNL000" | dedup _time | timechart span=1h count(transfer_in_MB) by IDs] | appendcols [search index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB>450 | search IDs="JNL01E" | dedup _time | timechart span=1h count(transfer_in_MB) by IDs] | fillnull value="0" JNL000 | fillnull value="0" JNL00A | fillnull value="0" JNL014 | fillnull value="0" JNL01E | eval start_time=_time, end_time=_time+_span | eval JNL000_00A=JNL000+JNL00A, JNL000_014=JNL000+JNL014, JNL000_01E=JNL000+JNL01E, JNL00A_014=JNL00A+JNL014, JNL00A_01E=JNL00A+JNL01E, JNL014_01E=JNL014+JNL01E | fields _time,JNL000_00A,JNL000_014,JNL000_01E,JNL00A_014,JNL00A_01E,JNL014_01E

The fields are JNL000_00A, JNL000_014, JNL000_01E, JNL00A_014, JNL00A_01E, JNL014_01E and i want to see only rows that the value of each of the joined fields is above 900.
Right now I only manage to see then if they are above 450 because there is a "where" for each field by it's own.

Tags (1)
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try like this (simplified)

index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA (IDs="JNL014" OR IDs="JNL00A" ORIDs="JNL000" OR IDs="JNL01E")
| eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB>450 
| dedup _time IDs| timechart span=1h count(transfer_in_MB) by IDs
| fillnull value="0" JNL000, JNL00A, JNL014, JNL01E 
| eval JNL000_00A=JNL000+JNL00A, JNL000_014=JNL000+JNL014, JNL000_01E=JNL000+JNL01E, JNL00A_014=JNL00A+JNL014, JNL00A_01E=JNL00A+JNL01E, JNL014_01E=JNL014+JNL01E 
| fields _time,JNL000_00A,JNL000_014,JNL000_01E,JNL00A_014,JNL00A_01E,JNL014_01E
| where JNL000_00A>900 AND JNL000_014>900 AND JNL000_01E>900 AND JNL00A_014>900 AND JNL00A_01E>900 AND JNL014_01E>900

netanelm7
Path Finder

Hi, i'm very close to solution, i've managed to see a timechart that shows the transfer_in_MB where the combined JNLs are above 900, and all i need to do is display it in a hourly interval and display each column count. (timechart span=1h count(transfer_in_MB) by JNL*), but it doesnt work with "*"

index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA (JOURNAL_ID="014" OR JOURNAL_ID="00A" OR JOURNAL_ID="000" OR JOURNAL_ID="01E")
| eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | table _time IDs transfer_in_MB | dedup _time IDs | timechart span=1m avg(transfer_in_MB) by IDs | eval JNL000_00A=if(JNL000+JNL00A<900,0,JNL000+JNL00A),JNL000_014=if(JNL000+JNL014<900,0,JNL000+JNL014), JNL000_01E=if(JNL000+JNL01E<900,0,JNL000+JNL01E), JNL00A_014=if(JNL00A+JNL014<900,0,JNL00A+JNL014), JNL00A_01E=if(JNL00A+JNL01E<900,0,JNL00A+JNL01E), JNL014_01E=if(JNL014+JNL01E<900,0,JNL014+JNL01E) | where JNL000_00A > 900 OR JNL000_014 > 900 OR JNL000_01E > 900 OR JNL00A_014 > 900 OR JNL00A_01E > 900 OR JNL014_01E > 900 | fields _time,JNL000_00A,JNL000_014,JNL000_01E,JNL00A_014,JNL00A_01E,JNL014_01E

0 Karma

netanelm7
Path Finder

Thank you for your command,
The last where is not working because each one of the fields consist a count of how many time the transfer_in_MB>450, and it will compare the count to 900 instead of the transfer_in_MB.
If i only could do a timechart that shows the transfer_in_MB of each JNL000_00A,JNL000_014,JNL000_01E,JNL00A_014,JNL00A_01E,JNL014_01E where transfer_in_MB of each of them > 900 it would solve it..
But the Where is used on a single JNL rather then the conbined fields.

Thank you for your time and patience!

0 Karma

niketn
Legend

@netanelm7, seems like all you need is to change the where condition from AND to OR

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

netanelm7
Path Finder

but i dont want to see where the counts are bigger then 900, i want to see where the transfer_in_MB of 2 fields together is greater then 900.
Thank you!

0 Karma

niketn
Legend

change

| timechart span=1h count(transfer_in_MB) by IDs

to

| timechart span=1h max(transfer_in_MB) by IDs
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

netanelm7
Path Finder

The graph should display the Number of times (count) 2 fields (when you sum their transfer_in_MB) are greather then 900 and display it in a timechart hourly interval.
Thank you for your time and patience!

0 Karma

netanelm7
Path Finder

Hi, i'm very close to solution, i've managed to see a timechart that shows the times where the combined JNLs are above 900, and all i need to do is display it in a hourly interval and display each column count. (timechart span=1h count(transfer_in_MB) by JNL*), but it doesnt work with "*"

index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA (JOURNAL_ID="014" OR JOURNAL_ID="00A" OR JOURNAL_ID="000" OR JOURNAL_ID="01E")
| eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | table _time IDs transfer_in_MB | dedup _time IDs | timechart span=1m avg(transfer_in_MB) by IDs | eval JNL000_00A=if(JNL000+JNL00A<900,0,JNL000+JNL00A),JNL000_014=if(JNL000+JNL014<900,0,JNL000+JNL014), JNL000_01E=if(JNL000+JNL01E<900,0,JNL000+JNL01E), JNL00A_014=if(JNL00A+JNL014<900,0,JNL00A+JNL014), JNL00A_01E=if(JNL00A+JNL01E<900,0,JNL00A+JNL01E), JNL014_01E=if(JNL014+JNL01E<900,0,JNL014+JNL01E) | where JNL000_00A > 900 OR JNL000_014 > 900 OR JNL000_01E > 900 OR JNL00A_014 > 900 OR JNL00A_01E > 900 OR JNL014_01E > 900 | fields _time,JNL000_00A,JNL000_014,JNL000_01E,JNL00A_014,JNL00A_01E,JNL014_01E

0 Karma

niketn
Legend

@netanelm7, at the beginning of your question you have mentioned sum of specific two fields. Towards the end of your question you have mentioned all fields. Which one do you want to use.
Also on similar lines you have mentioned 4 fields in the beginning and towards the end each of your joined field is actually 6.

In one of your previous question with similar data I had asked you to try out some of Search query performance tips. Does not seem like you are using any of those: https://answers.splunk.com/answers/595380/how-to-get-a-substr-from-a-fields-name.html#answer-595396
I had missed one that similar to eval even fillnull can except multiple field names using comma in single pipe i.e

| fillnull value="0" JNL000, JNL00A, JNL014, JNL01E
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

netanelm7
Path Finder

Hi, i'm very close to solution, i've managed to see a timechart that shows the transfer_in_MB where the combined JNLs are above 900, and all i need to do is display it in a hourly interval and display each column count. (timechart span=1h count(transfer_in_MB) by JNL*), but it doesnt work with "*"

index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA (JOURNAL_ID="014" OR JOURNAL_ID="00A" OR JOURNAL_ID="000" OR JOURNAL_ID="01E")
| eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | table _time IDs transfer_in_MB | dedup _time IDs | timechart span=1m avg(transfer_in_MB) by IDs | eval JNL000_00A=if(JNL000+JNL00A<900,0,JNL000+JNL00A),JNL000_014=if(JNL000+JNL014<900,0,JNL000+JNL014), JNL000_01E=if(JNL000+JNL01E<900,0,JNL000+JNL01E), JNL00A_014=if(JNL00A+JNL014<900,0,JNL00A+JNL014), JNL00A_01E=if(JNL00A+JNL01E<900,0,JNL00A+JNL01E), JNL014_01E=if(JNL014+JNL01E<900,0,JNL014+JNL01E) | where JNL000_00A > 900 OR JNL000_014 > 900 OR JNL000_01E > 900 OR JNL00A_014 > 900 OR JNL00A_01E > 900 OR JNL014_01E > 900 | fields _time,JNL000_00A,JNL000_014,JNL000_01E,JNL00A_014,JNL00A_01E,JNL014_01E

0 Karma

netanelm7
Path Finder

Thank you for your answer, i did improve it but after i've opened the case.. im sorry.

I have 4 fields in the beggining because they are my main field, but in the end I create 6 fields (which are the combination of the 4 main fields) and work on them.

Thanks to somesoni2, the current query looks like:

index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA (JOURNAL_ID="014" OR JOURNAL_ID="00A" OR JOURNAL_ID="000" OR JOURNAL_ID="01E")
| eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB>450
| dedup _time IDs | timechart span=1h count(transfer_in_MB) by IDs
| fillnull value="0" JNL000, JNL00A, JNL014, JNL01E
| eval JNL000_00A=JNL000+JNL00A, JNL000_014=JNL000+JNL014, JNL000_01E=JNL000+JNL01E, JNL00A_014=JNL00A+JNL014, JNL00A_01E=JNL00A+JNL01E, JNL014_01E=JNL014+JNL01E
| fields _time,JNL000_00A,JNL000_014,JNL000_01E,JNL00A_014,JNL00A_01E,JNL014_01E

it's still not working, but at least looks better and preforms better

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