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

[limalbert]

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

[somesoni2 · Accepted solution]

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

[limalbert]

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?

[somesoni2]

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

[limalbert]

It still return VALUE and NULL.

[somesoni2]

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

[limalbert]

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"

[limalbert]

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

[somesoni2]

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

[limalbert]

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

|search field1 != ""

[limalbert]

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

[somesoni2]

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

[limalbert]

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

[somesoni2]

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.

[limalbert]

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

[woodcock]

Yes, it is possible.

[sherifhmdy]

how can we do it?

[woodcock]

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

[limalbert]

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