Splunk Enterprise

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

amzar96
Explorer

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
6577drive
8872swim

 

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

serial_id person_in_chargetotal_person
6577Tom45
8872Giga23

 

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

serial_id person_in_chargetotal_personaction
6577Tom45drive
8872Giga23swim

 

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

Thanks in advance 😀

Labels (2)
0 Karma
1 Solution

rnowitzki
Builder

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

--
Karma and/or Solution tagging appreciated.

View solution in original post

rnowitzki
Builder

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

--
Karma and/or Solution tagging appreciated.

View solution in original post

amzar96
Explorer

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? 

0 Karma

rnowitzki
Builder

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

--
Karma and/or Solution tagging appreciated.
0 Karma

amzar96
Explorer

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

 

 

0 Karma

rnowitzki
Builder

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

--
Karma and/or Solution tagging appreciated.
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!