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

[anooshac]

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?

[manjunathmeti]

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

[anooshac]

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

[to4kawa]

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

[anooshac]

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?

[to4kawa]

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

[anooshac]

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

[to4kawa]

This is sample.
try spath with your query.

[gcusello]

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

[anooshac]

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

[gcusello]

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

[anooshac]

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?

[gcusello]

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

[anooshac]

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

[anooshac]

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 ?