Splunk Search
Highlighted

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

Explorer

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
Highlighted

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

Legend

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
Highlighted

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

Explorer

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 P1job.json and P1task.json.

1.json
{
"ANAME" : "aaa",
"dept" : [{
"D
NAME" : "D1",
"PROJECT" : [{
"PROJECT_NAME" : "P1"
}]
}]
}

P1job.json
{
"JOB
NUM" : "1",
"JOBTIME" : "1/1/2020",
"JOB
STATUS" : "PASS",
"JOB_DURATION" : "304"
}

P1task.json
{
"TASK
NUM" : "1",
"TASKTIME" : "10/2/2020",
"TASK
STATUS" : "FAIL",
"TASK_DURATION" : "239"
}

I want a table consisting of
ANAME,DNAME,PROJECTNAME,JOBNUM,JOBTIME,JOBSTATUS,TASKNUM,TASKTIME,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
Highlighted

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

Legend

Hi @anooshac,
what's the key to correlate P1, P1job and P1task?
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
Highlighted

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

Explorer

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,P1job.json,P1task.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
Highlighted

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

Explorer

Hi the 3 files are 1.json, P1job.json, P1task.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
Highlighted

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

Legend

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
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

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
Highlighted

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

Explorer

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

0 Karma