Hello,
I need to execute a search where the 5 fields from one search would be used to search another data. Also, I need to get two fields from inner search to make a report but those fields would not be used in the outer search.
I have renamed those fields but still the search for the outer search does not give any result.
Example
index=my_com sourcetype=my_desk [ search index=my_com sourcetype=my_desk query=*database_data* |rename transaction as txn, values as val |fields workstation, date_min, date_second, date_hour, date_day, txn, val ] query=*file_data* values!=val |table workstation, txn, val, transaction, values
But the above search is not working
Start by joining the search data like this:
index=my_com sourcetype=my_desk (query=*file_data* values!=val) OR query=*database_data*
Then merge it with one of these:
| stats values(*) AS * first(date_min) AS date_min first(date_second) AS date_second first(date_day) AS date_day list(values) AS vals BY workstation
| stats list(*) AS * first(date_min) AS date_min first(date_second) AS date_second first(date_day) AS date_day list(values) AS vals BY workstation
| stats values(*) AS * first(date_min) AS date_min first(date_second) AS date_second first(date_day) AS date_day list(values) AS vals BY transaction
| stats list(*) AS * first(date_min) AS date_min first(date_second) AS date_second first(date_day) AS date_day list(values) AS vals BY transaction
You should be able to take it from there.
You'll want to explore searches like this:
index=my_com sourcetype=my_desk (query=*file_data* ) OR (query=*database_data*)
| eval val=if(match(query,"database_data"),db_val,file_val)
| stats values(db_val) as db_val values(file_val) as file_val min(_time) as earliest max(_time) as latest values(workstation) by transaction
| where db_val!=file_val
As soon as you need field values from the inner search to make it to the final results, subsearches are not the way to go. Join is often what people think of next, but to make a long story short it's far better to get there with stats than with join.
Note: you will CERTAINLY have to play around with this search before it's right. I've made some guesses here that are probably wrong. In particular you may very well need to switch workstation from a 'values' field to a 'group by' field, eg changing values(workstation) by transaction
in that stats clause to by workstation, transaction
instead.
Question: What does your data look like? That sub search essentially pulls in search parameters to filter out more events based on the values of those fields. If any of those are null/empty, you might be filtering out everything.
What you might want to look into is using a left join. Possibly first using
index=my_com sourcetype=my_desk query=file_data values!=val |
join type=left workstation [ ] |
join type=left date_min [
Hi
The position of the your subsearch seems wrong... when you are using a subsearch at this position, it's a filter for the "outer" search.
To get results from the second search you should use a join. Also an eval with the subsearch could work.
Kind Regards
SierraX