How to edit my search to return a list within a list?

[singhh4]

Hey guys,

So what i am trying to do is put a list inside of a list to get an output such as the one below

Comapny    |   Count1  |   Group  |  Count2   |  Environment  |  Count3
_____________________________________________________________________
CompanyID       10        GroupID1      2        Environment1      1
                                                 Environment2      1
                          GroupID2      8        Environment1      4
                                                 Environment2      4
______________________________________________________________________ 
CompanyID2      12        GroupID1      4        Environment1      3
                                                 Environment2      1
                          GroupID2      8        Environment1      2
                                                 Environment2      6

Or this:

Comapny    |   Count1  |   Group  |  Count2   |  Environment1  |   Environment2
_______________________________________________________________________________________
CompanyID       10        GroupID1      2             1                  1
                          GroupID2      8             4                  4
_______________________________________________________________________________________
CompanyID2      12        GroupID1      4             3                  1
                          GroupID2      8             2                  6

I have a search that gets me the Company, Group, and Environment but I can't get the counts and Groups to show up properly

Current search:

index="Customers" |stats count by Customer,Group, Environment |stats sum(count) as Total list(Group) as Source list(count) as Count list(Environment) as Environment list(count) as Count2 by Customer

That search gets me the following output

Comapny    |   Count1  |   Group  |  Count2   |  Environment  |  Count3
_____________________________________________________________________
CompanyID       10        GroupID1               Environment1      1
                          GroupID1               Environment2      1
                          GroupID2               Environment1      4
                          GroupID2               Environment2      4
______________________________________________________________________ 
CompanyID2      12        GroupID1               Environment1      3
                          GroupID1               Environment2      1
                          GroupID2               Environment1      2
                          GroupID2               Environment2      6

How would i get one of the two outputs from above?

Thanks in advanced! 🙂

[somesoni2 · Accepted solution]

Give this a try (for expected format#2)

index="Customers" |stats count by Customer,Group, Environment | eval temp=Customer."#".Group | xyseries temp Environment count
| addtotals fieldname=Count2 | rex field=temp "(?<Customer>[^#]+)#(?<Group>.+)" | fields - temp | stats sum(Count2) as Count1 list(*) as * by Customer

[singhh4]

You are awesome! Thank you soo much!