topic Re: How do you search part of a text field (delimited by date)? in Splunk Search

How do you search part of a text field (delimited by date)?

anelson1 — Sun, 19 Apr 2020 06:27:20 GMT

I'm trying to search for specific words inside the last entry added to a paragraph, where each entry/addition to the paragraph is time & date stamped.

For example:
Paragraph = "25.12.2019 07:24:06 UTC Initial text entry 25.12.2019 09:50:52 UTC Should this be cancelled? No additional information found 26.12.2019 05:55:51 UTC No issues from this machine today, this should be cancelled"

I want to catalogue paragraphs that have the term 'cancelled' in them but only if the term is in the last entry in the paragraph. As you can see, the word 'cancelled' is in the middle of the paragraph following the entry on 25.12.2019 09:50:52 UTC and also in the last entry, so this would be catalogued as a "cancelled" paragraph in what I'm trying to do. There are several paragraphs I Have to search in this way and I plan to search for other terms aside from 'cancelled' once I figure out how to search on only the last entry in the paragraph rather than the whole paragraph.

Re: How do you search part of a text field (delimited by date)?

MuS — Sun, 19 Apr 2020 20:38:32 GMT

Hi anelson1,

This question needs more background information, details to get a correct answer. For example, when you say but only if the term is in the last entry in the paragraph do you want to match the time stamp before as well or not? There are many other question to be answered before this answer can be answered correctly - if that make sense.

As an example, if your _raw contains the message like this:

 Paragraph = "25.12.2019 07:24:06 UTC Initial text entry 25.12.2019 09:50:52 UTC Should this be cancelled? No additional information found 26.12.2019 05:55:51 UTC No issues from this machine today, this should be cancelled"

you could use a field extraction like this:

| rex "\s(?<last>\w+)\"" | search last="cancelled" | ...

But if this will yield the correct results as you expect it is hard to tell without more context.

cheers, MuS

PS: question, SPL posted here are from a chat with cp-regex-guru and martin_mueller

Re: How do you search part of a text field (delimited by date)?

cpetterborg — Sun, 19 Apr 2020 20:40:34 GMT

More information needed.

Can the term appear anywhere in the last entry of the paragraph?

What constitutes a paragraph (is it the entire event), and what constitutes an entry (I assume it is a part of the event, but what delineates one entry from another)?

Re: How do you search part of a text field (delimited by date)?

anelson1 — Mon, 20 Apr 2020 00:41:27 GMT

"when you say but only if the term is in the last entry in the paragraph do you want to match the time stamp before as well or not?"
I Don't think so, not sure what you mean by match the timestamp before as I don't want to match any time stamps.

Additional background information:
These paragraphs come from a csv file extracted from a maintenance tracking system, each entry contains a maintenance entry ID and a maintenance log (the paragraph). The system allows technicians to add information, via free text, to each maintenance record. The system will then append a timestamp and the free text entered to the existing text/paragraph.
The overall aim is to categorize the log entries into categories to draw a picture of our most common issues.
At first I was doing a simple
"| eval Cancelled=if(match(Paragraph, "cancelled|Cancelled"), 1, 0)" but then found a few entries incorrectly tagged as Cancelled as a technician had used the word 'cancelled' in a previous entry but not in the last entry, then I found that if they use that word in the last entry then 100% of the time it was correctly categorized.

Re: How do you search part of a text field (delimited by date)?

anelson1 — Mon, 20 Apr 2020 00:45:26 GMT

"Can the term appear anywhere in the last entry of the paragraph?"
Yes

"What constitutes a paragraph (is it the entire event), and what constitutes an entry (I assume it is a part of the event, but what delineates one entry from another)?"
I'm new to Splunk so I'm not sure what an "event" is....
Each entry is delineated by another field called maintenance ID.
The data comes from a csv file which when opened with excel has two columns "maintenance ID" and "Paragraph".

Re: How do you search part of a text field (delimited by date)?

cpetterborg — Mon, 20 Apr 2020 02:32:47 GMT

An event is any data item that comes back from a splunk search. In most cases that would be equivalent to a log entry, whether it is a single line or multiple lines that are part of a single log message.

What does the "entry" (or event) look like when you search the data? Can you provide a "sanitized" event that can show what you full event looks like and if there are fields that are extracted from that event?

Re: How do you search part of a text field (delimited by date)?

anelson1 — Mon, 20 Apr 2020 02:46:20 GMT

Ok thank you.

The data in splunk after this search:
source="maint_log.csv" host="Maint log data" sourcetype="csv" | eval Cancelled=if(match(Paragraph, "cancelled|Cancelled"), 1, 0) | table Cancelled "maintenance ID" Paragraph

returns 3 columns

First one is Cancelled, with just 1's and 0's,
Second one is maintenance ID, the values being a single number (8 chars long)
Third one is the Paragraph, a long block of text usually with multiple date/timestamps in it

so there are in this case 133 rows returned from that search as that's how many lines the csv contains too.

Re: How do you search part of a text field (delimited by date)?

anelson1 — Tue, 21 Apr 2020 05:41:45 GMT

Still looking for help on this if anyone has any ideas, please.

Re: How do you search part of a text field (delimited by date)?

anelson1 — Tue, 21 Apr 2020 13:43:09 GMT

Are you able to help any further MuS?

Re: How do you search part of a text field (delimited by date)?

anelson1 — Tue, 21 Apr 2020 13:43:34 GMT

Are you able to help any further cpetterborg?

Re: How do you search part of a text field (delimited by date)?

cpetterborg — Sat, 25 Apr 2020 14:52:51 GMT

Your reply still lacks sufficient information. Please use the text formatting button that looks like 101010 in two rows next to the double quotes " button to provide preformatted text to clarify answers and example data.

So, please provide some (sanitized) example data that shows the original data AND the various parts of the data (as you state "paragraphs") so that it can be determined exactly what you are trying to explain. It is still unclear what constitutes a "paragraph" and you talk about a first and second paragraph. So what part is the first paragraph? What part is the second paragraph? Are there always two paragraphs, or can there be just one? Are there ever three or more paragraphs?

Without an understanding of your data it is impossible to help you out. As you can see by the lack of responses, no one is able to help because there isn't enough information to help you. This is likely not a hard problem to overcome, it's just a lack of information to come up up with an answer.

Sorry to be blunt. I would truly like to help.

Re: How do you search part of a text field (delimited by date)?

anelson1 — Sun, 26 Apr 2020 03:37:18 GMT

Thank you for replying again, appreciate it.

So, please provide some (sanitized) example data that shows the original data AND the various parts of the data (as you state "paragraphs") so that it can be determined exactly what you are trying to explain.

Ok, let me try to clarify it further then
ORIGINAL DATA/Screenshot of the csv opened in excel

It is still unclear what constitutes a "paragraph" and you talk about a first and second paragraph. So what part is the first paragraph? What part is the second paragraph? Are there always two paragraphs, or can there be just one? Are there ever three or more paragraphs?

I had a read through my responses here and cannot find where I mentioned first/second/third paragraphs sorry, hoping the screenshot above clarifies everything enough.

Re: How do you search part of a text field (delimited by date)?

to4kawa — Sun, 26 Apr 2020 06:18:58 GMT

Which time do you want by each maintenaceID?

Re: How do you search part of a text field (delimited by date)?

anelson1 — Sun, 26 Apr 2020 06:24:39 GMT

None, not worried about what time the entries are, I just need to be able to search for keywords that exist in the last entry in each paragraph, regardless if they appear in the rest of the paragraph or not.

Re: How do you search part of a text field (delimited by date)?

to4kawa — Sun, 26 Apr 2020 06:39:28 GMT

| makeresults
| eval Paragraph="25.12.2019 07:24:06 UTC Initial text entry
25.12.2019 09:50:52 UTC Should this be cancelled?     
No additional information found 
26.12.2019 05:55:51 UTC No issues from this machine today, this should be cancelled"
| appendpipe [ eval Paragraph= "25.12.2019 07:24:06 UTC Initial text entry 
25.12.2019 09:50:52 UTC Should this be cancelled? 

No additional information found 
26.12.2019 05:55:51 UTC No issues from this machine today, this should be denied"]
| rex field=Paragraph max_match=0 "(?<timestamp>\S+\s\S+\sUTC)\s(?<msg>.+)"
| eval lastTimestamp=mvindex(timestamp,2), lastMsg=mvindex(msg,2)
| where match(lastMsg,"cancelled")

use rex and where

Re: How do you search part of a text field (delimited by date)?

to4kawa — Sun, 26 Apr 2020 07:21:54 GMT

I see, check my answer

Re: How do you search part of a text field (delimited by date)?

anelson1 — Sun, 26 Apr 2020 07:30:38 GMT

Sadly, this doesn't do what I need. This will find the word canceled ANYWHERE in the paragraph, I need to only find paragraphs where the word I'm looking for is in the last timestamped entry of the paragraph.

For example:
Paragraph example 1

    "25.12.2019 07:24:06 UTC Initial text entry 
    25.12.2019 09:50:52 UTC Should this be cancelled?     
    No additional information found 
    26.12.2019 05:55:51 UTC No issues from this machine today, this should be cancelled"

Paragraph example 2

"25.12.2019 07:24:06 UTC Initial text entry 
25.12.2019 09:50:52 UTC Should this be cancelled? 

No additional information found 
26.12.2019 05:55:51 UTC No issues from this machine today, this should be denied"

Example 2 has the word cancelled in it but not in the last timestamped entry, so this paragraph should NOT be counted.
Example 1 has the word cancelled in the last timestamped entry, so this paragraph SHOULD be counted.

Re: How do you search part of a text field (delimited by date)?

to4kawa — Sun, 26 Apr 2020 07:57:12 GMT

I see, my answer is updated.

Re: How do you search part of a text field (delimited by date)?

anelson1 — Sun, 26 Apr 2020 08:45:34 GMT

Thank you, that looks very promising and I'm eager to see if it works, however I'm not sure how to implement your answer into my current search as I'm new to splunk and not that bright, could you please advise how I can implement your answer if my search text looks like this:

source="maint_log.csv" host="Maint log data" sourcetype="csv" | eval Cancelled=if(match(Paragraph, "cancelled|Cancelled"), 1, 0) | table Cancelled "maintenance ID" Paragraph

Re: How do you search part of a text field (delimited by date)?

to4kawa — Sun, 26 Apr 2020 08:58:23 GMT

  source="maint_log.csv" host="Maint log data" sourcetype="csv" 
| rex field=Paragraph max_match=0 "(?<timestamp>\S+\s\S+\sUTC)\s(?<msg>.+)"
| eval lastTimestamp=mvindex(timestamp,2), lastMsg=mvindex(msg,2)
| eval Cancelled=if(match(lastMsg, "(?i)cancelled"), 1, 0) 
| table Cancelled "maintenance ID" Paragraph