topic Re: How do you change a row by checking another row? in Splunk Search

How do you change a row by checking another row?

veerendra_modi — Fri, 29 Mar 2019 08:24:55 GMT

Hi Team,

In the screenshot below, I want to change the criticality of sorcetype S2 to P2(row 3) if its Id is available in the 4th Column.

Keeping all the other values same.

Plz help.

Re: How do you change a row by checking another row?

kamlesh_vaghela — Fri, 29 Mar 2019 10:16:39 GMT

@veerendra_modi

You can try Eventstats to access fields of the previous event using stats functions.

https://docs.splunk.com/Documentation/Splunk/7.2.4/SearchReference/Eventstats

Re: How do you change a row by checking another row?

veerendra_modi — Fri, 29 Mar 2019 10:43:19 GMT

Thanks Kamlesh can you help me with the sample code.

Re: How do you change a row by checking another row?

kamlesh_vaghela — Fri, 29 Mar 2019 10:51:08 GMT

sure @veerendra_modi

Please share your search, sample data and expected output.

Re: How do you change a row by checking another row?

veerendra_modi — Fri, 29 Mar 2019 11:00:07 GMT

Consider that i am getting the above data displayed in the screenshot above.
The expected output would be that the third row of sourcetype=S2 will be changed to criticality=P2

See the attachment Result-1.png

Re: How do you change a row by checking another row?

FrankVl — Fri, 29 Mar 2019 12:16:35 GMT

Maybe I'm missing something, but wouldn't that be as simple as: | eval Criticality=if(Id=query AND sourcetype="S2","P2",Criticality)

Re: How do you change a row by checking another row?

veerendra_modi — Mon, 01 Apr 2019 03:35:22 GMT

Thanks Frank
what does it mean by Id=query here

Re: How do you change a row by checking another row?

FrankVl — Mon, 01 Apr 2019 07:28:13 GMT

It checks if those 2 columns match. That is how I interpreted "if its Id is available in the 4th Column".

Re: How do you change a row by checking another row?

veerendra_modi — Mon, 01 Apr 2019 08:54:20 GMT

but id and query are different columns, we need to check if an id is available in query 2 and we have same id with sourcetype=S2 then we need to change the Criticality to P2.
Is it clear now pls help

Re: How do you change a row by checking another row?

FrankVl — Mon, 01 Apr 2019 09:25:17 GMT

Ah, so you meant: "if its Id is available in the 4th Row" instead of Column? Then ignore my answer, let me think if I can come up with something for that.

Re: How do you change a row by checking another row?

FrankVl — Mon, 01 Apr 2019 09:43:11 GMT

If the query field is indeed a number like this, with the basic data showing as 1 and the row to check against showing as 2, then I think this would work:

... your current query...
| eventstats max(query) as max_query by Id
| eval Criticality=if(max_query=2 AND sourcetype="S2","P2",Criticality)

Note: this is specific to that sourcetype and criticality values.

If you need this to work more generically as if it was a lookup, it might be easier to actually store the data from query 2 into a lookup. I think a more generic solution with eventstats could look like this:

... your current query...
| eval CriticalityNew=if(query=2,Criticality,null())
| eventstats values(CriticalityNew) as CriticalityNew by Id,sourcetype
| eval Criticality = coalesce(CriticalityNew,Criticality)

That first eval to copy Criticality to CriticalityNew can perhaps also be achieved by adjusting your current query, such that for rows from query2 the Criticality field is called CriticalityNew.

Otherwise, a solution using join might work as well:

...query 1...
| join type=left Id, sourcetype [
search ...query 2...
| mvexpand Id
| rename Criticality as CriticalityNew
| table Id, sourcetype,CriticalityNew
]
| eval Criticality = coalesce(CriticalityNew,Criticality)