Splunk Search

How do i display only events that aren't "backed out"?

gregbo
Communicator

I have a table of fields with items that are either a Credit or Debit There can be multiples of the same item. Also, an item can be "backed out" by making it a Debit. Here's a sample:

alt text

So, "a" was credited twice and debited once. I'd like the table to show just one "a" (along with the other rows). Any idea how i can do this?

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

|makeresults | eval raw="1,a,C::2,j,C::3,q,C::4,a,C::5,l,C::6,a,D::7,m,C::8,r,C"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<line>[^,]+),(?<item>[^,]+),(?<CorD>.*)$"

| rename COMMENT AS "Everything above generates sample events; everything below is your solution"

| stats count(eval(CorD="C")) AS Credits count(eval(CorD="D")) AS Debits BY item
| eval CorD = Credits - Debits

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

|makeresults | eval raw="1,a,C::2,j,C::3,q,C::4,a,C::5,l,C::6,a,D::7,m,C::8,r,C"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<line>[^,]+),(?<item>[^,]+),(?<CorD>.*)$"

| rename COMMENT AS "Everything above generates sample events; everything below is your solution"

| stats count(eval(CorD="C")) AS Credits count(eval(CorD="D")) AS Debits BY item
| eval CorD = Credits - Debits
0 Karma

gregbo
Communicator

That worked like a charm! Thanks Woodcock!

0 Karma

niketn
Legend

@gregbo, you will have to add more details for Credit and Debit. For example if a is credited three times and Debited only once, will you have two rows for a?

Based on the current question if you want to display only one row per item, you can use dedup

<Your Base Search>
| dedup item
| table line item
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

somesoni2
SplunkTrust
SplunkTrust

You probably have to run some stats/dedup command to collapse duplicate rows. The command/function to use will depend upon what value of "C/D" you want to show.

If you want to show all values of C/D for a,

your current search giving field line item C/D | stats values("C/D") as "C/D" max(line) as line by item

If you just want to show the first/last value for C/D for a

your current search giving field line item C/D | stats first("C/D") as "C/D" first(line) as line by item
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...