Splunk Search

Would it be possible to include time into this chart?

Communicator

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
0 Karma
1 Solution

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

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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 OOIDaction. 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 OOIDaction 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.

Communicator

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.

0 Karma

SplunkTrust
SplunkTrust

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.

Communicator

Yes please!

0 Karma

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

View solution in original post

0 Karma

Communicator

Thanks for your answer but I wanted it to display inside the time chart! Would that be possible?

0 Karma

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?

0 Karma

Communicator

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

0 Karma