How to do compound query with where clause?

[yshen]

I have the following data as example:

I want to find the events whose locations having had temperature above a threshold, say 80F.

Temperature=82.4, Location=xxx.165.152.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=84.2, Location=xxx.165.152.48, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=82.4, Location=xxx.165.154.21, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=82.4, Location=xxx.165.162.22, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=77.0, Location=xxx.165.164.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=75.2, Location=xxx.165.170.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=77.0, Location=xxx.165.208.12, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=73.4, Location=xxx.165.224.20, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=75.3, Location=xxx.165.52.13, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=77.9, Location=xxx.165.52.14, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=76.3, Location=xxx.165.54.24, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=83.8, Location=xxx.165.48.20, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=73.8, Location=xxx.165.36.21, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor

I would like to first find the subsets of locations with whom the temperatures having been above a threshold, say 80F. 

I may perform the a query like the following:

| Temperature > 80
| fields Location
| dedup Location

I'd call the locations outcome of the query "hot_locations",

then I'd like perform my eventual query:

| Location IN hot_locations

My question is what's the syntax of Splunk query language to express the declarations?

Thanks for your pointers!

[hc_joycechen]

 hot_locations:

| makeresults
| eval _raw="Temperature=82.4, Location=xxx.165.152.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=84.2, Location=xxx.165.152.48, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=82.4, Location=xxx.165.154.21, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=82.4, Location=xxx.165.162.22, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=77.0, Location=xxx.165.164.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=75.2, Location=xxx.165.170.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=77.0, Location=xxx.165.208.12, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=73.4, Location=xxx.165.48.20, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=75.3, Location=xxx.165.52.13, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=77.9, Location=xxx.165.52.14, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=76.3, Location=xxx.165.54.24, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=83.8, Location=xxx.165.48.20, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=73.8, Location=xxx.165.36.21, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;"
| eval x=split(_raw,";")
| mvexpand x
| rename x as _raw
| extract
| fields - _raw
| stats max(Temperature) as max_Temperature by Type, Location
| sort Type, - max_Temperature
| stats first(Location) as Location by Type
| fields Location
| dedup Location

[yshen]

Thanks for providing input. Your suggestion:

| where Temperature>80 
| stats min(Temperature) as LowestTemp max(Temperature) as HighestTemp by  Location Type

only computes the locations that have temperatures greater than 80F, and the minimum temperature and maximum temperature of those above 80F. 

But I want to all the raw events with the locations that have had temperatures above 80F sometimes. 

For those locations, I even want to have the events with temperature below 80F, as long as the location in question sometimes have temperature above 80F. It's very different semantics.

Thanks for helping!

[bowesmana]

OK, so then you need to address the time range for 'sometimes'. If sometimes is 'for ever' then you probably want to create a lookup of locations that have >80F and then your search would simply use that lookup file as a subsearch as the hot location list.

your_search [ | inputlookup hot_locations.csv | fields location ]

 If your 'sometimes' is outside the window you're searching within, then you would either need to go the lookup route above or run the subsearch over the 'sometimes' window searching for temp > 80 and then returning those hot locations, e.g.

your_search [ search time_range temp>80 | stats count by location | fields location ]

which method you use will depend on your data volume and performance requirements.

[bowesmana]

I don't know why you think you need two queries. You can copy/paste this example into the Splunk search window - up to the | where clause is just setting up your data

| makeresults
| eval _raw="Temperature=82.4, Location=xxx.165.152.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=84.2, Location=xxx.165.152.48, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=82.4, Location=xxx.165.154.21, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=82.4, Location=xxx.165.162.22, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=77.0, Location=xxx.165.164.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=75.2, Location=xxx.165.170.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=77.0, Location=xxx.165.208.12, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=73.4, Location=xxx.165.224.20, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=75.3, Location=xxx.165.52.13, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=77.9, Location=xxx.165.52.14, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=76.3, Location=xxx.165.54.24, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=83.8, Location=xxx.165.48.20, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=73.8, Location=xxx.165.36.21, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor"
| eval x=split(_raw,";")
| mvexpand x
| rename x as _raw
| extract
| fields - _raw
| where Temperature>80
| stats min(Temperature) as LowestTemp max(Temperature) as HighestTemp by  Location Type

so, all you need is the where+stats last two lines - as an example.

[yshen]

@bowesmana 

Let me use an example to illustrate what I'm looking for. The following is changed sample data for illustration:

Temperature=82.4, Location=xxx.165.152.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=84.2, Location=xxx.165.152.48, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=82.4, Location=xxx.165.154.21, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=82.4, Location=xxx.165.162.22, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=77.0, Location=xxx.165.164.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=75.2, Location=xxx.165.170.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=77.0, Location=xxx.165.208.12, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=73.4, Location=xxx.165.48.20, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=75.3, Location=xxx.165.52.13, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=77.9, Location=xxx.165.52.14, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=76.3, Location=xxx.165.54.24, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=83.8, Location=xxx.165.48.20, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor
Temperature=73.8, Location=xxx.165.36.21, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor

I want to select ALL records whose locations have had temperature reached 83F ever. The qualifying locations are the following:

xxx.165.152.48
xxx.165.48.20

as their temperature had reached 83F or above.

So my expected output is the following:

Temperature=84.2, Location=xxx.165.152.48, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=73.4, Location=xxx.165.48.20, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS
Temperature=83.8, Location=xxx.165.48.20, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor

Note, I want ALL with the qualified locations, not just the ones with temperature above 83F.

I need the raw data in its original form, not reconstructed statistics. 

Thank all for the help, but I have not found a solution that can produce the above expected outcome.

[bowesmana]

@yshen 

There are a number of ways to solve this, but it's still not clear when you talk about "sometimes" or "ever". Your example data is only 16 Sep, however, the basic solution to hanging on to the raw data is to use eventstats/where, like this

| makeresults
| eval _raw="Temperature=82.4, Location=xxx.165.152.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=84.2, Location=xxx.165.152.48, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=82.4, Location=xxx.165.154.21, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=82.4, Location=xxx.165.162.22, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=77.0, Location=xxx.165.164.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=75.2, Location=xxx.165.170.17, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=77.0, Location=xxx.165.208.12, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=73.4, Location=xxx.165.48.20, Time=Wed Sep 16 07:43:01 PDT 2020, Type=UPS;
Temperature=75.3, Location=xxx.165.52.13, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=77.9, Location=xxx.165.52.14, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=76.3, Location=xxx.165.54.24, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=83.8, Location=xxx.165.48.20, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor;
Temperature=73.8, Location=xxx.165.36.21, Time=Wed Sep 16 07:47:01 PDT 2020, Type=TempSensor"
| eval x=split(_raw,";")
| mvexpand x
| rename x as _raw
| extract
| fields - _raw
| eventstats max(Temperature) as mt by Location
| where mt>83

which will leave you 3 rows and then you can do what you want with that.

However, it's not clear if, say, the location "Temperature=76.3, Location=xxx.165.54.24", recording the 76.3 on 16th September, but which had recorded 83.1 on 4th July 2002, should be in the 'hot locations'. (assuming of course you have data going back that far).

If so, then the solution would have to change, as it is unlikely to be practical to search that much data with eventstats.

Instead you would be better off doing a daily search to find temps that day that exceeded your threshold, or even just the max temp for each location and save that max to a lookup file for the location.

Then when doing the 'find me raw data for hot locations' query, you would then do the basic search for all data for your period, then lookup the location from the lookup and make the check there, for example

base_search
| lookup location_list.csv Location OUTPUT locationHistoricalMaxTemp
| eval maxTemp=max(locationHistoricalMaxTemp, Temperature)
| eventstats max(maxTemp) as mt by Location
| where mt>83

What this is doing is getting the currently saved historical max from your lookup based on location, then assuming you update that lookup at the end of the day, so the current Temp might be higher, the maxTemp will pick the highest of either today's or the historical, then the eventstats/where comes into play to find the rows from hot locations.

Hope this answers what you're trying to do.