How do I combine query and use the certain data from first searching in next searching?

[amzar96]

Hi everyone, I am new with Splunk but I have a knowledge in SQL.

I got a new problem with my search query which is can I use the data from first searching in next searching in the same query? 

Example:

1) index=abc sourcetype="abc:scanning" status="new_scanning" | table serial_id, action

serial_id	action
6577	drive
8872	swim

2) index=abc sourcetype="abc:viewing" status="new_viewing" | table serial_id, person_in_charge, total_person

serial_id	person_in_charge	total_person
6577	Tom	45
8872	Giga	23

may I know what is the best way to get this kind of output?

serial_id	person_in_charge	total_person	action
6577	Tom	45	drive
8872	Giga	23	swim

My idea is to use join or append but I do not know how to "serial_id" in the second searching.

Thanks in advance 😀

[rnowitzki · Accepted solution]

edit 10:20am:  SPL had errors, fixed it.

Hi  @amzar96,

Yes, you could join, but it's pretty slow and hungry.

This should work with join:

index=abc sourcetype="abc:scanning" status="new_scanning" 
| stats values(action) as action by serial_id
| join serial_id [search index=abc sourcetype="abc:viewing" status="new_viewing" | stats values(person_in_charge) as person_in_charge, values(total_person) as total_person by serial_id]

Compare it with:

index=abc (sourcetype="abc:scanning" status="new_scanning")  OR (sourcetype="abc:viewing" status="new_viewing")
| fields person_in_charge, total_person, action, serial_id
| stats values(*) as * by serial_id

I assume that all values are unique and only one per serial_id and that "total_person" is already calculated in the logs. If you need to sum them up you have to use sum(total_person) for example.

Hope it helps
BR
Ralph

[amzar96]

Thanks @rnowitzki , you saved my life! I can run with both query. But, i didn't understand how stats work.

| stats values(*) as * by serial_id

what meant by this above query? 

[rnowitzki]

Hi  @amzar96,

Glad I could help.

To explain this one:

| stats values(*) as * 

values(*) runs the values() command on all available fields which we limited in the lines above to just the ones we need.

as * ist just cosmetic.  values() changes all field names to be e.g. values(total_person), and with as * we change them back to the initial name, total_person e.g.
Try to run | stats values(total_person)by serial_id and you will see what I mean.

Cheers
Ralph

[amzar96]

Understand @rnowitzki . However, I can't retrieve any data from the second search (2nd table). when I query, it give me blank row.

[rnowitzki]

Hi @amzar96 ,

Sorry, I missed your question last week.

Can you please give more detail, you don't get any data for which query? 

Thanks 
Ralph