Reporting

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

limalbert
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

somesoni2
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

somesoni2
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

limalbert
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

somesoni2
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

limalbert
Path Finder

It still return VALUE and NULL.

0 Karma

somesoni2
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

limalbert
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

limalbert
Path Finder

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

0 Karma

somesoni2
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

limalbert
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

limalbert
Path Finder

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

0 Karma

somesoni2
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

limalbert
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

somesoni2
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

limalbert
Path Finder

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

0 Karma

woodcock
Esteemed Legend

Yes, it is possible.

sherifhmdy
New Member

how can we do it?

0 Karma

woodcock
Esteemed Legend

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

0 Karma

limalbert
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...