Need some help with and advance joining of 3 queries
I have three queries that produce tables, I need to combine the results into one table. The first query will produce a table with 10 fields with a count for each, the other two only produce one field with a count but I want all the results to display in one table.
Table 1:
{My Search Criteria} PassedResult=0| stats count by riskcheckName| sort count desc
Table 2:
{My Search Criteria} PassedREsult=0 Golden_Key_Failed=Failed |stats count by Golden_Key_Failed | replace Failed WITH "Risk Score Fail" IN Golden_Key_Failed
Table 3:
{My Search Criteria} PassedIAM=0 SN_Does_Not_Exist=Failed |stats count by SN_Does_Not_Exist | replace Failed WITH "SN Does Not Exist" IN SN_Does_Not_Exist
thanks for any helo
Try using append
{My Search Criteria} PassedResult=0| stats count by riskcheckName| sort count desc
| append [ | search {My Search Criteria} PassedREsult=0 Golden_Key_Failed=Failed |stats count by Golden_Key_Failed | replace Failed WITH "Risk Score Fail" IN Golden_Key_Failed]
| append [ | search {My Search Criteria} PassedIAM=0 SN_Does_Not_Exist=Failed |stats count by SN_Does_Not_Exist | replace Failed WITH "SN Does Not Exist" IN SN_Does_Not_Exist]
Try using append
{My Search Criteria} PassedResult=0| stats count by riskcheckName| sort count desc
| append [ | search {My Search Criteria} PassedREsult=0 Golden_Key_Failed=Failed |stats count by Golden_Key_Failed | replace Failed WITH "Risk Score Fail" IN Golden_Key_Failed]
| append [ | search {My Search Criteria} PassedIAM=0 SN_Does_Not_Exist=Failed |stats count by SN_Does_Not_Exist | replace Failed WITH "SN Does Not Exist" IN SN_Does_Not_Exist]
Thank you for the response, this worked , we tweaked it a little after trying your solution. the final query ended like this ( due to rows being added in original query)
{My Search Criteria} PassedResult=0 | stats count by riskcheckName | rename riskcheckName as column | rename count as "row 1"
| append
[| search {My Search Criteria} PassedIAM=0 Golden_Key_Failed=Failed | dedup ID |stats count as “Golden Key Failed” | transpose]
| append
[search {My Search Criteria} PassedIAM=0 SN_Does_Not_Exist=Failed | dedup ID | stats count as " SN Does Not Exist" | transpose]
| rename column as "Cause of Failure"
| rename "row 1" as Count
| sort "Cause of Failure"