Need Help with spl query

[807mohd]

Hello,

I'm trying to achieve a result set which can be used in an alert later on.

Basically when search is executed, its should look for field named "state" and evaluate with its value from two hours ago for the same corresponding record, which is field name "pv_number" and if the value of field did not change between "now" and "two hours ago", capture it as table showing previous state and current state along with previous time and current time.

Any help is greatly appreciated.

Thanks much! 

[yuanliu · Accepted solution]

I think streamstats in the title throw volunteers off because it is hard to see how it relates to your requirement, which you describe quite well without SPL.  It would be better if you also illustrate input and desired output.

Here is one way to do what you ask:

| index = foo sourcetype = bar earliest=-2h latest=now
| addinfo
| stats earliest(state) as two_hours_ago latest(state) as now by pv_number info_min_time info_max_time
| where two_hours_ago == now
| eval info_min_time = strftime(info_min_time, "%F %T"), info_max_time = strftime(info_max_time, "%F %T")

Emulated output without the where filter looks like

pv_number	info_min_time	info_max_time	two_hours_ago	now
ApplicationUpdateThread	2024-10-03 22:44:19	2024-10-04 00:44:19	22	22
ExecProcessor	2024-10-03 22:44:19	2024-10-04 00:44:19	44	44
HTTPDispatch	2024-10-03 22:44:19	2024-10-04 00:44:19	28	29
SavedSearchFetcher	2024-10-03 22:44:19	2024-10-04 00:44:19	27	27
TcpChannelThread	2024-10-03 22:44:19	2024-10-04 00:44:19	21	33
TelemetryMetricBuffer	2024-10-03 22:44:19	2024-10-04 00:44:19	31	33
indexerPipe	2024-10-03 22:44:19	2024-10-04 00:44:19	0	0
tailreader0	2024-10-03 22:44:19	2024-10-04 00:44:19	44	44
webui	2024-10-03 22:44:19	2024-10-04 00:44:19	28	29

With filter, the output is

pv_number	info_min_time	info_max_time	two_hours_ago	now
ApplicationUpdateThread	2024-10-03 22:42:19	2024-10-04 00:42:19	22	22
ExecProcessor	2024-10-03 22:42:19	2024-10-04 00:42:19	42	42
SavedSearchFetcher	2024-10-03 22:42:19	2024-10-04 00:42:19	27	27
indexerPipe	2024-10-03 22:42:19	2024-10-04 00:42:19	0	0
tailreader0	2024-10-03 22:42:19	2024-10-04 00:42:19	42	42

Is this something you are looking for?

The emulation I use to produce mock data is

index = _internal earliest=-2h latest=now
| rename thread_name as "pv_number", date_minute as state
``` data emulation above ```

[807mohd]

Hi yuanliu

Firstly thanks for looking into it and helping with the SPL query.  It was pleasing to see someone responding I felt like I should buy a coffee 🙂
I apologize for my mistake of mentioning streamstats.

I think i did not put my original request properly, let me try again.
so when the search is executed (now), we need data from two point in times, from now and two hours ago.

If I'm running a search at 16:05:02,

first set will have data values of pv_number (example ext034)  and "state" value (6) at that point-in-time  (from two hours ago, so 14:05:02)

In the second set of data values,  pv_number (if its still exist in this point of time @ 16:05:02) AND still has "state" value (6), then want to see the table showing pv_number and both times along with previous and current state.

Hope It helps..

[yuanliu]

I think my understanding fits your description.  The idea behind my suggested search is:

1. Search between the two hours.  Find all records that has pv_number. (You can restrict pv_number to a given value but my search assumes that you want to group by pv_number, which is stated in the OP.)
2. Look for the earliest value of state, and the latest.
3. Compare earliest value and latest value. Only print those where the two equal.

Have you tried my search?  Also play with my emulation (that should run in any instance), and examine output with and without that where filter.  As my code indicates, I use thread_name to fake pv_number, date_minute to fake state.  They may have different values from your real data, but the principle is the same.

[807mohd]

You are right, I had issues in my raw data. Thanks very much for the help!!