Splunk Search

How to find a Name in one sourcetype and not in other sourcetype after outer join

dtccsundar
Path Finder

i am not able differentiate which sourcetype the Name belongs too after outer join.This is needed becoz when the Name is available in a sourcetype the other sourcetypes Agent should be changed as "Not in Scope" based on the sourcetype with which the Name belongs too. my query is like ,

index=A sourcetype=Compare| fillnull value="" | join type=outer ITAM_sysid [ search index=A sourcetype=Fire| fillnull value=""]
| fillnull value=""
| stats values(*) as * values(sourcetype) as sourcetype by Name
| eval Status=if(Fire_Agent_Version = "" AND Compare_Agent_Version = "","Not Covered","Covered")
| eval Compare_Agent_Version=if(Status="Not Covered","Not installed",Compare_Agent_Version)
| eval Fire_Agent_Version=if(Status="Not Covered" AND Compare_Agent_Version="Not installed","Not in Scope",Agent_Version)
| eval Fire_Agent_Version=if(Status="Not Covered" AND Compare_Agent_Version="Not installed","Not in Scope",Fire_Agent_Version)
| table sourcetype Name, Fire_Agent_Version, Compare_Agent_Version, Status

Labels (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dtccsundar,

you have to use a simple eval, something like this:

| eval Name=if(sourcetype="Compare",Name,"Not in scope")

where <your_sourcetype_yes> is the sourcetype.

One warning: subsearches have the limit of 50,000 results and join is a very slow command, you should explore a different approach using stats, something like this:

if Name in in both the sourcetypes:

index=A sourcetype In (Compare,Fire)
| stats values(*) as * by ITAM_sysid Name
| eval 
     Status=if(Fire_Agent_Version = "" AND Compare_Agent_Version = "","Not Covered","Covered"),
     Compare_Agent_Version=if(Status="Not Covered","Not installed",Compare_Agent_Version),
     Fire_Agent_Version=if(Status="Not Covered" AND Compare_Agent_Version="Not installed","Not in Scope",Agent_Version),
     Fire_Agent_Version=if(Status="Not Covered" AND Compare_Agent_Version="Not installed","Not in Scope",Fire_Agent_Version)
| table sourcetype Name Fire_Agent_Version Compare_Agent_Version Status

If not:

index=A sourcetype In (Compare,Fire)
| stats values(*) as * by ITAM_sysid
| mvexpand Name
| stats values(*) as * by Name
| eval 
     Status=if(Fire_Agent_Version = "" AND Compare_Agent_Version = "","Not Covered","Covered"),
     Compare_Agent_Version=if(Status="Not Covered","Not installed",Compare_Agent_Version),
     Fire_Agent_Version=if(Status="Not Covered" AND Compare_Agent_Version="Not installed","Not in Scope",Agent_Version),
     Fire_Agent_Version=if(Status="Not Covered" AND Compare_Agent_Version="Not installed","Not in Scope",Fire_Agent_Version)
| table sourcetype Name Fire_Agent_Version Compare_Agent_Version Status

Ciao.

Giuseppe

0 Karma

dtccsundar
Path Finder

But i am  getting no results for this

index=A sourcetype In (Compare,Fire)

Background :

i am  getting  sourcetype values from DWH as view format .Here the Name available in one view Compare but in other view the same Name is not available and the row is empty for few names ,the same way around for other view .

Due to this the Name will not be available fire sourcetype and vice versa.

Any idea on this comparison to achieve this requirement.

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dtccsundar,

please  try this

index=A sourcetype IN (Compare,Fire)

"IN" must be un uppercase.

Ciao.

Giuseppe

0 Karma

dtccsundar
Path Finder

Its works now .Mine is the second scenario which you mentioned above where Name available in one sourcetype and the entire row is not available in other sourcetype.

Like below ,

Sourcetype =Compare

Name                  OS Compare_VersionCompare_Agent Installedsysid
ABC11        windows 10.1   2.2qweq

Sourcetype =Fire

NameOS Fire_VersionFire_Agent Installedsysid
     

 

After doing ,
index=A sourcetype IN (Compare,Fire)

| stats values(*) as * by ITAM_sysid ITAM_Name
| mvexpand ITAM_Name
| stats values(*) as * by ITAM_Name

Since the sourcetype =Fire   has empty row for particular sysid or Name , i am not getting exact output.ITs null ,so i need to fill null value="" in the sourcetype which has no rows and the same is required for other scenario too(when sourcetype=Compare has no data and sourcetype =Fire has data).

Please let me know a search which accomodates this too .

 

 

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dtccsundar,

if you put ITAM_Name in the BY clause you don't need mvexpand, it's useful if you have a multivalue.

If sometimes you have null value for ITAM_Name, you  cannot use it in the BY clause because in this way you exclude all the null values, so please, try something like this:

index=A sourcetype IN (Compare,Fire)

| stats values(*) as * by ITAM_sysid
| mvexpand ITAM_Name
| stats values(*) as * by ITAM_Name

In other words: the key or the first stats must be present in both the sourcetypes otherwise you cannot correlate results both using join or stats.

Ciao.

Giuseppe

0 Karma

dtccsundar
Path Finder

 

I am using below query,

index=A sourcetype IN (Compare,Fire)| fillnull value="" | search ITAM_Name="*SWZWZQ0001*" OR ITAM_Name="*SADAPP0002*" OR ITAM_Name="*SALINU0016*" OR ITAM_Name="*SGGRNP1002*"

| stats values(*) as * by ITAM_sysid
|eval Status=if(F_Agent_Version ="" AND C_Agent_Version ="","Not Covered","Covered")
| table sourcetype ITAM_sysid ITAM_Name F_Agent_Version C_Agent_Version Status

sourcetypeITAM_sysidITAM NameFire Agent VersionCompare Agent VersionStatus

Compare     

Fire

0003fbSALINU0016
32.30.
6.3Not Covered
Compare                    Fire003fcbSGGRNP1002
29.7
 Not Covered
Fire0d456SADAPP000232.3 Covered
Compare0d526SWZWZQ0001  Not Covered

 

Due to the null's in the first and second rows (SALINU0016,SGGRNP1002) for Agent_version and Compare Agent Version , i am getting not covered instead of covered.Please let me know ,how to get rid of nulls and make the status Covered .

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...