How do I combine my results for two searches to display on a bar graph for comparison?

[vrmandadi]

Hello,

I am trying to join two searches
1)which gives the count for the last three months and
2)which gives the count for the latest month

index=*d*  source="sample_data"  earliest=-3mon@mon latest=-1@mon | rex field=MESSAGE_QUALIFIER "(?\w+)_"| stats count by MESSAGE_TYPE | rename count as last3 | append [search index=*d* source="sample_data"  earliest=-1mon@mon latest=now | rex field=MESSAGE_QUALIFIER "(?\w+)_" | stats count by MESSAGE_TYPE | rename count as last1]

Current output:

MESSAGE_TYPE   last3   last1
CMO            115   
CMP            75    
INQ            403   
NIT            23    
REV            23    
CMP                   15 

Expected output:

MESSAGE_TYPE   last3   last1
CMO            115   
CMP            75    15
INQ            403   
NIT                    23    
REV                    23   

The expected output when selecting a bar graph should give two bars for CMP side by side, thus making it easy for comparison, right?

[somesoni2 · Accepted solution]

Give these a try

Updated
Recommended

index=*d* source="_sample_data" earliest=-3mon@mon latest=now 
| eval Period=if( _time<relative_time(now(),"-1mon@mon") ,"last1","last3") 
| rex field=MESSAGE_QUALIFIER "(?<MESSAGE_TYPE>\w+)_"  | chart count over MESSAGE_TYPE by Period 

Option 2

index=*d* source="_sample_data" earliest=-3mon@mon latest=-1@mon | rex field=MESSAGE_QUALIFIER "(?<MESSAGE_TYPE>\w+)_" | stats count as last3 by MESSAGE_TYPE | append [searchindex=*d* source="_sample_data"  earliest=-1mon@mon latest=now | rex field=MESSAGE_QUALIFIER "(?<MESSAGE_TYPE>\w+)_" | stats count as last1 by MESSAGE_TYPE ]
| stats values(*) as * by MESSAGE_TYPE

[vrmandadi]

Hello Somesh,

I tried both the queries both the queries gives only the cmp and its count ,but it does not get the results as attached in the screen shot

Sorry I just updated the question both the searches have same sources,and sourcetype

[somesoni2]

Is the rex also same for both the searches OR they differ?? (field=MESSAGE_QUALIFIER to be used in both OR one to use field=MESSAGE_QUALIFIER and other to use field=EWS_MESSAGE_QUALIFIER

[vrmandadi]

o yes it is same for both of them..once again sorry

[somesoni2]

Well that makes it easy. Try the updated answer.

[vrmandadi]

Thanks a lot Somesh,just one more question.

what if I want to do the same for a day in a week and compare to the same day in the previous week like monday of this week to the monday of the previous week

Thanks a ton

[vrmandadi]

can You explain what the below command does

| eval Period=if( _time

[jmallorquin]

Hi,

A easy way to do that is just sum 7,884e+6 seconds to all the events in the query -3m just to bring the events to the same time of the other query.

Then with time chart you will be able to compare the 3 month with the last month.

Hope i help you.

[jmallorquin]

Hi,
No problem try this:

index=d source="sample_data" earliest=-3mon@mon latest=-1@mon | rex field=MESSAGE_QUALIFIER "(?\w+)"  | eval _time=_time+7884000 | eval last="Last 3 month" | append [search index=d source="sample_data_for_splunk_dashboard.json" earliest=-1mon@mon latest=now | rex field=EWS_MESSAGE_QUALIFIER "(?\w+)_"  | eval last="Last month"] | timechart count by last

Hope i help you

[vrmandadi]

I tried but it is not giving the results as expected in the attached image

[vrmandadi]

I am very new to splunk and I did not understand your answer