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
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
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.
Hi @dtccsundar,
please try this
index=A sourcetype IN (Compare,Fire)
"IN" must be un uppercase.
Ciao.
Giuseppe
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_Version | Compare_Agent Installed | sysid |
ABC11 | windows | 10.1 | 2.2 | qweq |
Sourcetype =Fire
Name | OS | Fire_Version | Fire_Agent Installed | sysid |
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 .
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
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
sourcetype | ITAM_sysid | ITAM Name | Fire Agent Version | Compare Agent Version | Status |
Compare Fire | 0003fb | SALINU0016 | 32.30. | 6.3 | Not Covered |
Compare Fire | 003fcb | SGGRNP1002 | 29.7 | Not Covered | |
Fire | 0d456 | SADAPP0002 | 32.3 | Covered | |
Compare | 0d526 | SWZWZQ0001 | 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 .