Reporting

[Search] Change Pivot search to datamodel search

test_qweqwe
Builder

Hi!
I never used Pivot command and now I need to change this:

| pivot Authentication Authentication count(Authentication) AS "Count of Authentication" SPLITROW src AS src SPLITCOL action LIMIT src BY TOP 15 count(is_Failed_Authentication) FILTER action isNot unknown FILTER action isNot search FILTER src isNot "10.10.20.58" FILTER src isNot "10.10.20.140" FILTER src isNot "10.10.20.200" FILTER src isNot unknown FILTER src isNot *az* FILTER src isNot struts FILTER src isNot *DESKTOP* SORT 1000 src ROWSUMMARY 0 COLSUMMARY 0 NUMCOLS 100 SHOWOTHER 0 |rename src AS Host | sort -failure

to something like:

| tstats `summariesonly` values(Authentication.app) as app,count from datamodel=Authentication.Authentication by Authentication.action,Authentication.src | `drop_dm_object_name("Authentication")` | eval success=if(action="success",count,0) | eval failure=if(action="failure",count,0) | stats values(app) as app,sum(failure) as failure,sum(success) as success by src | where success > 0 | xswhere failure from failures_by_src_count_1d in authentication is above medium | `settags("access")`

^ this search shows success/failed authentication only by remote (and i need in general all success/failed by all users)
And by low skill with working tstats and Pivot syntax I'm stack :<

And one more question, it's possible to run my Pivot search with acceleration?

0 Karma
1 Solution

test_qweqwe
Builder

This.

| tstats `summariesonly` count from datamodel=Authentication.Authentication where Authentication.src!=unknown by Authentication.action,Authentication.src 
| `drop_dm_object_name("Authentication")` 
| eval success=if(action="success",count,0) 
| eval failure=if(action="failure",count,0) 
| stats sum(failure) as failure,sum(success) as success by src 
| where success > 0
| xswhere failure from failures_by_src_count_1d in authentication is above medium 
| fields + src failure success 
| rename src as Host failure as Failure success as Success
| sort -Failure

View solution in original post

0 Karma

test_qweqwe
Builder

This.

| tstats `summariesonly` count from datamodel=Authentication.Authentication where Authentication.src!=unknown by Authentication.action,Authentication.src 
| `drop_dm_object_name("Authentication")` 
| eval success=if(action="success",count,0) 
| eval failure=if(action="failure",count,0) 
| stats sum(failure) as failure,sum(success) as success by src 
| where success > 0
| xswhere failure from failures_by_src_count_1d in authentication is above medium 
| fields + src failure success 
| rename src as Host failure as Failure success as Success
| sort -Failure
0 Karma

mayurr98
Super Champion

hey @test_qweqwe
Try this,

| tstats count from datamodel=Authentication where (Authentication.action!="unknown" OR Authentication.action!="search") AND (Authentication.src!="10.10.20.58" OR Authentication.src!="10.10.20.200" OR Authentication.src!="10.10.20.140" OR Authentication.src!="unknown" OR Authentication.src!=*az* OR Authentication.src!="struts" OR Authentication.src!=*DESKTOP*) by Authentication.src Authentication.action | rename Authentication.* as *  | <further query>

This is not an exact query but this is how you must start.
Let me know if it helps!

0 Karma
Get Updates on the Splunk Community!

Index This | What are the 12 Days of Splunk-mas?

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

Get Inspired! We’ve Got Validation that Your Hard Work is Paying Off

We love our Splunk Community and want you to feel inspired by all your hard work! Eric Fusilero, our VP of ...

What's New in Splunk Enterprise 9.4: Features to Power Your Digital Resilience

Hey Splunky People! We are excited to share the latest updates in Splunk Enterprise 9.4. In this release we ...