How to solve this: Count unique unique number of field X, expect to find the same number of field Y

[rune_hellem]

First start with what I have today. We use a tool to deploy applications on to our WebSphere Deployment Server. A scheduled script will list every fourth hour applications, version number and enviroment, like this

2019/04/03 08:06:10 Application=useradmin-dar Version=2018.9.0.3 Environment=Environments/PROD
2019/04/03 08:06:10 Application=platform-forsikring Version=2085 Environment=Environments/PROD
2019/04/03 08:02:10 Application=useradmin-dar Version=2018.9.0.3 Environment=Environments/QA
2019/04/03 08:02:10 Application=nettpensjon-dar Version=4.9.12 Environment=Environments/QA

Then by using the search string below I will get a table showing all applications where the version number is not identical in all enviroments

index=xebialabs source="e:\\logs\\environments\\*\\deployedApplications.log"  |  stats dc(Version) AS numVersions list(Version) AS versions list(Environment) AS indices BY Application | where numVersions > 1

That's just fine, but there is one issue with this. It won't show applications not installed in one of the environments.

So what I mean by the question

How to solve this: Count unique unique number of field X, expect to find the same number of field Y

is: Based on this very simple example

2019/04/03 08:06:10 Application=useradmin-dar Version=2018.9.0.3 Environment=Environments/PROD
2019/04/03 08:06:10 Application=platform-forsikring Version=2085 Environment=Environments/PROD
2019/04/03 08:02:10 Application=useradmin-dar Version=2018.9.0.3 Environment=Environments/QA

I would need a search that returns

Application=platform-forsikring Environment=Environments/PROD

Telling me that the application platform-forsikring is only found in Prod, and even better would be

Application=platform-forsikring Environment=Environments/QA

Telling me that is is NOT found in QA, but not sure if that is possible.

[knielsen · Accepted solution]

Hi

I created myself a little run everywhere with both issues in the input. For this small dataset, I get the correct output:

| makeresults | eval input="useradmin-dar;2018.9.0.3;PROD%platform-forsikring;2085;PROD%useradmin-dar;2018.9.0.3;QA%test;42;PROD%test;44;QA" | makemv delim="%" input | mvexpand input | rex field=input "(?<Application>[^;]+);(?<Version>[^;]+);(?<Environment>.+)" | eval AppVer=Application+"_"+Version |  eventstats count as AppCount by Application | eventstats count(Environment) as EnvCount by AppVer  | where AppCount=1 OR EnvCount=1 | eval clashV=Version+" in "+Environment | stats values(*) as * by Application | eval clashS=mvjoin(clashV," and ") | eval MissEnv=if(Environment="PROD","QA","PROD"), Issue=if(AppCount=1,"Missing: Application="+Application+" Environment="+MissEnv,"Version clash: Application="+Application+" has Version "+clashS) | table Issue

gives

Missing: Application=platform-forsikring Environment=QA 
Version clash: Application=test has Version 42 in PROD and 44 in QA 

So try that with your original query maybe:

index=xebialabs source="e:\\logs\\environments\\*\\deployedApplications.log" |  eval AppVer=Application+"_"+Version |  eventstats count as AppCount by Application | eventstats count(Environment) as EnvCount by AppVer  | where AppCount=1 OR EnvCount=1 | eval clashV=Version+" in "+Environment | stats values(*) as * by Application | eval clashS=mvjoin(clashV," and ") | eval MissEnv=if(Environment="PROD","QA","PROD"), Issue=if(AppCount=1,"Missing: Application="+Application+" Environment="+MissEnv,"Version clash: Application="+Application+" has Version "+clashS) | table Issue

Maybe I was just lucky, and it breaks with a bigger dataset. 🙂

Regards,
Kai.

[rune_hellem]

Whow!! I'm impressed, thanks a bunch! Totally accepted, Worked just fine with bigger dataset as well

[somesoni2]

Whats the final output that you expect?