Splunk Search

how to perform a search with multiple files and combine the results in a single table?

anooshac
Communicator

Hello all,
I have a requirement where i want to get data from multiple files which has different indexes and combine the results into a single table. I tried using all possible ways using appendcols , nested search etc. can anyone please help me in doing this?

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Assuming three files in three different indexes. Replace index names in below search query and try:

index = index_PROJECT | eval PROJECT_NAME = dept{}.PROJECT{}.PROJECT_NAME, D_NAME =  dept{}.D_NAME | join type=left PROJECT_NAME [search index = index_job | rex field = source "/*(?<PROJECT_NAME>[a-zA-Z0-9]+)\w*.json" | join type=left PROJECT_NAME [search index = index_task | rex field = source "/*(?<PROJECT_NAME>[a-zA-Z0-9]+)\w*.json" ] ] | table A_NAME, D_NAME, PROJECT_NAME, JOB_NUM, JOB_TIME, JOB_STATUS, TASK_NUM, TASK_TIME, TASK_STATUS
0 Karma

anooshac
Communicator

thanks @manjunathmeti for the response! i'll try this and let you know.

0 Karma

to4kawa
Ultra Champion
| makeresults 
| eval _raw="{\"A_NAME\":\"aaa\",\"dept\":[{\"D_NAME\":\"D1\",\"PROJECT\":[{\"PROJECT_NAME\":\"P1\"}]}]}
{\"JOB_NUM\":\"1\",\"JOB_TIME\":\"1/1/2020\",\"JOB_STATUS\":\"PASS\",\"JOB_DURATION\":\"304\"}
{\"TASK_NUM\":\"1\",\"TASK_TIME\":\"10/2/2020\",\"TASK_STATUS\":\"FAIL\",\"TASK_DURATION\":\"239\"}"
| eval _raw=replace(_raw,"(?m)^\s?{","#{")
| eval _raw=split(_raw,"#")
| stats count by _raw
`comment("this is your sample, from here, the logic")`
| spath
| stats values(*) as *
0 Karma

anooshac
Communicator

hi @to4kawa , thanks for the response.
I tried this and i'm only getting ,
A_NAME dept{}.D_NAME dept{}.PROJECT{}.PROJECT_NAME
these fields in the table. Why is that so?

0 Karma

to4kawa
Ultra Champion

hi, @annoshac
code sample makes extra space 😞
my answer is updated.

0 Karma

anooshac
Communicator

Still i'm getting only 3 fields in my result.

0 Karma

to4kawa
Ultra Champion

This is sample.
try spath with your query.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @anooshac,
could you share more infos?
there are common fields?
can you describe two or three of these searches to combine?

There are some ways to combine more searches in one table, but you have to define which is the table structure, in other words what do you have on X and Y axes?

Ciao.
Giuseppe

0 Karma

anooshac
Communicator

Hello @gcusello , thanks for the response. I have 3 json files. The names of the PROJECT is in 1.json and the details of PROJECT will be in P1_job.json and P1_task.json.

1.json
{
"A_NAME" : "aaa",
"dept" : [{
"D_NAME" : "D1",
"PROJECT" : [{
"PROJECT_NAME" : "P1"
}]
}]
}

P1_job.json
{
"JOB_NUM" : "1",
"JOB_TIME" : "1/1/2020",
"JOB_STATUS" : "PASS",
"JOB_DURATION" : "304"
}

P1_task.json
{
"TASK_NUM" : "1",
"TASK_TIME" : "10/2/2020",
"TASK_STATUS" : "FAIL",
"TASK_DURATION" : "239"
}

I want a table consisting of
A_NAME,D_NAME,PROJECT_NAME,JOB_NUM,JOB_TIME,JOB_STATUS,TASK_NUM,TASK_TIME,TASK_STATUS
(There are a lot more data in json file but here i posted a small part of it ).
I have tried using subsearch i couldn't able to get the proper result. Can you please help me in this!!

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @anooshac,
what's the key to correlate P1, P1_job and P1_task?
in other words, what's the common field?
is it maybe the file name?
if it's the file name, please share some examples of file names.

Anyway, you should extract the key from file names (using regexes) and use it to correlate the three data types and use stats:

index=your_index
| rex field=source "^(?<key1>[^\._]*)\.json"
| rex field=source "^(?<key2>[^_]*)_job\.json"
| rex field=source "^(?<key3>[^_]*)_task\.json"
| eval key=coalesce(key1,key2,key3)
| stats values(A_NAME) AS A_NAME values(D_NAME) AS D_NAME values(PROJECT_NAME) AS PROJECT_NAME values(JOB_NUM) AS JOB_NUM values(JOB_TIME9 AS JOB_TIME values(JOB_STATUS) AS JOB_STATUS values(TASK_NUM) AS TASK_NUM values(TASK_TIME) AS TASK_TIME values(TASK_STATUS) AS TASK_STATUS BY key
| fields - key

Ciao.
Giuseppe

0 Karma

anooshac
Communicator

Hi the 3 files are 1.json, P1_job.json, P1_task.json. The file 1.json has project name as a field and the other 2 files has project name in their file name. Is there a way that i can achieve this by using sub search?

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @anooshac,
as I said, you have to find a common key, if the project name is a field of first flow and in the name for second and this flow, use it to correlate the three flows, in other words, you can use my search without the first regex becaus it's already a field and you don't need to extract.

You cannot use a subsearch because, as I said, you need a common key for correlation.

Ciao.
Giuseppe

0 Karma

anooshac
Communicator

thank you for the answer, i'll try that and let you know.

0 Karma

anooshac
Communicator

Thank you for the answer i'll try it out and let you know.The main file is not P1.json it's 1.json and P1 is a project name which is a field of that file.
The only term common to all the files is the name of project in the file name that is in this example "P1".
The files are 1.json,P1_job.json,P1_task.json
The project names are in 1.json, which are being used in the file names of other two.
Is there any way that i can do sub search ?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...