[Search] Change Pivot search to datamodel search

[test_qweqwe]

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?

[test_qweqwe · Accepted solution]

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

[mayurr98]

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!