topic Re: Combine Values to Return Search Result in Splunk Search

Combine Values to Return Search Result

NewToSplunk1 — Thu, 31 Aug 2023 17:59:29 GMT

We are using Splunk OPC Add-On to bring in some tags. We have two specific tags that we are currently looking at. Tag 1's value will always be "Productive" or "Non-productive". Tag 2's value will be a current string value or blank.

We are hoping that we can alert if Tag1 = Productive & Tag2 != "", then we can return a result and alert off of this result.

I have tried: "Tag1"="Productive" AND NOT isnull("Tag2") but that doesn't return any results when there should be a few results. I'm not sure if I need to combine these somehow?

Re: Combine Values to Return Search Result

richgalloway — Thu, 31 Aug 2023 19:33:34 GMT

Are Tag1 and Tag2 in the same event? If not, what field links the two events? Where are you using the isnull() function?

Re: Combine Values to Return Search Result

isoutamo — Fri, 01 Sep 2023 07:42:45 GMT

your said that Tag2 can be “blank”, but what this blank actually means? Does it mean value which are empty or space or that this Tag didn’t exists? Only the last option means that you could use functions isnull(Tag2) or isnotnull(Tag2). 1st and 2nd option means that Tag2 exists (isnotnull), but it hasn’t value or value is “ “.

r. Ismo

Re: Combine Values to Return Search Result

NewToSplunk1 — Fri, 01 Sep 2023 11:56:21 GMT

@richgalloway

@isoutamo

I should bring in some examples.

My current query is:

index=plc source="middleware" sourcetype="plc:___" Tag = "Channel1*"
| dedup _time
| table _time Tag Value

This brings in a table with two different tags that we are currently monitoring. One is an incident and the other is a tag that specifies if the time is working hours or not:

I want to be able to take the last scheduled event value and apply this to every incident column rather than the scheduled time populating within the incident column.

Re: Combine Values to Return Search Result

isoutamo — Fri, 01 Sep 2023 12:21:46 GMT

Ok, this seems to be totally different case what you are asking earlier 😞

Basically you have only one Tag which has several values. Unfortunately your examples didn't show enough information to answer you. Can you give the whole events (scrambled if need)? We are needing something to make reactions between those events.

Re: Combine Values to Return Search Result

NewToSplunk1 — Fri, 01 Sep 2023 13:49:28 GMT

I apologize for the confusion.

Here's a general query to grab the information:

index=plc source="middleware" sourcetype="plc:___" Tag = "Channel1*"

| where Value != "" AND Value != "nothing"

Here are the results for the last 120 minutes... You can see around 9AM that the schedule tag value changes. I would almost want to keep the last schedule tag value and tack that onto the incident tags as they come in.

Time	Event
9:27:36 AM	2023-09-01 13:27:36.260 +0000 Tag="Incident" Value="ALARM3" Quality="good"
9:21:20 AM	2023-09-01 13:21:20.297 +0000 Tag="Incident" Value="ALARM3" Quality="good"
9:16:33 AM	2023-09-01 13:16:32.918 +0000 Tag="Incident" Value="ALARM3" Quality="good"
9:15:22 AM	2023-09-01 13:15:22.263 +0000 Tag="Incident" Value="ALARM4" Quality="good"
9:10:15 AM	2023-09-01 13:10:15.419 +0000 Tag="Schedule" Value="Productive" Quality="good"
8:59:14 AM	2023-09-01 12:59:14.164 +0000 Tag="Schedule" Value="Non-productive" Quality="good"
8:59:13 AM	2023-09-01 12:59:12.661 +0000 Tag="Incident" Value="ALARM3" Quality="good"
8:52:15 AM	2023-09-01 12:52:14.779 +0000 Tag="Incident" Value="ALARM3" Quality="good"
8:48:59 AM	2023-09-01 12:48:59.291 +0000 Tag="Incident" Value="ALARM1" Quality="good"
8:46:41 AM	2023-09-01 12:46:41.037 +0000 Tag="Incident" Value="ALARM1" Quality="good"
8:42:16 AM	2023-09-01 12:42:16.314 +0000 Tag="Incident" Value="ALARM1" Quality="good"
8:39:58 AM	2023-09-01 12:39:58.018 +0000 Tag="Incident" Value="ALARM1" Quality="good"
8:27:52 AM	2023-09-01 12:27:51.918 +0000 Tag="Incident" Value="ALARM3" Quality="good"
8:20:13 AM	2023-09-01 12:20:13.465 +0000 Tag="Incident" Value="ALARM3" Quality="good"
8:15:44 AM	2023-09-01 12:15:44.416 +0000 Tag="Incident" Value="ALARM3" Quality="good"
8:11:46 AM	2023-09-01 12:11:46.442 +0000 Tag="Incident" Value="ALARM3" Quality="good"
8:09:37 AM	2023-09-01 12:09:37.184 +0000 Tag="Incident" Value="ALARM1" Quality="good"
8:07:23 AM	2023-09-01 12:07:23.474 +0000 Tag="Incident" Value="ALARM1" Quality="good"
8:01:53 AM	2023-09-01 12:01:52.538 +0000 Tag="Incident" Value="ALARM1" Quality="good"
7:58:28 AM	2023-09-01 11:58:27.990 +0000 Tag="Incident" Value="ALARM1" Quality="good"
7:57:16 AM	2023-09-01 11:57:15.859 +0000 Tag="Incident" Value="ALARM1" Quality="good"
7:49:31 AM	2023-09-01 11:49:31.305 +0000 Tag="Incident" Value="ALARM1" Quality="good"
7:48:21 AM	2023-09-01 11:48:20.686 +0000 Tag="Incident" Value="ALARM2" Quality="good"
7:47:13 AM	2023-09-01 11:47:13.069 +0000 Tag="Incident" Value="ALARM1" Quality="good"
7:35:14 AM	2023-09-01 11:35:14.139 +0000 Tag="Incident" Value="ALARM1" Quality="good"

Re: Combine Values to Return Search Result

richgalloway — Fri, 01 Sep 2023 14:25:32 GMT

"keep the last schedule tag value" == filldown

index=plc source="middleware" sourcetype="plc:___" Tag = "Channel1*" | where Value != "" AND Value != "nothing" | eval Schedule=if(Tag="Schedule", Value, null()) | filldown Schedule

Re: Combine Values to Return Search Result

NewToSplunk1 — Fri, 01 Sep 2023 16:21:44 GMT

@richgalloway @isoutamo

Rich was able to help out with his search. I now have this as my search:

index=plc source="middleware" sourcetype="plc:" Tag = "Channel1*" | where Value != "" | eval Schedule = if(Tag="Schedule", Value, null()) | eval Incident = if(Tag="Incident", Value, null()) | table _time Schedule Incident Value

This returns the following results:

_time	Schedule	Incident	Value
11:54:31 AM		Alarm2	Alarm2
11:30:15 AM	Productive		Productive
10:59:15 AM	Non-productive		Non-productive
10:59:09 AM		Alarm2	Alarm2
10:55:10 AM		Alarm1	Alarm1
10:47:59 AM		Alarm2	Alarm2
10:27:40 AM		Alarm2	Alarm2
10:17:12 AM		Alarm2	Alarm2
10:15:03 AM		Alarm2	Alarm2
10:13:12 AM		Alarm2	Alarm2
10:01:49 AM		Alarm2	Alarm2
9:54:00 AM		Alarm2	Alarm2
9:48:44 AM		Alarm2	Alarm2
9:38:20 AM		Alarm2	Alarm2
9:27:36 AM		Alarm2	Alarm2
9:21:20 AM		Alarm2	Alarm2
9:16:33 AM		Alarm2	Alarm2
9:15:22 AM		Alarm3	Alarm3
9:10:15 AM	Productive		Productive
8:59:14 AM	Non-productive		Non-productive
8:59:13 AM		Alarm2	Alarm2
8:52:15 AM		Alarm2	Alarm2
8:48:59 AM		Alarm1	Alarm1
8:46:41 AM		Alarm1	Alarm1
8:42:16 AM		Alarm1	Alarm1
8:39:58 AM		Alarm1	Alarm1
8:27:52 AM		Alarm2	Alarm2
8:20:13 AM		Alarm2	Alarm2
8:15:44 AM		Alarm2	Alarm2
8:11:46 AM		Alarm2	Alarm2
8:09:37 AM		Alarm1	Alarm1
8:07:23 AM		Alarm1	Alarm1
8:01:53 AM		Alarm1	Alarm1
7:58:28 AM		Alarm1	Alarm1
7:57:16 AM		Alarm1	Alarm1

I think I need the opposite of the filldown command (if there is one?), where I take the last known value of schedule and populate the schedule field with that if a get a value timestap where the schedule is null.

Re: Combine Values to Return Search Result

richgalloway — Fri, 01 Sep 2023 16:47:25 GMT

The opposite of filldown is to reverse sort the data, use filldown, then re-sort to the original order.