topic Re: How to combine multiple reports into one report from same index? in Reporting

How to combine multiple reports into one report from same index?

limalbert — Mon, 08 Jun 2020 23:12:07 GMT

Hey guys,

Is it possible to combine 3 reports (bar charts) from the same index into one report (bar chart)?

Thanks in advance!

Edit:
Report 1:

index=app_fig keywordA keywordB* keywordC
| rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
| rex "type\=(?<field1>[^,]*)"
| rex "userId:(?<user>\w*)\_"
| rex "workOrder=(?<woNum>\w*)\," 
| search referral=*
| chart dc(woNum) by currentDate, field1

Report 2:
index=app_fig keywordD keywordE keywordF

| rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
| rex "fieldType=(?<field1>[^,]*)"
| rex "userId:(?<user>\w*)\_"
| rex "workOrderNumber=(?<woNum>\w*)\,"
| chart dc(woNum) by currentDate, field1

Report 3:

index=app_fig keywordG keywordH
| rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
| rex "fieldName\":\"(?<field1>[^\"]*)"
| rex "userId:(?<user>\w*)\_"
| rex "wo:(?<woNum>\w*)_" 
| chart dc(woNum) by currentDate, field1

Re: How to combine multiple reports into one report from same index?

woodcock — Fri, 01 Dec 2017 21:21:16 GMT

give us something with which to work. What are your 3 searches?

Re: How to combine multiple reports into one report from same index?

woodcock — Fri, 01 Dec 2017 21:21:28 GMT

Yes, it is possible.

Re: How to combine multiple reports into one report from same index?

somesoni2 — Fri, 01 Dec 2017 21:24:05 GMT

Depends upon the queries of those 3 reports. As long as their filter/search logic can be combined, they can too.

Re: How to combine multiple reports into one report from same index?

limalbert — Fri, 01 Dec 2017 21:26:49 GMT

Are you asking the reports' name? Or searches within report?

Re: How to combine multiple reports into one report from same index?

limalbert — Fri, 01 Dec 2017 21:29:07 GMT

Ok. I know that it can be combined depending of the query. I was thinking of combining it by report name, since each reports is created with below. But, the regex for each field aren't unique between reports.
chart dc(woNum) by currentDate, referral

Re: How to combine multiple reports into one report from same index?

somesoni2 — Fri, 01 Dec 2017 21:34:14 GMT

That can be made same (may be extract using different names and use eval with coalesce to create a common field). You'd get a dead-straight answer if you could share your searches/queries.

Re: How to combine multiple reports into one report from same index?

limalbert — Tue, 05 Dec 2017 14:38:37 GMT

Sorry for the late update. I posted the 3 reports code in my original question.

Re: How to combine multiple reports into one report from same index?

somesoni2 — Tue, 05 Dec 2017 15:33:47 GMT

Try this

index=app_fig (keywordA keywordB* keywordC) OR (keywordD keywordE keywordF) OR (keywordG keywordH)
| rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
 | rex "(type|fieldType)\=(?<field11>[^,]*)"
 | rex "fieldName\":\"(?<field12>[^\"]*)"
 | rex "(workOrder|workOrderNumber)=(?<woNum1>\w*)\,"
 | rex "wo:(?<woNum2>\w*)_"  
| eval field1=coalesce(field11,field12)
| eval woNum=coalesce(woNum1,woNum2)
| chart dc(woNum) by currentDate, field1

Re: How to combine multiple reports into one report from same index?

limalbert — Tue, 05 Dec 2017 19:23:14 GMT

Hey,

This works! But, I'm getting NULL from field11, and VALUE from field12.
I have never used coalesce command before. What does this actually do?

Re: How to combine multiple reports into one report from same index?

somesoni2 — Tue, 05 Dec 2017 19:36:35 GMT

The coalesce (like oracle coalesce) takes the first non-null value. If instead of null values it has literal NULL string, try this variation.

index=app_fig (keywordA keywordB* keywordC) OR (keywordD keywordE keywordF) OR (keywordG keywordH)
 | rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
  | rex "(type|fieldType)\=(?<field11>[^,]*)"
  | rex "fieldName\":\"(?<field12>[^\"]*)"
  | rex "(workOrder|workOrderNumber)=(?<woNum1>\w*)\,"
  | rex "wo:(?<woNum2>\w*)_"  
 | eval field1=if(searchmatch("keywordG keywordH"), field12,field11)
 | eval woNum=if(searchmatch("keywordG keywordH"), woNum2,woNum1)
 | chart dc(woNum) by currentDate, field1

Re: How to combine multiple reports into one report from same index?

limalbert — Tue, 05 Dec 2017 19:42:12 GMT

It still return VALUE and NULL.

Re: How to combine multiple reports into one report from same index?

somesoni2 — Tue, 05 Dec 2017 19:45:43 GMT

So you're getting column with name as "VALUE" and "NULL"?? Can you post the actual query you're running? (mask anything sensitive)

Re: How to combine multiple reports into one report from same index?

limalbert — Tue, 05 Dec 2017 19:50:47 GMT

Yes, it return VALUE and NULL, but that's not the only field values. Sorry for the misunderstanding.
Oh, I think I just fix it. The some values in the field contain no data, so it returns NULL and VALUE. I add below.

|search field11=*
| search field12 = "fieldValue1" OR "fieldValue2"

Re: How to combine multiple reports into one report from same index?

limalbert — Tue, 05 Dec 2017 20:04:33 GMT

So, that only works to exclude NULL, but it doesn't work to exclude VALUE.

Re: How to combine multiple reports into one report from same index?

somesoni2 — Tue, 05 Dec 2017 20:07:39 GMT

How about this

index=app_fig (keywordA keywordB* keywordC) OR (keywordD keywordE keywordF) OR (keywordG keywordH)
  | rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
   | rex "(type|fieldType)\=(?<field11>[^,]*)"
   | rex "fieldName\":\"(?<field12>[^\"]*)"
   | rex "(workOrder|workOrderNumber)=(?<woNum1>\w*)\,"
   | rex "wo:(?<woNum2>\w*)_"  
| search field11=* OR field12=* 
  | eval field1=if(searchmatch("keywordG keywordH"), field12,field11)
  | eval woNum=if(searchmatch("keywordG keywordH"), woNum2,woNum1)
  | chart dc(woNum) by currentDate, field1

Re: How to combine multiple reports into one report from same index?

limalbert — Tue, 05 Dec 2017 20:14:34 GMT

It still has VALUE as one of the field value, and the NULL is excluded.

Re: How to combine multiple reports into one report from same index?

limalbert — Tue, 05 Dec 2017 20:40:15 GMT

Issue is fixed. Below is the solution. Thank you! I used the one with coalesce. It's much cleaner

|search field1 != ""

Re: How to combine multiple reports into one report from same index?

sherifhmdy — Wed, 26 Feb 2020 16:15:10 GMT

how can we do it?