Splunk Dev

Need help in extracting results from two indexes?

amaurya1
Explorer

In the below query, I'm using indexes "abc" and "def" and extracting the results only for the accounts which are present in index "abc" and not in "def" for each hour.
The query works fine but I've an additional requirement where I want to have a third column where I want the count of events coming only from index "abc" in each hour. I'm not able to add that condition, can someone please help?

(index=abc sourcetype=xyz event_type= "Only_Fail")
OR
( index=def (TYPE1=10 OR TYPE2=20) )
| eval dex1 = if(index=="abc", 1, 0)
| eval dex2 = if(index=="def", 1, 0)
| eval myaccounts = coalesce(CUST_ID, account_number)
| stats min(_time) as _time, sum(dex1) as dex1, sum(dex2) as dex2 by myaccounts | where dex2 = 0 and dex1 > 0
| timechart span=1h count(myaccounts) as total_accounts,sum(dex1) as all_fails

0 Karma

Anantha123
Communicator

if you want to see the count in same timechart then use appendcols and add the total events of 1st query.

(index=abc sourcetype=xyz event_type= "Only_Fail")
OR
( index=def (TYPE1=10 OR TYPE2=20) )
| eval dex1 = if(index=="abc", 1, 0)
| eval dex2 = if(index=="def", 1, 0)
| eval myaccounts = coalesce(CUST_ID, account_number)
| stats min(_time) as _time, sum(dex1) as dex1, sum(dex2) as dex2 by myaccounts
| where dex2 = 0 and dex1 > 0
| timechart span=1h count(myaccounts) as total_accounts,sum(dex1) as all_fails
| appendcols
[ search (index=abc sourcetype=xyz event_type= "Only_Fail")
| timechart count as Total ]

0 Karma

amaurya1
Explorer

Hi @Anantha123.. thanks for your reply but this is not giving the correct answer. This query will give the total events only for the accounts which have been received from the existing query(ie before appendcols). I want all the events from the index abc with these conditions (index=abc sourcetype=xyz event_type= "Only_Fail")

0 Karma

Anantha123
Communicator

appendcols doesnot take any values from before searches . Appendcols queries as new query . Else can you try doing the calc the total and then use appendcols to extract accounts .

(index=abc sourcetype=xyz event_type= "Only_Fail")
| timechart count as Total
| appendcols
[ search (index=abc sourcetype=xyz event_type= "Only_Fail") OR ( index=def (TYPE1=10 OR TYPE2=20) )
| eval dex1 = if(index=="abc", 1, 0)
| eval dex2 = if(index=="def", 1, 0)
| eval myaccounts = coalesce(CUST_ID, account_number)
| stats min(_time) as _time, sum(dex1) as dex1, sum(dex2) as dex2 by myaccounts
| where dex2 = 0 and dex1 > 0
| timechart span=1h count(myaccounts) as total_accounts,sum(dex1) as all_fails ]

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