Details under count

[spm807]

How do I show details of individual records in a count total? I have a query that counts events, and then returns the total count when it's above a specified threshold. How do I display the individual events that constitute that count total? But only for those totals where the count exceeds the threshold?

[spm807]

Simple and very helpful. Thank you.

[isoutamo]

Two additions. When you are playing with data and creating your final queries you could use verbose mode. Then you can see all events in events tab even after using transform commands.
Another command which you could use to calculate subtotals is appendpipe.

[spm807]

I have been using verbose mode for the event details. I have not used appendpipe, though, so I will look into that. Thank you!

[spm807]

Very helpful, thank you. I will now play with eventstats, to try to refine the results. Using stats I had two rows, one for each where the count > X. Now with eventstats I get individual rows for each of the events that made up the two stats rows. So, for X=4, I had 2 rows with counts of 9 and 5, respectively. Now I'm seeing 14 events returned. Definitely closer to what I'm looking for.

[livehybrid]

I guess this is because of the "by field" - Are you wanting to count by another field or just a total count? you can remove the by <fieldName> if you dont need that.

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[spm807]

I added sort by uid, and it did. It still shows the uid just as part of the big event record. What would be nice would be if I could pull it out to the side, so the recipient of the report could quickly see that uid 12345 had 5 events, and uid 67890 had 9 events, rather than just the detail event records. In other words (mocked up output):

uid 12345       total number of events: 5

   event detail1

   event detail2

   event detail3 

   etc.

uid 67890       total number of events: 9

    event detail1

    event detail2

    event detail3

    etc.

Also, in case you couldn't tell, I am a beginner at Splunk. Thank you for your help.

[PickleRick]

Splunk is not Excel 😉

But seriously. For Splunk every result row is... well, a separate row.

Depending on the actual use case you could cheat a bit but the way to do so would depend on the detailed desired outcome. You could do something like

<your_initial_search>
| stats values(_raw) as "Event Details" by UID

(and maybe do some magic with custom CSS in dashboard to "un-align" the table a bit).

But that will give you just raw events.

If you want to have separate fields from those "content" events... that's gonna get tricky and ugly (and un-splunky because the result will not have any internal logical consistency and will be only for presentation purposes).

An example using my windows events index:

index=winevents

This is just the base search - nothing to write home about

| sort EventID

That should also be pretty obvious - we want the events grouped by EventID field. You can add subsequent sort field(s) if you want them sorted within those groups.

| streamstats window=1 current=f last(EventID) as previousID

Now the magic starts. We're copying the EvenID value from previous event to the current one. The previous one is called previousID.

| eval splittable=if(NOT EventID=previousID,mvappend("1","0"),0)

If the current EventID is the same as previous one (which we carried over in last step) it means that it's not the first result with given EventID. If those values are different (or - in case of the very first result row, the previousID is empty; that's why the condition is in the form of NOT a=b instead of a!=b), this is a first row of results for given EventID. Depending on which case it is, we create a temporary field with either a single value (whether it's a zero, or anything else is not important; I just chose zero) or two values of which the second one must be the same as for the "not-first" row.

We're doing this because Splunk cannot just arbitrarily add rows. So we're doing the trick with multiple values in one result (so called multivalue field) so we can split that result into two separate ones.

And this we do by calling:

| mvexpand splittable

Now the first row for each unique EventID, which we marked with two values in the field called "splittable" got split into two separate rows with one value each. The row which had just one value was left unchanged. What is also important is that the order of the split results remains the same as the order of the values in the field on which we're calling mvexpand.

So now all that's left is to find the "header" row and clean all "non-header" values. And clean the "header" field (in our case the EventID field) for all "non-header" rows.

| foreach * 
    [ eval <<FIELD>>=case(splittable=1 AND "<<FIELD>>"="EventID",EventID,splittable=1,"",splittable=0 AND "<<FIELD>>"="EventID","",1=1,<<FIELD>>) ]

We may now remove the temporary fields which we don't need anymore (this step is optional if we're limiting displayed fields to a strictly defined set; if we just list all fields, we might want to do this so we don't drag temporary fields along)

| fields - splittable previousID

And now we can present the results as table with either

| table EventID host _time field1 field2 and so on

or simply

| table EventID *

OK. So this exercise was fun but I wouldn't do that this way. After doing all this you're getting a set of results where you have no relationship between the EventID field from one result and the actual "contents" of the events in other results - you can't aggregate the data, (re)sort them or do anything else, maybe except some general statistics. This kind of result is unusable.

As I said at the beginning - Splunk is not Excel and you can't "merge fields".

The only way this could work would be if someone wrote a custom visualization which would do some JS magic comparing values from neighboring rows and fiddling with CSS but so far I don't think anyone did such thing.

[spm807]

Thank you! This is great material, especially for a Splunk beginner. I will digest this for a bit.

[ITWhisperer]

| eventstats count as total by uid
| where total > 4

[livehybrid]

Hi @spm807 

Once you have used "stats" you will have a statistics table with your summarised data output. At this point you are not able to view the original events. 

Depending on your usecase you may find that "eventstats" is more useful?

| eventstats count as total_count by some_field

This will create the count (total_count in this example) whilst still retaining the original events.

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing