Splunk IT Service Intelligence

how to append two search with same index ?

Communicator

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 !

Labels (1)
Tags (1)

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

Communicator

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

 

Legend

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

Communicator

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 : 

mah_0-1603114624956.png

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
]

 

Legend

Hi @mah,

if you run 

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

have you results?

Ciao.

Giuseppe

0 Karma

Communicator

yes I do :

mah_0-1603122974053.png

 

0 Karma

Communicator

hi @to4kawa @gcusello ,

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

0 Karma

Ultra Champion

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.


0 Karma

Communicator

Can I share a log without it being visible to others, but just to you?

0 Karma

Ultra Champion
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?

SplunkTrust
SplunkTrust

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

Legend

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

Communicator

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

 

Legend

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

Communicator

it doesn't work at all.

It return me : 

_time Id values but the rest is empty cell:

mah_0-1602854936740.png

 

 

0 Karma

Legend

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 

Communicator

yes for sourcetype "ccc", there is fields Id, name, price, country,... I can do the table : 

mah_0-1602861119305.png

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

 

Legend

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

Communicator

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

0 Karma

Legend

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