I am trying to obtain the DailyTransactions and WeeklyTranscations . The following is my Query ->
index=INDEXA sourcetype=*XYZ* earliest=-7d@d latest=@d "service=randomservice"
| stats first(_time) as _time, values(A), values(B), values(C), values(D), values(ProductType) AS ProductType, values(E) AS Decision by productId
| search (ProductType=ProductTypeA OR ProductType=ProductTypeB OR ProductType=ProductTypeC OR ProductType=ProductTypeD)
| stats values(count) as WeeklyTransactions by ProductType Decision
| appendcols
[search index=INDEXA sourcetype=*XYZ* earliest=-1d@d latest=@d "service=randomservice"
| stats first(_time) as _time, values(A), values(B), values(C), values(D), values(ProductType) AS ProductType, values(E) AS Decision by productId
| search (ProductType=ProductTypeA OR ProductType=ProductTypeB OR ProductType=ProductTypeC OR ProductType=ProductTypeD)
| stats count as DailyTransactions by ProductType Decision ]
This is the result i am expecting to see ->
ProductType Decision WeeklyTransactions DailyTransactions
ProductTypeA Decision1 10 3
ProductTypeA Decision2 25 0
ProductTypeB Decision2 13 4
ProductTypeC Decision3 85 20
This is the result i'm obtaining on running my query ->
ProductType Decision WeeklyTransactions DailyTransactions
ProductTypeA Decision1 10 3
ProductTypeA Decision2 25 4
ProductTypeB Decision2 13 20
ProductTypeC Decision3 85
One could, if one were so disposed to do so, use eventstats to get your total over the whole period, then relative_time() functions to re-filter out all the items older than what you actually want.
So for instance, here's an example on my firewall data.
index=fw earliest=-7d@d latest=@d direction=inbound OR direction=outbound
| eventstats count AS TotalCount
| eval ButIReallyOnlyWantToday = relative_time(now(), "-1d@d")
| where _time>ButIReallyOnlyWantToday
| stats count AS Count, max(TotalCount) AS Total
ButIReallyOnlyWantToday
using relative time, putting that number at the start of yesterday.So for your specific problem... There's a lot of extra detail and you might have some logic off, but I think you can modify the above to suit. I'll try below. Keep in mind, you can always (and indeed it's a good idea!) run the search command by command, trying to understand each step and what it does. So start with the first few lines, and add line by line watching how it changes. That is really useful to help catch simple mistakes!
Before I do that, in looking at your search - you do a stats first (line 2) THEN filter out some of those? That may be what you need, but usually I'd filter first, then aggregate (makes the whole pipeline more efficient). Still, I don't know your data so if that's right, that's fine with me. You could carefully compare both ways and see. Also, you seem to take a list of producttypes then count the list items. Maybe that's what you want, maybe not. dc() (distinct count) may be a better choice, but I'm not sure exactly what you are doing in that section so don't pay any attention to me unless I speak sense.
Anyway, so, my try:
search index=INDEXA sourcetype=*XYZ* earliest=-7d@d latest=@d "service=randomservice"
| stats first(_time) as _time, values(A), values(B), values(C), values(D), values(ProductType) AS ProductType, values(E) AS Decision by productId
| search (ProductType=ProductTypeA OR ProductType=ProductTypeB OR ProductType=ProductTypeC OR ProductType=ProductTypeD)
| eventstats count as WeeklyTransactions by ProductType Decision
| eval ButIReallyOnlyWantToday = relative_time(now(), "-1d@d")
| where _time>ButIReallyOnlyWantToday
| stats count AS DailyTransactions, max(WeeklyTransactions) AS WeeklyTransactions
That may do it. If not, could you try it line by line and post back with the first step that seems to do the wrong thing? That's a complex enough search that it could take a little bit of work to get it just right!
Happy Splunking!
Rich
One could, if one were so disposed to do so, use eventstats to get your total over the whole period, then relative_time() functions to re-filter out all the items older than what you actually want.
So for instance, here's an example on my firewall data.
index=fw earliest=-7d@d latest=@d direction=inbound OR direction=outbound
| eventstats count AS TotalCount
| eval ButIReallyOnlyWantToday = relative_time(now(), "-1d@d")
| where _time>ButIReallyOnlyWantToday
| stats count AS Count, max(TotalCount) AS Total
ButIReallyOnlyWantToday
using relative time, putting that number at the start of yesterday.So for your specific problem... There's a lot of extra detail and you might have some logic off, but I think you can modify the above to suit. I'll try below. Keep in mind, you can always (and indeed it's a good idea!) run the search command by command, trying to understand each step and what it does. So start with the first few lines, and add line by line watching how it changes. That is really useful to help catch simple mistakes!
Before I do that, in looking at your search - you do a stats first (line 2) THEN filter out some of those? That may be what you need, but usually I'd filter first, then aggregate (makes the whole pipeline more efficient). Still, I don't know your data so if that's right, that's fine with me. You could carefully compare both ways and see. Also, you seem to take a list of producttypes then count the list items. Maybe that's what you want, maybe not. dc() (distinct count) may be a better choice, but I'm not sure exactly what you are doing in that section so don't pay any attention to me unless I speak sense.
Anyway, so, my try:
search index=INDEXA sourcetype=*XYZ* earliest=-7d@d latest=@d "service=randomservice"
| stats first(_time) as _time, values(A), values(B), values(C), values(D), values(ProductType) AS ProductType, values(E) AS Decision by productId
| search (ProductType=ProductTypeA OR ProductType=ProductTypeB OR ProductType=ProductTypeC OR ProductType=ProductTypeD)
| eventstats count as WeeklyTransactions by ProductType Decision
| eval ButIReallyOnlyWantToday = relative_time(now(), "-1d@d")
| where _time>ButIReallyOnlyWantToday
| stats count AS DailyTransactions, max(WeeklyTransactions) AS WeeklyTransactions
That may do it. If not, could you try it line by line and post back with the first step that seems to do the wrong thing? That's a complex enough search that it could take a little bit of work to get it just right!
Happy Splunking!
Rich
Thanks for the detailed response rich7177, really appreciate the time and effort you put into it.
Appendcols does not match any fields together, it just appends the columns from the searches together. You would need to use |join ProductType Decision type=left