JIRA-backup-XXXXXX
├── activeobjects.xml
├── entities.xml
├── data
│   ├── attachments
│   └── avatars
└── logos

media-only-X.zip
└─── data
      ├── attachments
      │         ├── ProjectKey
      │         │         └── 10000
      │         │		    ├── IssueKey-1
      │         │		    ├── IssueKey-2
      │         │       	└── IssueKey-3
      │         └─── ProjectKey2
      └── avatars

grep '<UpgradeHistory' <Backup File Location>/entities.xml | awk 'BEGIN{ORS="\n"};{print $2}{print $5"\n"}'


Example Output:
id="10000"
status="complete"

id="10100"
status="complete"

id="10101"
status="complete"

id="10104"
status="complete"

id="10105"
status="failed"

grep 'lowerEmailAddress="'<Backup File Location>/entities.xml | awk -F\lowerEmailAddress=\" '{print $2}' | cut -d\" -f1 | sort | uniq -c

Example Output:
   1 felix.test@test.com
   2 fixed.test@test.com
   1 florian.test@test.com

--ADD-ON USERS - TESTED ON POSTGRESQL
select * from cwd_user where lower_email_address like 'connect.atlassian.com';

cat <Backup File Location>/entities.xml | grep '<Workflow id="' | cut -d " " -f7- | sort | uniq -c

Example Output:
   1 name="Software Simplified Workflow for Project SER">
   1 name="Software Simplified Workflow for Project SKP">
   1 name="Software Simplified Workflow for Project TES">
   1 name="TEST: Change Management workflow for Jira Service Desk">
   1 name="TEST: Incident Management workflow for Jira Service Desk">
   1 name="TEST: Jira Service Desk default workflow">
   1 name="TEST: Problem Management workflow for Jira Service Desk">
   1 name="TEST: Service Request Fulfilment with Approvals workflow for Jira Service Desk">
   1 name="TEST: Service Request Fulfilment workflow for Jira Service Desk">
   1 name="company-managed default workflow">

SELECT * FROM clusteredjob 
WHERE job_id in 
(SELECT job_id FROM clusteredjob GROUP BY job_id HAVING COUNT(*)>1)

grep 'ClusteredJob id="' <Backup File Location>/entities.xml | cut -d "=" -f2 | sort | uniq -c
Example Output:
   1 "10001" jobId
   1 "10107" jobId
   1 "10201" jobId
   1 "10202" jobId
   1 "10204" jobId
   1 "10205" jobId
   1 "10300" jobId
   1 "10301" jobId
   1 "10302" jobId
   1 "10303" jobId
   1 "10304" jobId
   1 "10307" jobId
   1 "10308" jobId
   1 "10309" jobId
   1 "10311" jobId

SELECT id,issueid, char_length(filename)
FROM fileattachment
WHERE char_length(filename) > 250

grep <Backup File Location>/entities.xml | cut -d\  -f9 | awk 'length > 250  {print ;}' | wc -l


Example Output:
   0 

// get list of project permission schemes which has share type as "Anyone" (i.e. open)
SELECT p.id, p.pname, ps.name FROM project p
INNER JOIN nodeassociation na ON
p.id = na.source_node_id
INNER JOIN schemepermissions sp ON
na.sink_node_id = sp.scheme
INNER JOIN permissionscheme ps ON
na.sink_node_id = ps.id
WHERE na.source_node_entity = 'Project'
AND na.sink_node_entity = 'PermissionScheme'
AND sp.permission_key='BROWSE_PROJECTS'
AND sp.perm_type='group'
AND sp.perm_parameter is null

// get list of filters which has share type as "share with everyone" (i.e. global)
SELECT sr.filtername, sp.sharetype AS current_share_state, sr.username AS owner_name, sr.reqcontent AS JQL
FROM searchrequest sr
INNER JOIN sharepermissions sp ON sp.entityid = sr.id 
WHERE sp.sharetype='global' and sp.entitytype ='SearchRequest';

// get list of Agile broads which has share type as "share with everyone" (i.e. global)
SELECT DISTINCT "rv"."NAME" as "Board Name", sr.filtername, sp.sharetype AS current_share_state, sr.username AS owner_name
FROM "AO_60DB71_RAPIDVIEW" as rv 
INNER JOIN searchrequest sr ON sr.id = rv."SAVED_FILTER_ID"
INNER JOIN sharepermissions sp ON sp.entityid = sr.id 
WHERE sp.sharetype='global' and sp.entitytype ='SearchRequest';

// get list of Dashboard which has share type as "share with everyone" (i.e. global)
SELECT DISTINCT pp.id as Dashboard_Id, pp.pagename AS Dashboard_name, sp.sharetype AS current_share_state, pp.username AS owner_name
FROM portalpage pp
INNER JOIN sharepermissions sp ON sp.entityid = pp.id 
WHERE sp.sharetype='global' and sp.entitytype ='PortalPage'
ORDER BY pp.id;

grep "<Users>" <Support Zip Location>/application-properties/application.xml

Example Output:
<Users>2205</Users>

grep "<jvm.system.timezone>" <Support Zip Location>/application-properties/application.xml


Example Output: 
<jvm.system.timezone>America/New_York</jvm.system.timezone>

select round(sum(filesize)/1024/1024,2) 
as "Total Attachment Size(MB)"
from fileattachment 
join jiraissue on jiraissue.id = fileattachment.issueid;


select round(sum(filesize)/1024/1024,2) 
as "Size(MB) by Project", project.pname as "Project Name" 
from fileattachment 
join jiraissue on jiraissue.id = fileattachment.issueid 
join project on project.id = jiraissue.project group by project.pname order by "Size(MB) by Project" desc;

xmlstarlet sel -t -v "/entity-engine-xml/Project/@key"  -n <Backup File Location>/entities.xml 


Example Output:
SER

SKP
TEST