How to get results from two different Sourcetype and presenting in a table?

[SentinelPrime01]

Im trying to get the following into a table and have a count of the successful attempts.

I have tried a few ways, but still am lost. below are my 2 attempts:

Ex1:

(index="sfdc" sourcetype="sfdc:loginhistory" eventtype="sfdc_login_history" LoginType="SAML Sfdc Initiated SSO" app="sfdc" action=success)  OR (index="microsoft" (sourcetype="azure:aad:signin" eventtype="azure_aad_signin" app="windows:sign:in" action=success) OR (sourcetype="azure:aad:user" jobTitle!=null))
| eval login=case((sourcetype=="azure:aad:signin" AND eventtype=="azure_aad_signin"), "windows", (sourcetype=="sfdc:loginhistory" AND app="sfdc"), "salesforce")
| table displayName  mail jobTitle officeLocation eventtype

Ex2:

index=microsoft (sourcetype="azure:aad:user" givenName="***" surname="***" jobTitle!="null" officeLocation!="null") OR (sourcetype="azure:aad:signin" eventtype="azure_aad_signin" app="windows:sign:in" action=success)    | stats  count by displayName  mail jobTitle officeLocation     | rename  displayName AS "Display Name" mail AS Email department AS Department jobTitle AS "Job Title" officeLocation AS Branch    | fields  - count     | sort  + Display Name

EX3:

index=microsoft (sourcetype="azure:aad:signin" eventtype="azure_aad_signin" app="windows:sign:in" action=success) OR (sourcetype="azure:aad:user" givenName="***" surname="***" jobTitle!="null" officeLocation!="null")
| eval joiner=if(sourcetype="azure:aad:signin", action, displayName) | stats values(action) as action by displayName mail jobTitle officeLocation | rename displayName AS "Display Name" mail AS Email department AS Department jobTitle AS "Job Title" officeLocation AS Branch
| sort + Display Name

What I'm trying to achieve here is to have a table listing the following

Display Name | Email | Job Title | Branch | Windows Logon Attempt* | Sales Force Login Attempt*

Windows Logon Attempt* | Sales Force Login Attempt* - is the part that I get stuck and can't seem to populate the list from the following index and srctype.

Ex2 and 3 is without Salesforce (which I can live with ). If you can help he with Ext3 that will be great!

Any ideas from the Splunkers in here?

Thanks,

S

[SentinelPrime01]

Hi @gcusello Not to combined them. These are 3 sort of similar searches that I have been working on to give an output of the table - which is where I am stuck.  My apologies that my explanation is not that clear - its probably just me :-).

So to make it simple, lets look at EX3: 

index=microsoft (sourcetype="azure:aad:signin" eventtype="azure_aad_signin" app="windows:sign:in" action=success) OR (sourcetype="azure:aad:user" givenName="***" surname="***" jobTitle!="null" officeLocation!="null")
| eval joiner=if(sourcetype="azure:aad:signin", action, displayName) | stats values(action) as action by displayName mail jobTitle officeLocation | rename displayName AS "Display Name" mail AS Email department AS Department jobTitle AS "Job Title" officeLocation AS Branch
| sort + Display Name

Which essentially will  show the values of these:

Display Name | Email | Job Title | Branch | Windows Logon Attempt* 

Windows Logon Attempt* should get its output from sourcetype="azure:aad:signin", when action="success" 

the | stats  count  or value  should then show the successful login within X no of days.

**note that I  have been working with 2 different stats and eval

| stats values(action) as action by displayName mail jobTitle officeLocation &

| stats  count by displayName  mail jobTitle officeLocation 

| eval login=case((sourcetype=="azure:aad:signin" AND eventtype=="azure_aad_signin"), "windows") &
| eval joiner=if(sourcetype="azure:aad:signin", action, displayName)

I'm a bit lost as to which one to use. and have also tried  using the | eval coalesce().

Hope this makes sense. It is abit long winded.

Cheers

S

[yuanliu]

Still not completely clear.  But I get that all three examples are attempts to achieve one goal, and the end result should include fields as indicated in the sample table head.  Maybe something like this?

(index="sfdc" sourcetype="sfdc:loginhistory" eventtype="sfdc_login_history" LoginType="SAML Sfdc Initiated SSO" app="sfdc" action=success)
  OR (index="microsoft" (sourcetype="azure:aad:signin" eventtype="azure_aad_signin" app="windows:sign:in" action=success)
  OR (sourcetype="azure:aad:user" jobTitle!=null))
| bin span=5d _time ``` assume you want to count in 5-day intervals ```
| stats count(eval(sourcetype=="azure:aad:signin" AND eventtype=="azure_aad_signin")) as "Windows logon attempts"
  count(eval(sourcetype=="sfdc:loginhistory" AND app="sfdc")) as "Salesforce logon attempts"
  by displayName  mail jobTitle officeLocation _time
| rename  displayName AS "Display Name", mail AS Email, department AS Department, jobTitle AS "Job Title", officeLocation AS Branch
| sort  + "Display Name"

[SentinelPrime01]

Hi @yuanliu , Thanks for your response. Appreciate it.

That's it! However for some reason the fields in the table for "jobTitle" and "officeLocation" are not populating. What I have done now is completely remove any reference to index=sfdc and only used the following to get Salesforce fields/data:

index="microsoft" (sourcetype="azure:aad:signin" userDisplayName="***" userPrincipalName="***" appDisplayName="Salesforce" OR appDisplayName="Windows Sign In" action=success) OR (sourcetype="azure:aad:user" jobTitle!=null officeLocation="****")
| stats count(eval(sourcetype=="azure:aad:signin" AND appDisplayName="Windows Sign In")) as "Windows login"
count(eval(sourcetype=="azure:aad:signin" AND appDisplayName="Salesforce")) as "Salesforce Login" by userDisplayName userPrincipalName
| table userDisplayName userPrincipalName jobTitle officeLocation "Windows login" "Salesforce Login"

 I just can't get the values for "jobTitle" and "officeLocation" in the table.

Any Ideas?

Cheers

S.

[yuanliu · Accepted solution]

If you do not include jobTitle and officeLocation in the stats command, of course they cannot appear in the final table.

Note my previous mockup code used these two fields in groupby.  Because you didn't tell us how these fields appear in data, I had to make an assumption: that these fields were ubiquitous in every event in every source.  This is forum basics: When asking a question, always explain your data, maybe illustrate anonymized sample, how fields relate to each other, and how data relates to desired output, etc.  If others have to shoot in the dark, chances are the assumptions will be wrong.

If jobTitle and officeLocation do not appear in every event of every source, you can use values function to preserve non-null values, like

index="microsoft" (sourcetype="azure:aad:signin" userDisplayName="***" userPrincipalName="***" appDisplayName="Salesforce"
  OR appDisplayName="Windows Sign In" action=success)
  OR (sourcetype="azure:aad:user" jobTitle!=null officeLocation="****") 
| stats count(eval(sourcetype=="azure:aad:signin" AND appDisplayName="Windows Sign In")) as "Windows login",
  count(eval(sourcetype=="azure:aad:signin" AND appDisplayName="Salesforce")) as "Salesforce Login"
  values(jobTitle) as jobTitle values(officeLocation) as officeLocation
  by userDisplayName userPrincipalName
| table userDisplayName userPrincipalName jobTitle officeLocation "Windows login" "Salesforce Login"

Hope this helps.

[gcusello]

Hi @SentinelPrime01,

what's your request, I don't understand: do you want to have these three searches in one or to separately debug each of them?

If you want to separately degub them, the only problem of your searches that I can see is that in 2 and 3 you want to display "department" that isn't in the previous stats command, so it isn't possible to display it.

If you want to have one big earch, you have to group the statements.

Ciao.

Giuseppe