topic Re: how to append two search with same index ? in Splunk ITSI

how to append two search with same index ?

mah — Fri, 16 Oct 2020 09:52:40 GMT

hi,

I create a search with a join, but I want to know if there is a better way to do (append ?) :

index=AAA sourcetype="bbb"
| table _time Id
| join Id
[ search index=AAA sourcetype="ccc"
| table Id name price
]

Can you help me ?

thanks !

Re: how to append two search with same index ?

gcusello — Fri, 16 Oct 2020 09:57:11 GMT

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

Re: how to append two search with same index ?

mah — Fri, 16 Oct 2020 10:34:10 GMT

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

Re: how to append two search with same index ?

gcusello — Fri, 16 Oct 2020 10:58:55 GMT

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

Re: how to append two search with same index ?

mah — Fri, 16 Oct 2020 13:29:04 GMT

it doesn't work at all.

It return me :

_time Id values but the rest is empty cell:

Re: how to append two search with same index ?

gcusello — Fri, 16 Oct 2020 14:14:30 GMT

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

Re: how to append two search with same index ?

mah — Fri, 16 Oct 2020 15:19:20 GMT

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

Re: how to append two search with same index ?

gcusello — Fri, 16 Oct 2020 15:32:46 GMT

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

Re: how to append two search with same index ?

mah — Fri, 16 Oct 2020 15:50:09 GMT

Some fields mix lower and upper case but I take care of writting it as they are.

Re: how to append two search with same index ?

gcusello — Fri, 16 Oct 2020 15:55:42 GMT

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

Re: how to append two search with same index ?

mah — Fri, 16 Oct 2020 16:06:12 GMT

It still not working.

In the stats command, as soon as I mix fields from both sourcetype, it doesn't work.

Re: how to append two search with same index ?

somesoni2 — Fri, 16 Oct 2020 19:00:37 GMT

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

Re: how to append two search with same index ?

to4kawa — Sat, 17 Oct 2020 22:15:43 GMT

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.

Re: how to append two search with same index ?

mah — Mon, 19 Oct 2020 09:06:27 GMT

hi @to4kawa @gcusello,

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 ...

Re: how to append two search with same index ?

to4kawa — Mon, 19 Oct 2020 10:34:45 GMT

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?

Re: how to append two search with same index ?

gcusello — Mon, 19 Oct 2020 12:09:17 GMT

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

Re: how to append two search with same index ?

mah — Mon, 19 Oct 2020 13:58:16 GMT

Hi @to4kawa @gcusello

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
]

Re: how to append two search with same index ?

gcusello — Mon, 19 Oct 2020 14:04:32 GMT

Hi @mah,

if you run

index=AAA sourcetype="bbb" | table level app duration url userNum_Id Sessionid

have you results?

Ciao.

Giuseppe

Re: how to append two search with same index ?

mah — Mon, 19 Oct 2020 15:56:30 GMT

yes I do :

Re: how to append two search with same index ?

mah — Thu, 22 Oct 2020 07:40:28 GMT

hi @to4kawa @gcusello ,

if you try your solutions on your side, do you have a table with all fields completed ?