Hello all
I want to display the field name(CNB) in the main result which has no result now but in future it ll.
I just want to showcase the total number of clients new and old.
CNB - new clients no data
PPN-old we have data
INB - old we have data
Want to display in table format
clients | recent time project update | number of users | by country
Thanks in advance.
| makeresults count=100
| eval count=random() % 3
| eval clients=case(count=1,"PPN",count=2,"INB",true(),NULL)
| stats values(_time) as Project_Update count as Number_of_Users by clients
| eval Project_Update=strftime(Project_Update,"%F")
| append
[| makeresults
| eval clients=split("CNB#PPN#INB","#")
| mvexpand clients
| eval Project_Update="N/A",Number_of_Users="N/A"
| fields - _time]
| stats first(Project_Update) as Project_Update first(Number_of_Users) as Number_of_Users by clients
For the time being, it looks like this.
so,
Create user.csv:
Clients
PPN
CNB
INB
.....
and
your_search
|table Clients "Project Update" "Number of Users" "by Country"
|inputlookup append=t user.csv
|fillnull value="N/A"
This is easy.
| makeresults count=100
| eval count=random() % 3
| eval clients=case(count=1,"PPN",count=2,"INB",true(),NULL)
| stats values(_time) as Project_Update count as Number_of_Users by clients
| eval Project_Update=strftime(Project_Update,"%F")
| append
[| makeresults
| eval clients=split("CNB#PPN#INB","#")
| mvexpand clients
| eval Project_Update="N/A",Number_of_Users="N/A"
| fields - _time]
| stats first(Project_Update) as Project_Update first(Number_of_Users) as Number_of_Users by clients
For the time being, it looks like this.
so,
Create user.csv:
Clients
PPN
CNB
INB
.....
and
your_search
|table Clients "Project Update" "Number of Users" "by Country"
|inputlookup append=t user.csv
|fillnull value="N/A"
This is easy.
Thanks For your help @to4kawa
small info how i can add drildown option for
Clients
PPN-- opne in new page (with there details )
CNB -- opne in new page (with there details )
INB-- opne in new page (with there details )
I don't know.
you didn't provide your search details.
@ololdach can you pls help me
I've amended the answer below. Please always include sample data and an example of the output as it really helps to guess what you are up to 🙂
Hi,
from a logical data flow point of view: First you need to get a list of all clients, those that have updates and/or users and those that don't. Either you query an index for "client creation events", select the data from another system/database or you use a lookup table/csv. Whatever you choose, you will get a one-column table with all clients like "PPN,CNB,XXD,TYZ...INB...etc.". Try this query to generate some data that show what the result would look like:
| makeresults | eval Clients="PPN,INB,CNB,XXA,etc" | table Clients | makemv delim="," Clients | mvexpand Clients
Then you join the list with the stats results of the clients and finally, you fill the null values. The result should look something like this:
<Client Table generating search> | join type=left Clients [ search <your search generating the user/project events> | stats sum(users) as users, latest(project_id) as project_id by Clients] |fillnull value="N/A"
Use this example to generate some sample data for the join search:
| makeresults count=10 | eval users=random()%100 | eval Clients=if(users>50,"PPN","CNB") | eval project_id="Project v".users | stats sum(users) as users, latest(project_id) as project_id by Clients
The full query looks like this:
| makeresults | eval Clients="PPN,INB,CNB,XXA,etc" | table Clients | makemv delim="," Clients | mvexpand Clients | join type=left[| makeresults count=10 | eval users=random()%100 | eval Clients=if(users>50,"PPN","CNB") | eval project_id="Project v".users | stats sum(users) as users, latest(project_id) as project_id by Clients] | fillnull value="N/A"
Best
Oliver
sample log please