Reporting

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

Path Finder

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
Labels (1)
0 Karma
1 Solution

Revered Legend

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

View solution in original post

Revered Legend

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

View solution in original post

Path Finder

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?

0 Karma

Revered Legend

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
0 Karma

Path Finder

It still return VALUE and NULL.

0 Karma

Revered Legend

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

0 Karma

Path Finder

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"
0 Karma

Path Finder

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

0 Karma

Revered Legend

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
0 Karma

Path Finder

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

|search field1 != ""
0 Karma

Path Finder

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

0 Karma

Revered Legend

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

0 Karma

Path Finder

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

0 Karma

Revered Legend

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.

0 Karma

Path Finder

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

0 Karma

Esteemed Legend

Yes, it is possible.

New Member

how can we do it?

0 Karma

Esteemed Legend

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

0 Karma

Path Finder

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!