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?
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
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
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
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
o yes it is same for both of them..once again sorry
Well that makes it easy. Try the updated answer.
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
can You explain what the below command does
| eval Period=if( _time
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.
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
I tried but it is not giving the results as expected in the attached image
I am very new to splunk and I did not understand your answer