index=AAA (sourcetype="bbb" OR sourcetype="ccc")
| eval Id=upper(coalesce(id_number,id))
| stats values(name) AS name values(price) AS price max(city) AS city values(product) AS product values(amount) AS amount earliest(log_time) AS log_time by Id
If you show us a log of samples of the two source types, we can do it.
I have no idea what's wrong with it, which is why I'm in this situation.
the last solutions you gave me are not working.
Here is a sample of sourcetype "bbb" log : (I need to get the values of all fields present on this sample)
{ [-]
level: Acceptable
app: Prod
duration: 3268
url: https://abc.com
userNum_Id: XXXXXX
Sessionid: YYYYYYY
}
Here is a sample of sourcetype "ccc" log (I need to get the values of all fields present on this sample) :
{ [-]
browser: Firefox
country: France
price: 542
product: abc
userId: XXXXXX
userSessId: YYYYYYY
}
The fields in common in both logs are in green.
The new difficulty is that the base search is filtring on one of field in sourctype "bbb" which gave me a search like :
index=AAA sourcetype="bbb" OR sourcetype="ccc" url=*
The new problem is that this search above gave me only fields of sourcetype "bbb" and the stats no more works ...
Hi @mah,
try something like this:
index=AAA (sourcetype="bbb" OR sourcetype="ccc")
| eval userId=upper(coalesce(userNum_Id,userId)), userSessId=upper(coalesce(Sessionid,userSessId))
| stats
values(level) AS level
values(app) AS app
values(duration) AS duration
values(url) AS url
values(country) AS country
values(browser) AS browser
values(price) AS price
values(product) AS product
by userId userSessId
I think that you already extracted all the fields, if not, you have to add the regexes to extract them.
Ciao.
Giuseppe
I tried :
index=AAA ((sourcetype="bbb" url=*) OR sourcetype="ccc")
| eval userId=upper(coalesce(userNum_Id,userId)), userSessId=upper(coalesce(Sessionid,userSessId))
| stats
values(level) AS level
values(app) AS app
values(duration) AS duration
values(url) AS url
values(country) AS country
values(browser) AS browser
values(price) AS price
values(product) AS product
by userId userSessId
and :
index=AAA ((sourcetype="bbb" url=*) OR sourcetype="ccc")
| eval Id=coalesce(userId,userNum_Id)
| eval Session=coalesce(Sessionid,userSessId)
| stats
values(level) AS level
values(app) AS app
values(duration) AS duration
values(url) AS url
values(country) AS country
values(browser) AS browser
values(price) AS price
values(product) AS product
by userId userSessId
but no success :
The empty fields are present in sourcetype "bbb"
@to4kawa my first request was :
index=AAA sourcetype="bbb"
| table _time Id
| join Id
[ search index=AAA sourcetype="ccc"
| table Id name price
]
Hi @mah,
if you run
index=AAA sourcetype="bbb"
| table level app duration url userNum_Id Sessionid
have you results?
Ciao.
Giuseppe
yes I do :
After all, we can't make a proper query because you're not provided with a proper log, right?
Like whether it's uppercase or lowercase, or how many characters.
Can I share a log without it being visible to others, but just to you?
index=AAA ((sourcetype="bbb" url=*) OR sourcetype="ccc")
| eval Id=coalesce(userId,userNum_Id)
| eval Session=coalesce(Sessionid,userSessId)
| stats as_you_like by Id Session
What was the first query?
Try something like this:
index=AAA (sourcetype="bbb" OR sourcetype="ccc")
| eval Id=coalesce(id_number, Id)
| stats values(name) AS name values(price) AS price max(city) AS city values(product) AS product values(amount) AS amount earliest(log_time) AS log_time by Id
Hi @mah,
I prefer to use join only if there isn't any other solution, because it's very slow and there's the limit of 50,000 results in the subsearch.
The same limit is present also in append command.
So I hint to see a different approach using stats, something like this:
index=AAA (sourcetype="bbb" OR sourcetype="ccc")
| stats earliest(_time) AS _time values(name) AS name max(price) AS price BY Id
Ciao.
Giuseppe
It's not working.
Actually I have in sourcetype "bbb" that fields :
log_time and id_number
And in sourcetype "ccc" that fields:
Id (which contains same values that fields id_number above), name, price, product, amount
I did a rename of id_number in Id, but my request below doesn't work :
index=AAA (sourcetype="bbb" OR sourcetype="ccc")
| rename id_number as Id
| stats values(name) values(price) max(city) values(product) values(amount) by log_time Id
Hi @mah,
the problem is that log_time is present only in one sourcetype, this means that you cannot put it in the BY clause
I don't know the format of log_time, so to use the earliest option, you have to convert it in epochtime.
Anyway, try something like this:
index=AAA (sourcetype="bbb" OR sourcetype="ccc")
| rename id_number as Id
| stats values(name) AS name values(price) AS price max(city) AS city values(product) AS product values(amount) AS amount earliest(log_time) AS log_time by Id
Ciao.
Giuseppe
it doesn't work at all.
It return me :
_time Id values but the rest is empty cell:
Hi @mah,
are you sure to have name, price and city for those Ids?
in other words, have you results if you run:
index=AAA sourcetype=ccc
| search id_number=<one_of_the_ids_in_the_screenshot>
| table id_number name price city
?
Ciao.
Giuseppe
yes for sourcetype "ccc", there is fields Id, name, price, country,... I can do the table :
but this fields above are not in sourcetype "bbb".
for sourcetype "bbb", there is id_number (which contains same values as Id in sourcetype "ccc") and other fields I want to append, like app, sessionid
Hi @mah,
check the field names (they are case sensitive).
then check if Ids and id_numbers are both in lowercase or uppercase or not.
Ciao.
Giuseppe
Some fields mix lower and upper case but I take care of writting it as they are.
Hi @mah,
put attention to the field names (they are case sensitive), if there someone different, rename it.
then modify the search in this way:
index=AAA (sourcetype="bbb" OR sourcetype="ccc")
| rename id_number as Id
| eval Id=upper(id)
| stats values(name) AS name values(price) AS price max(city) AS city values(product) AS product values(amount) AS amount earliest(log_time) AS log_time by Id
Ciao.
Giuseppe