Archive

How do you change a row by checking another row?

veerendra_modi
Loves-to-Learn

alt text

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.

Tags (1)
0 Karma
1 Solution

FrankVl
Ultra Champion

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)

View solution in original post

0 Karma

FrankVl
Ultra Champion

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)

View solution in original post

0 Karma

FrankVl
Ultra Champion

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

0 Karma

veerendra_modi
Loves-to-Learn

Thanks Frank
what does it mean by Id=query here

0 Karma

FrankVl
Ultra Champion

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

0 Karma

veerendra_modi
Loves-to-Learn

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

0 Karma

FrankVl
Ultra Champion

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

0 Karma

veerendra_modi
Loves-to-Learn

Thanks Kamlesh can you help me with the sample code.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

sure @veerendra_modi

Please share your search, sample data and expected output.

0 Karma

veerendra_modi
Loves-to-Learn

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

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.