Hi guys!
So I have a pretty detailed splunk search to get the five most active OOID's in my data. I was wondering if it would be possible to display the same results, but display it the past 7 days. Is this possible?
Here is my query:
sourcetype=doccloud_catalina "Document workspace"
| rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)"
| chart count by OOID action
| addtotals | sort 5 -Total
Hi splunkman341,
This is possible! try with this query:
sourcetype=doccloud_catalina "Document workspace" earliest=-7d
| rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)"
| chart count by OOID action
| addtotals | sort 5 -Total
Sure.
Dropping the "Total" for the moment, this would simply be:
sourcetype=doccloud_catalina "Document workspace"
| rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)"
| eval OOID_action=OOID . "--" + action
| timechart count by OOID_action
If you render this as a stacked column chart or stacked area chart, arguably you don't need the Total calculated because the stacked columns add up to that total.
If you render this as a line chart, you might still want the total though. If that's the case it's pretty simple, you would just add | addtotals
at the end
sourcetype=doccloud_catalina "Document workspace"
| rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)"
| eval OOID_action=OOID . "--" + action
| timechart count by OOID_action
| addtotals
And by default timechart will only show up to 10 of the "split-by" values, ie 10 values of OOID_action. Everything else it dumps in a value called "OTHER". To force it to show more, and banish OTHER from your results, you would put limit=100
or somesuch into the timechart clause, like this. | timechart count by OOID_action limit=100
UPDATE.
OK I understand better now. The trick here is that you want to calculate and filter based on a broader set of criteria, ignoring the actions, but then you want to retain all the detail about actions throughout, to show in the final result. Whenever there's this kind of "loss of detail" catch-22, you should think eventstats and streamstats. Generally the solutions will result in swapping in one or the other in place of a stats command.
sourcetype=doccloud_catalina "Document workspace"
| rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)"
| bin _time span=1d
| stats count by _time OOID action
| eventstats sum(count) as totalPerOOIDCount by OOID _time
| sort - _time - totalPerOOIDCount
| streamstats dc(OOID) as OOIDs by _time
| search OOIDs<6
| eval timePlusOOID=_time . " --- " . OOID
| xyseries timePlusOOID action count
| eval timePlusOOID=split(timePlusOOID ," --- ")
| eval _time=mvindex(timePlusOOID,0)
| eval OOID=mvindex(timePlusOOID,1)
| fields - timePlusOOID
| table _time OOID *
Let's walk through it, starting at | stats count by _time OOID action
. This gives us the number of events for each combination of day (cause we binned _time to day), OOID and action.
Next, eventstats sum(count) as totalPerOOIDCount by OOID _time
goes and for each combination of OOID and day, it adds up all the counts as a new field and puts it on all those rows. End result is the same rows that went into eventstats, except they now all have a "totalPerOOIDCount" field on them.
Now we need to filter down to just the top five overall OOID's per day. We sort by the totalPerOOIDCount and day so within each day the OOIDS with the highest total count for that day will be first.
Anyway, then we use streamstats to walk through the set and count the number of distinct OOID's it sees per day. Then we use a trusty search command to throw away all those after number 5. Because of how we sorted, we'll thus retain only the top 5 overall OOID's per day.
Now things are a little weird. I think you want to end up with a table like
time ooid created updated deleted moved
4/10/2014 bob 127 21 513 123
4/10/2014 alice 12 31 413 123
4/10/2014 mildred 922 41 313 123
4/10/2014 agnes 127 51 213 123
4/10/2014 elihu 127 61 113 123
4/11/2014 alice 127 21 213 123
....
And normally you'd use the chart command to kinda "spray out" the action values across the top. However the chart command will only let you have one "over" field (boo). in other words it would be nice to do chart count over _time OOID by action
but we can't.
So instead we have to do this crazy hack.
| eval timePlusOOID=_time . " --- " . OOID
| xyseries timePlusOOID action count
| eval timePlusOOID=split(timePlusOOID ," --- ")
| eval _time=mvindex(timePlusOOID,0)
| eval OOID=mvindex(timePlusOOID,1)
| fields - timePlusOOID
In spirit, that whole block is doing our chart count over _time OOID by action
for us.
And that's kinda it. Making the very daring assumption that I have finally grasped your requirements.
Maybe there's a shorter path but I haven't been able to think of one.
Thanks for your answer but I wanted what I had before exactly the way it is, and in addition, to display those same results for the past 7 days. This search does not include the five most active OOID's. It instead shows what action each OOID performed day by day for the past 7 days.
Oh I see. I missed the "only the top 5 OOID values" nuance. Yes this can be done. GIve me a second and I'll update my answer.
Yes please!
Hi splunkman341,
This is possible! try with this query:
sourcetype=doccloud_catalina "Document workspace" earliest=-7d
| rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)"
| chart count by OOID action
| addtotals | sort 5 -Total
Thanks for your answer but I wanted it to display inside the time chart! Would that be possible?
Try with this! It corcern the seven last days!
sourcetype=doccloud_catalina "Document workspace" earliest=-7d
| rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)" |timechart count by OOID action | addtotals | sort 5 -Total
in your XML code?
No, in this query! Sideview has it partially right, except I only want for the five most active OOID's actions as opposed to displaying each OOID's action