Splunk Search

Trying to calculate a percentage of results within a range against the total number of events.

donfarland
Explorer

This seems like it should be rather simple, but I'm simply at a loss. All I'm trying to do is:

  • Count the total number of readings from a given sensor.
  • Count the number of readings that are in the allowable range.
  • Calculate the compliance percentage.

I can run the following searches independently without issue, but can't seem to figure out how to put them together.

  • room="106" | stats count as Total
  • room="106" | where S1_Temp>70 OR S1_Temp<76 | stats count as Compliant

All I really need to do is put them together so I can perform the calculation of "round((Compliant/Total),2) as CompliancePct"

The following is what I have so far, but it is interesting to note that the second query returns 28000+ when run alone and 0 in this combined query.

room="106" | stats count as Total | appendcols[| where S1_Temp>70 OR S1_Temp<76 | stats count as In]
0 Karma
1 Solution

somesoni2
Revered Legend

Try something like this

room="106" | eval Compliant=if(S1_Temp>70 AND S1_Temp<76,1,0) | stats count as Total sum(Compliant) as Compliant | eval CompliancePct=round((Compliant/Total),2)

View solution in original post

somesoni2
Revered Legend

Try something like this

room="106" | eval Compliant=if(S1_Temp>70 AND S1_Temp<76,1,0) | stats count as Total sum(Compliant) as Compliant | eval CompliancePct=round((Compliant/Total),2)

View solution in original post

donfarland
Explorer

I should have known the answer would involve 'eval'. I had played around with i t some, but was trying to combine it with the "where" instead of an "if". Thank you very much, that works perfectly and opened my eyes to a number of other possibilities.

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.