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.
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)
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)
Maybe I'm missing something, but wouldn't that be as simple as: | eval Criticality=if(Id=query AND sourcetype="S2","P2",Criticality)
Thanks Frank
what does it mean by Id=query here
It checks if those 2 columns match. That is how I interpreted "if its Id is available in the 4th Column".
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
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.
@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
Thanks Kamlesh can you help me with the sample code.
sure @veerendra_modi
Please share your search, sample data and expected output.
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