topic Re: how to perform a search with multiple files and combine the results in a single table? in Splunk Search

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

anooshac — Thu, 13 Feb 2020 10:16:56 GMT

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?

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

gcusello — Thu, 13 Feb 2020 11:04:40 GMT

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

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

anooshac — Wed, 30 Sep 2020 04:11:31 GMT

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

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

gcusello — Wed, 30 Sep 2020 04:11:37 GMT

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

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

to4kawa — Thu, 13 Feb 2020 12:06:34 GMT

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

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

anooshac — Wed, 30 Sep 2020 04:12:10 GMT

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 ?

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

anooshac — Wed, 30 Sep 2020 04:12:13 GMT

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?

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

manjunathmeti — Fri, 14 Feb 2020 05:31:38 GMT

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

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

anooshac — Fri, 14 Feb 2020 06:12:38 GMT

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

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

anooshac — Wed, 30 Sep 2020 04:12:24 GMT

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?

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

gcusello — Fri, 14 Feb 2020 11:09:47 GMT

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

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

to4kawa — Sat, 15 Feb 2020 00:34:19 GMT

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

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

anooshac — Mon, 17 Feb 2020 11:49:23 GMT

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

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

anooshac — Tue, 18 Feb 2020 05:28:49 GMT

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

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

to4kawa — Tue, 18 Feb 2020 09:26:49 GMT

This is sample.
try spath with your query.